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.
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.
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;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.SelectCommand.Parameters.Value = prodID;
// DataSet is filled with only required records
DataSet ds = new DataSet();
if (ds.Tables.Rows.Count == 0)
// Maintain last product ID
int lastrow = ds.Tables.Rows.Count;
prodID = (int)ds.Tables.Rows[lastrow - 1];
Stored procedure uspGetProductsPaged:
CREATE PROCEDURE uspGetProductsPaged
SET ROWCOUNT @pageSize
WHERE ProductID > @lastProductID
ORDER BY ProductID
Thanks & Regards,
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.