SQL Server 2005 and Ajax.NET Professional - RowNumber Example

Tags: .NET, AJAX, Ajax.NET, ASP.NET, JavaScript

Wednesday, March 15, 2006 1:13:00 PM

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 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/ 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...

11 Comments

  • Craig Nicholson said

    Just a few tips regarding your code example.

    You are calling "throw ex" in your catch clause which will effectively hide the real location of your exception as it will generate a new stack trace. Rather re-throw the same exception by calling "throw" with no parameter.

    Instead of catching Exception it is considered best practice to catch the specific exceptions that could be handled. Remember that not all exceptions inherit from Exception either. So maybe use a catch all-exception handler where needed.

    Another point, SqlConnection.Close is synonymous with SqlConnection.Dispose, there is no need to call both, but no harm in doing so either.

    Normally I'd make use of the IDisposable interface as per the pattern and call it as follows:

    try
    {
    using (SqlConnection conn = new SqlConnection([...]))
    {
    conn.Open();
    // ...
    }
    }
    catch ()
    {
    throw;
    }
    return null

  • rodrigo diniz said

    Thank you. It would be great if we had some way of using the DataGrid (or another control).
    I have been commenting about Ajax.Net on my blog for some time now , about some minor failures.
    Go check it out

  • raj said

    This is not a question related to this topic but for the last 2 weeks i am searching for response.write or response methods useing ajax, can we implement response.redirect through ajax and how?

    have any idea

  • Steve said

    Could you also show in your examples what is needed from the javascript side?

    What I see above is just server side asp.net - without seeing the javascript I don't see the tie in of what ajax.net provides.

    Thanks

Comments have been disabled for this content.