DataGrid paging is important for a number of reasons. In most cases it is not good to display the entire results set all at once. It is typically best to display only small subsets of the data, one "page" at a time. This keeps the DataGrid in the user's field of view and prevents Web page scrolling. It means the page will render more quickly. If you implement paging at the SQL Server level then there can be an extra performance gain because you are only returning over the network the currently viewed results, not the entire results set.
This application requires the release version of Visual Studio .NET Professional (or greater) and access to the Northwind database residing in SQL Server or the Microsoft Data Engine (MSDE). To install MSDE, do the following:
1. Open the Start menu, click Programs, click Microsoft .NET Framework SDK, and then click Samples and Quickstart Tutorials.
2. Click "Step 1: Install the .NET Framework Samples Database".
3. Open a command window, and CD to [FRAMEWORKSDK]\Samples\Setup This is typically : C:\Program Files\Microsoft Visual Studio .NET\FrameworkSDK\Samples\Setup
4. Type: osql -E -S (local)\NetSDK -i InstNwnd.sql
Press F5 to run the sample.
It is worth pointing out that there is at least one other type of custom paging you could implement. This consists of moving the logic for determining the actual rows to display from the code to the SQL Server tier. The RowID column would be added to a temporary table in a stored procedure (instead of to the DataSet's DataTable, as in this How-To). The "FirstRecord" / "LastRecord" values would then be assigned to input parameters (instead of being used in a RowFilter statement, as in this How-To) and used to SELECT the subset of rows to be returned via a SqlDataReader (instead of a DataSet). In this case the performance gains come from using a SQL Server more effectively and using a faster SqlDataReader to return a much smaller results set. The disadvantage is that you have to recreate a potentially large temporary table on each paging request; and if the RowID field did not already exist in the SQL Server table from which the temporary table is created, you would also have to add the identity column on each paging request. Finally, you could not cache a DataView of the results set as it would be different for every paging request. It boils down whether you want to use SQL Server resources or Web server resources for the performance gains. The "right way" to do DataGrid paging is definitely scenario-driven.
For more on this technique see http://www.dotnetjunkies.com/tutorials.aspx?tutorialid=50.
Note:
If you get an error when running the sample make sure that IIS has appropriate permissions to query tables in the local SQL Server. There are multiple ways to achieve this but the easiest way to do this for testing and running samples is to change the default <processModel> settings in machine.config. The steps to do this are as follows:
1. Navigate to
c:\[WINDOWSDIR]\Microsoft.NET\Framework\[FrameworkVersion]\Config
2. Open the Machine.Config file found there and change
the userName attribute so that it's value is SYSTEM (i.e.
userName="SYSTEM")
3. Save the File.
4. Open up a command prompt and type IISReset to
restart IIS.
Another options to use that is more applicable for a production
environment is to give appropriate permissions to the ASPNET account in
the appropriate database.
See Also