I often want to display something like a DataGrid on my web sites with next and prev buttons to scroll through the complete table. Using SQL Server 2005 it is very easy to build such controls that are using the new RowNumber feature to get only a page of rows that are displayed on the page. If you click on next you will get the next n rows.
For this example I build on new SQL stored procedure that will have two arguments, one for the current page index starting at 1 and the second one to specify the page size:
CREATE
PROCEDURE [dbo].[GetAddressList] @PageIndex INT, @PageSize INT AS BEGIN WITH AddressList AS ( SELECT ROW_NUMBER() OVER (ORDER BY Email DESC)AS RowNumber, Email, Company, FirstName, FamilyName, Password FROM dbo.Addresses ) SELECT FROM AddressList WHERE RowNumber BETWEEN (@PageIndex - 1) @PageSize + 1 AND @PageIndex * @PageSize END
On the server-side code I created a Ajax.NET Professional method that will return any page as a DataTable:
[AjaxPro.AjaxMethod] public static DataTable GetAllContacts(int pageIndex, int pageSize) { SqlConnection conn = new SqlConnection([...]);
try { conn.Open();
try { DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand("GetAddressList", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex; cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt);
return dt; } catch (Exception ex) { throw ex; } finally { conn.Close(); conn.Dispose(); } } catch (Exception ex) { throw ex; }
return null; }
Now, I started to create my JavaScript DataGrid that will have two buttons to scroll down and up. I have only optimized the page for Internet Explorer (no time, but there is no problem to get it running with Firefox or other web browsers, of course).
See http://www.ajaxpro.info/datagrid.aspx [1] for an example how the page looks like. Click on the text up and down on the right column to switch pages.
Use the FiddlerTool from http://www.fiddlertool.com/ [2] to see the data that is transfered for each page change. With the Northwind table Customers I get about 1.600 Bytes per page. I think this is fantastic!!!
Update 06/03/16: FireFox is working, now...