Monday, May 12, 2008

How To Increase Performance of Loading Large Data from Database (Part-2)

Level: Advanced

Knowledge Required:
  • Typed DataSet
  • TableAdapter
  • DataAdapter
  • Partial Class
  • Data Binding

In my previous post, I showed you how to Implement Paging in SQL Server 2005 (Database Level). In this post I will discuss How To Implement Paging Using DataAdapter (Application Level). This is a simple technique,

SQLDataAdapter gives us an option to load the Data from Particular Row and to Limit the Loading of Data by providing the Maximum Number of Records to be Loaded. The Fill Method of SQLDataAdapter has 5 Declarations, one of them is:
SQLDataAdapter.Fill(StartRecord, MaxNumberOfRecordsToLoad, ParamArray DataTables())
We will use the above Fill Method to start loading Data from Particular Record Number and Limit the SQLDataAdapter to load only 20 or 30 Records, as per our Page Size.

To implement it professionaly, we will create another Fill Method in our TableAdapter Class (using Partial Class) as,

Partial Class LargeDataTableAdapter
Public Function FillByDataAdapterPaging(ByRef LargeDataDataTable As LargeDataSet.LargeDataDataTable, ByVal PageNumber As Integer, ByVal PageSize As Integer) As Integer
Dim StartRow As Integer
StartRow = ((PageNumber - 1) * PageSize)
Me.Adapter.SelectCommand = Me.CommandCollection(0)
If (Me.ClearBeforeFill = True) Then
End If
Dim returnValue As Integer = Me.Adapter.Fill(StartRow, PageSize, LargeDataDataTable)
Return returnValue
End Function
End Class

Source Code:
Note: Execute the ScriptToInsertBulkData.sql file (ziped inside the TemporaryDB.rar) to add the Fake Data

No comments: