Data Paging - Different & Simple Approach

Data Paging

Some applications need to display records in a grid. If there are too many records, then pagination has to be used so that the DataGrid displays only n number of records at a time. User has a facility to navigate through the records using Next, Previous, First, Last buttons.

 

Implementation

One of the overloads of Fill() method takes two integer values - startRecord and maxRecords. The startRecord value indicates the zero-based index of the start record. The maxRecords value indicates the number of records, starting from startRecord, to copy into the new DataSet.

 

But, this method is not recommended because although the DataSet is only filled with the number of records specified by the maxRecords parameter, entire query is returned. This incurs unnecessary processing to read past the "unwanted" records, as well as uses unnecessary server resources to return the additional records.

 

It is recommended that you implement your own logic to fetch required number of records from the table using stored procedure. Following sample code shows one such example. It uses WHERE clause and  SET ROWCOUNT statement to filter the records and return required number of records respectively from a database table that has a primary key.

 

 

Sample Code:

 

using (SqlConnection conn = new SqlConnection(connString))

{

          int prodID = 0;

          int maxRecords = 50;

 

          SqlCommand cmd = new SqlCommand("uspGetProductsPaged", conn);

          cmd.CommandType = CommandType.StoredProcedure;

 

          // Configure stored procedure input parameter

          SqlParameter lastProductID =

          new SqlParameter("@lastProductID", prodID);

          lastProductID.Direction = ParameterDirection.Input;

 

          SqlParameter pageSize =

          new SqlParameter("@pageSize", maxRecords);

          pageSize.Direction = ParameterDirection.Input;

 

    cmd.Parameters.AddRange(new SqlParameter[]

   {lastProductID, pageSize});

 

          SqlDataAdapter da = new SqlDataAdapter(cmd);

 

          while(true)

{

              da.SelectCommand.Parameters[0].Value = prodID;

 

        // DataSet is filled with only required records

              DataSet ds = new DataSet();

              da.Fill(ds);

 

              if (ds.Tables[0].Rows.Count == 0)

                  break;

 

              // Maintain last product ID

              int lastrow = ds.Tables[0].Rows.Count;

              prodID = (int)ds.Tables[0].Rows[lastrow - 1][0];                   

           }

}

 

 

Stored procedure uspGetProductsPaged: 

CREATE PROCEDURE uspGetProductsPaged

@lastProductID int,

@pageSize int

AS

SET ROWCOUNT @pageSize

SELECT *

FROM Production.Product

WHERE ProductID > @lastProductID

ORDER BY ProductID

 

 

 

Thanks & Regards,

Arun Manglick

SMTS || Microsoft Technology Practice || Bridgestone - Tyre Link || Persistent Systems || 3023-6258

 

DISCLAIMER ========== This e-mail may contain privileged and confidential information which is the property of Persistent Systems Pvt. Ltd. It is intended only for the use of the individual or entity to which it is addressed. If you are not the intended recipient, you are not authorized to read, retain, copy, print, distribute or use this message. If you have received this communication in error, please notify the sender and delete all copies of this message. Persistent Systems Pvt. Ltd. does not accept any liability for virus infected mails.