When people talk about paging with the GridView control, they’re normally talking about the default, out of the box, vanilla paging that comes built in the GridView control. This is good for demos or small databases. But what happens to the performance of your website if you connect to a database with thousands of records? The application won’t fall over, but the decline in performance will be noticeable. That is because GridView paging retrieves all records from the SQL Server and displays only what is required. One of the best ways to handle this scenario is to use the GridView control with an ObjectDataSource control and specify the methods you will use for manual paging.
The following example will utilize Visual Studio 2008, LINQ to SQL and SQL Server 2008.
Open Visual Studio 2008 and choose File > New > Web > ASP.NET Web Application.
Add a new Linq To SQL file to the project and connect to the Northwind database. Drag the Customers table onto the designer. This is the table that will be referenced in the example.
Now that the database layer exists, let’s turn our attention to the ObjectDataSource. The ObjectDataSource has two methods that are required for efficient paging. These are SelectMethod and SelectCountMethod. The following parameters are required for both methods:
int maximumRows, int startPageIndex, int startRowIndex
These values will be passed by the ObjectDataSource to the methods we define.
Add a new Class file to the project and name it Northwind.cs or Northwind.vb. This will be a partial class as this example will extend the functionality of the Northwind class defined in the Linq To SQL file. The following code is required for paging:
C#
using System.Linq;
namespace Paging
{
public partial class Northwind
{
private NorthwindDataContext Context
{
get;
set;
}
public Northwind()
{
Context = new NorthwindDataContext();
}
public int GetCustomerCount(int maximumRows, int startPageIndex, int startRowIndex)
{
return Context.Customers.Count();
}
public IQueryable<Customer> GetCustomers(int maximumRows, int startPageIndex, int startRowIndex)
{
var query = Context.Customers.Skip(startRowIndex).Take(maximumRows);
return query;
}
}
}
VB.NET
Imports System.Linq
Namespace Paging
Partial Public Class Northwind
Private privateContext As NorthwindDataContext
Private Property Context() As NorthwindDataContext
Get
Return privateContext
End Get
Set(ByVal value As NorthwindDataContext)
privateContext = value
End Set
End Property
Public Sub New()
Context = New NorthwindDataContext()
End Sub
Public Function GetCustomerCount(ByVal maximumRows As Integer, ByVal startPageIndex As Integer, ByVal startRowIndex AsInteger) As Integer
Return Context.Customers.Count()
End Function
Public Function GetCustomers(ByVal maximumRows As Integer, ByVal startPageIndex As Integer, ByVal startRowIndex AsInteger) As IQueryable(Of Customer)
Dim query = Context.Customers.Skip(startRowIndex).Take(maximumRows)
Return query
End Function
End Class
End Namespace
The GetCustomers method accepts the arguments that are passed by the ObjectDataSource. It utilizes the Skip and Take methods to only return the rows needed for the GridView, not the whole contents of the table. The GetCustomerCount method returns a count of all the records in the table. That is all that is required for our methods. Now we need to connect this up to a GridView control and see the results.
Open the Default.aspx page. Drag a GridView control onto the page. Open the Smart Tag and select Choose Data Source. Select <New data source...>:
A wizard will open and guide you through the setup. The first screen defines the Data Source Type:
Select Object and click OK. The following screen asks you to choose a Business Object. Select Paging.Northwind and click Next:
The following screen asks you to choose a Data Method. Select GetCustomers and click Next:
The final screen asks you to define the parameters. Leave the Value blank for as it will be passed by the ObjectDataSource. Once this is done click Finish to close the wizard:
The final step is to select the GridView and set AllowPaging to true. Then select the ObjectDataSource and set EnablePaging to true. The hard work is complete. To test this you can use SQL Profiler to see the SQL that is executed on the database. The following code is a sample of what is executed on the SQL Server:
SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region],[t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle],[t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]) AS [ROW_NUMBER],[t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region],[t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]
The code that is generated utilises the ROW_NUMBER() function which limits the numbers of rows that are returned.
By handling the paging as shown in this example, your ASP.NET website will be able to display thousands of records efficiently in a GridView and let the user page through data without delays that are common when dealing with large data sets. This will not only improve performance on the web server, but it will also decrease the amount of data that is sent to the client. The source code of the article can be downloaded from here.
Or you could use something different than GridView - https://gaiasoul.com/2016/10/21/the-fastest-ajax-datagrid-in-the-world/
ReplyDelete... ;)