Click here to Skip to main content
Click here to Skip to main content

Gridview with SQL Paging

, 21 Jul 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
A simple and detailed ASP.NET program using Gridview with paging in SQL 2005

Introduction

This is a simple C# website that uses ASP Gridview to display records, but only displays partial data from the executed SQL Paging function.

Background

Back when I was not familiar with SQL 2005, I wondered what was new to SQL 2005 and how I could benefit from it. There I found the row_number() function, a function that is similar to table's auto-identity seeding, only that it is implemented during the execution of the query.

Here in my sample program. I'll show you how to extend the capability of row_number() to your ASP.NET web page!

Remember, the SQL I did here can be easily used as a stored procedure in your database.

Using the Code

Now, I'll discuss the GetSQL() method or our main SQL paging:

string GetSQL()
{
    /* My Generated SQL Paging */
    return @" 
                   
    /* Here we declare our main variable, this will be your 
    parameters when you use this as a Stored Procedure */ 

    DECLARE 
          @START    AS INT , 
          @MAX    AS INT , 
          @SORT    AS VARCHAR(100) , 
          @FIELDS AS VARCHAR(MAX) , 
          @OBJECT AS VARCHAR(MAX) 

    SELECT 
          @START = {3} ,
          @MAX = {4} , 
          @SORT = '{2}' , 
          @FIELDS = '{1}' ,
          @OBJECT = '{0}'

    /* CLEANING PARAMETER VALUES */
    IF (ISNULL(@SORT , '') = '') BEGIN SET @SORT = 'SELECT 1' END 
    IF (@START < 1) BEGIN SET @START = 1 END 
    IF (@MAX < 1) BEGIN SET @MAX = 1 END 

    /* SET THE LENGTH OF RESULT */
    DECLARE @END AS INT 
    SET @END = (@START + (@MAX - 1))

    /* Here we get the total rows therein based from the 
       Object or main SQL Query given to the parameter @object */ 

   /* GET THE TOTAL PAGE COUNT */
   DECLARE 
       @SQL_COUNT AS NVARCHAR(MAX) 

       SET @TOTAL = 0
       SET @SQL_COUNT = 'SELECT @GET_TOTAL = COUNT(*) 
                         FROM (' + @OBJECT + ') AS [TABLE_COUNT]'

       EXEC sp_executesql @SQL_COUNT, N'@GET_TOTAL INT OUTPUT', 
                                        @GET_TOTAL = @TOTAL OUTPUT 

    /* Here we are now creating the actual SQL paging script to 
       produce the desired partial records */ 

    /* GET THE RECORDS BASED FROM THE GIVEN STATEMENT AND CONDITION */
    DECLARE @SQL AS NVARCHAR(MAX)
    SET @SQL = 
        'SELECT ' + @FIELDS + ' 
         FROM 
         (
             SELECT 
                (ROW_NUMBER() OVER(ORDER BY ' + @SORT + ')) AS [ROWNUM] , * 
             FROM ( 
                     SELECT ' + @FIELDS + ' 
                     FROM (' + @OBJECT + ') AS [SOURCE_TABLE]
                   ) AS [SOURCE_COLLECTION]
             ) AS TMP
             WHERE 
                [ROWNUM] BETWEEN ' + CAST(@START AS VARCHAR(10)) + 
                 ' AND ' + CAST(@END AS VARCHAR(10)) + '
          '

          EXEC(@SQL) /* we now execute the script */
    ";
} 

Now we go to assigning values to a method. 

Here, we assign the Object or the main Query. We can use SQL VIEWS, but for this example, we assign plain query.

string MAIN_SQL = @" Select A.ProductId, A.ProductName, A.UnitPrice, A.UnitsInStock, 
                           B.CompanyName From Products AS A Inner Join Suppliers AS B on 
                          (B.SupplierId = A.SupplierId)";

Here, we assign the fields to be displayed on our GridView:

string FIELDS_TO_DISPLAY = 
	"ProductId, ProductName, UnitPrice, UnitsInStock,CompanyName";

Here, we assign the Fields to be sorted. In our example, it's just one field and is sorted Ascending:

string FIELDS_TO_BE_SORT = " ProductName ASC ";

Now we simply put the variable on its index assignment. Remember, if you are using a Stored Procedure, it will be much easier and more descriptive because in our example, we simply used the string format we have from .NET.

SQL = string.Format(SQL, MAIN_SQL, FIELDS_TO_DISPLAY, 
	FIELDS_TO_BE_SORT, rows_start, rows_per_page);

Next is just for you to try; these are only the major fields you need to know, others are ordinary codes which we use everyday.

I know there are still improvements that can be made to this article, so please feel free to leave your comments.

Points of Interest

I hope I did share a good article with you guys!

You can contact me at tom.bauto@gmail.com

History

  • [2009.05.27] - Tom Bauto { version 1.0.0 }

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Tom Bauto
Software Developer (Senior) RealPage, Inc.
Philippines Philippines
I am very passionate about software development
My daily interest is to contribute on innovations.
 
Let's collaborate, let me know at tom.bauto@gmail.com

Comments and Discussions

 
GeneralMy vote of 1 PinmemberMark Nischalke5-Jun-09 8:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141220.1 | Last Updated 21 Jul 2009
Article Copyright 2009 by Tom Bauto
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid