ASP.Net tutorial by Barbie Hocking  ©2010

Next Page           

The ASP.Net DataList display format is defined via templates. This provides flexibility in the layout and eliminates the 'column' restriction of a gridview.  Additionally, DataLists allow you to repeat the template display.  Paging must be manually implemented.  This Visual Studio tutorial contains instructions on how to implement paging within a DataList. 

The SQL access paging method used in this tutorial returns only the rows that display on each page. This is a good method to use when the resultset is too large to keep in cache or when complex queries result in slow performance. This example uses a DataList, but the same technique can be used for any data bound control.

If you have a small resultset, you can use the technique in the Add Paging to a Repeater tutorial.  That lesson utilizes the PagedDataSource class which encapsulates the paging-related properties of a data-bound control.

1.	Download and install the Northwind database.  Create this stored procedure.	create PROCEDURE [dbo].[GetSalesByCategoryWithPaging]     @startRowIndex INT,     @maximumRows INT,     @SalesCategoryCnt INT OUTPUT AS BEGIN     SELECT @SalesCategoryCnt=(SELECT COUNT(*) FROM         (SELECT C.CategoryName, ProductName          FROM [Order Details] OD          inner join Orders O on O.OrderID = OD.OrderID          inner join Products P on P.ProductID = OD.ProductID          inner join Categories C on C.CategoryID = P.CategoryID     GROUP BY C.CategoryName, ProductName) as salesCategoryCnt );     Declare @currRowIndex INT;     set @currRowIndex = (@startRowIndex * @maximumRows) + 1;     With SalesEntries as        (SELECT ROW_NUMBER() OVER (ORDER BY C.CategoryName ASC) as Row,                          C.CategoryName,                          ProductName,                          TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2),                              OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)         FROM [Order Details] OD         inner join Orders O on O.OrderID = OD.OrderID         inner join Products P on P.ProductID = OD.ProductID         inner join Categories C on C.CategoryID = P.CategoryID         GROUP BY C.CategoryName, ProductName )     Select * FROM SalesEntries     WHERE Row between @currRowIndex and @currRowIndex+@maximumRows-1     order by CategoryName asc END
2.	In Solution Explorer, create an App_Code folder.  Right click on the App_Code folder and select "Add New Item". Select "Add Dataset".
2.	Right click on Dataset Designer and select "Add->Table Adapter".
3.	Select a data connection. If you already have a connection string defined in your web.config, select that connection.
4.	Select "Use existing stored procedure" in the "Choose a Command Type" dialog.
5.	Select the GetSalesByCategoryWithPaging stored procedure that was created in step-1.
6.	Select "Return a DataTable in the "Choose Methods to Generate" dialog. Name the method GetSalesByCategoryWithPaging.
7.	Select "Finish" on the "Choose Methods to Generate" dialog.  This data table should be created.

Next Page