A couple of months ago I did an article on how to Efficiently Server Side Paging with the ASP.NET GridView Control. I thought I’d focus some articles on using the ASP.NET 3.5 ListView control, which I find useful when I need to totally customise the look and feel of the UI. I thought a good starting point would be to also show you how to efficiently page through data using the ListView. Out of the box, there is paging, but like other controls, the built-in paging is not great when you start connecting to large amounts of data. I’ll show you how to page through the data in an efficient way. This example uses the Northwind database. If you don’t have a copy of it you can download it from here.
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 paging. These are SelectMethod and SelectCountMethod. Also we need to define two variable names that will automatically be passed through from the ObjectDataSource to our methods. These are MaximumRowsParameterName and StartRowIndexParameterName. And lastly you need to set EnablePaging to true. After we have defined everything for the ObjectDataSource the properties window will look similar to following:
Let’s add the GetCustomerCount and GetCustomers methods. Add a new Class to the project and name it Northwind.cs. This will be a partial class as this example will extend the functionality of the Northwind class defined in the Linq to SQL file. Add the following code to the class:
C#
public int GetCustomerCount()
{
int totalCount = 0;
object o = HttpContext.Current.Cache["TotalCount"];
if (o == null)
{
using (NorthwindDataContext dc = new NorthwindDataContext())
{
totalCount = dc.Customers.Count();
HttpContext.Current.Cache["TotalCount"] = totalCount;
}
}
else
{
totalCount = (int)o;
}
return totalCount;
}
public List<Customer> GetCustomers(int maximumRows, int startRowIndex)
{
using (NorthwindDataContext dc = new NorthwindDataContext())
{
return dc.Customers.Skip(startRowIndex).Take(maximumRows).ToList();
}
}
VB.NET
Public Function GetCustomerCount() As Integer
Dim totalCount As Integer = 0
Dim o As Object = HttpContext.Current.Cache("TotalCount")
If o Is Nothing Then
Using dc As New NorthwindDataContext()
totalCount = dc.Customers.Count()
HttpContext.Current.Cache("TotalCount") = totalCount
End Using
Else
totalCount = CInt(Fix(o))
End If
Return totalCount
End Function
Public Function GetCustomers(ByVal maximumRows As Integer, ByVal startRowIndex As Integer) As List(Of Customer)
Using dc As New NorthwindDataContext()
Return dc.Customers.Skip(startRowIndex).Take(maximumRows).ToList()
End Using
End Function
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 ListView control, not the whole contents of the table. The GetCustomerCount method returns a count of all the records in the table. To make this more efficient, instead of hitting the database to get a count of the records for each page, I’m adding it to the ASP.NET Cache so the database is only hit once. That is all that is required for our methods. Now we need to connect this up to a ListView control and see the results.
Open the Default.aspx page. Drag a ListView 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 Choose your business object and select the newly created Customer class:
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 values blank as they will be passed by the ObjectDataSource. Once this is done click Finish to close the wizard:
That’s all that’s required for the ObjectDataSource. The next step is to click on the ListView’s smart tag and click Configure ListView...
A new screen will open and ask you to configure the ListView’s look and feel as well as enabling paging. This is a must as without it we won’t have access to the DataPager. The DataPager is a new control in ASP.NET 3.5, and this is automatically added when you want a ListView control to have paging:
Everything is done now. If you run the project now, you’ll see the ListView paging through the data. To get a better look at what is happening between the website and SQL Server, open SQL Profiler to view the SQL statements that are being generated. 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’s generated utilises the ROW_NUMBER() function which limits the numbers of rows that are returned by the query.
In upcoming articles I’ll show you how to insert, update and delete data using the ListView control.
The entire source code of this article can be downloaded over here
No comments:
Post a Comment