Download source - 5.82 KB

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 }

Gridview with SQL Paging