Blog Detail

Home
Blog
Detail

Fetch Data into a GridView using the Select, Skip, Take Approach

Technologies Used: ASP.NET, SQL, C#, VB.NET, Model Binding

Introduction:

This tutorial will show you how an ASP.NET 4.5 GridView web control can Select data, Take a set amount of records, Skipping past a few records starting from a set index. Or for AspxFormsGen 4.5 Professional Plus owners, this will show you what's happening in the generated code for web forms using a GridView. If you're an ASP.NET developer then the concepts and coding here will be very easy and simple to understand. The code will be using jquery and jquery UI other than that, the controls used should be very familiar, they're either an ASP.NET web control or an html control. Rather than selecting all the records from a specific database table, we will fetch just the number of records needed depending on the Sort Expression and Page we are in the GridView. We will call this tutorial the Select, Skip, Take approach. The code used here will be in a N-Tier structure:

Front End (User Interface)
Middle-Tier (business objects)
Data Tier (data layer)
Stored Procedures

GridView using Select, Skip, Take data fetching approach

Requirements:

Visual Studio 2012
.NET Framework 4.5 (ASP.NET 4.5)
Microsoft SQL Server (Express is Fine)
Microsoft Northwind Database (please google it)
AspxFormsGen 4.5 Professional Plus (Optional)

Note: The code example uses Model-Binding, a new feature in ASP.NET 4.5, that's why you will need Visual Studio 2012 and the .NET Framework 4.5 or higher. Everything in the code download and the samples here was generated by AspxFormsGen 4.5 Professional Plus. You don't need this tool for this tutorial. About 99% of all Web Forms (with a GridView) generated by AspxFormsGen 4.5 Professional Plus uses the Select, Skip, Take approach. The code sample was taken from the generated code for GridView, Read-Only web form. was

Front End (User Interface - UI):

ASP.NET 4.5 uses a new technology called Model-Binding. Model Binding is instrumental in how we implement the Select, Skip, Take approach using a GridView web control. First notice the ItemType="Northwind.BusinessObject.Products". This is telling the GridView that each item/row will be this type. You can either put the fully qualified Business Object name here like we did (which is recommended) or just the type itself, e.g. Products. We can bind a GridView using the SelectMethod property's Public Method.

<asp:GridView ID="GridView1" runat="server" DataKeyNames="ProductID"
    ItemType="Northwind.BusinessObject.Products" SelectMethod="GetGridData"
	onrowdatabound="GridView1_RowDataBound" onrowcreated="GridView1_RowCreated" SkinID="GridViewProfessional">
	<Columns>
		<asp:BoundField DataField="ProductID" HeaderText="Product ID" ReadOnly="true" SortExpression="ProductID" ItemStyle-HorizontalAlign="Right" />
		<asp:BoundField DataField="ProductName" HeaderText="Product Name" ReadOnly="true" SortExpression="ProductName" />
        <asp:TemplateField HeaderText="Supplier ID" SortExpression="SupplierID" HeaderStyle-Wrap="false">

To see the full UI code, go here: https://www.junnark.com/Products/AspxFormsGen45/Demos/SampleCode/GridView_Read_Only_UI_CS.html

The Code Behind GetGridData method catches this method call:

public ProductsCollection GetGridData(int maximumRows, int startRowIndex, out int totalRowCount, string sortByExpression)
{
    return Products.SelectSkipAndTake(maximumRows, startRowIndex, out totalRowCount, sortByExpression);
}

To see the full UI code, go here: https://www.junnark.com/Products/AspxFormsGen45/Demos/SampleCode/GridView_Read_Only_Codebehind_CS.html

Notice that there are 4 parameters in this method. These parameters are required so we can implement the Select, Skip, Take approach. The GetGridData will be called everytime there's a postback on the GridView, this would include Sorting and Paging calls. Here's a quick explanation of each the parameter functions:

maximumRows: The number of records to retrieve. This will be tied to the GridView's PageSize property. So if the PageSize="16", then only 16 records will be retrieved.
startRowIndex: The index to start fetching data from. This is automatically computed by the GridView based on the PageSize and Page number that was clicked on the GridView. This is Zero-based.
totalRowCount: Total number of all records. The GridView needs to know this number so it can display the right amount of Paging in the footer.
sortByExpression: The field name and sort order. E.g. "ProductName desc" which means Sort ProductName in descending order or just "ProductName" which means Sort ProductName in ascending order.

A call to the Middle Tier (or Business Object) is made here as shown in the highlighted code above: Products.SelectSkipTake.

Middle Tier (Business Object):

The Middle Tier or alternatively Business Object code is where we put business computations/logic. Here we return the totalRowCount so the GridView control can compute how many pages is needed and the total records we should have retrieved if we were to retrieve everything for the specific table. We also assign the field to be sorted if none was passed, this happens when you first load the web page. After doing these very simple computations, we then call the respective Data Tier/Data Layer code ProductsDataLayer.SelectSkipAndTake.

public static ProductsCollection SelectSkipAndTake(int maximumRows, int startRowIndex, out int totalRowCount, string sortByExpression)
{
    totalRowCount = GetRecordCount();
    int end = startRowIndex + maximumRows;
 
    if (String.IsNullOrEmpty(sortByExpression))
        sortByExpression = "ProductID";
 
    return ProductsDataLayer.SelectSkipAndTake(sortByExpression, startRowIndex, end);
}

Data Tier

The Data Tier or alternatively the Data Layer code is where all calls to the database or storage is placed. For example, this is the only tier where references to System.Data.SqlClient should be in. We're calling a Stored Procedure named [dbo].[aspx_Products_SelectSkipAndTake] and passing parameters to it. The 3 parameters should be familiar by now;

@start: An integer to tell the Stored Procedure where to start to Select and Take data.
@end: An integer to tell the Stored Procedure how many rows to retrieve starting from the @start.
@sortByExpression: The field name and sort order. E.g. "ProductName desc" which means Sort ProductName in descending order or just "ProductName" which means Sort ProductName in ascending order.

public static ProductsCollection SelectSkipAndTake(string sortByExpression, int start, int end)
{
    return SelectShared("[dbo].[aspx_Products_SelectSkipAndTake]", null, null, true, null, sortByExpression, start, end);
}

This calls a shared/static method which basically calls the respective Stored Procedure and fills the objProductsCol, which is then returned to the calling Middle-Tier code.

public static ProductsCollection SelectShared(string storedProcName, string param, object paramValue, bool isUseStoredProc = true, string dynamicSQL = null, string sortByExpression = null, int? start = null, int? end = null)
{
    SqlConnection connection = Dbase.GetConnection();
    SqlCommand command;
 
    if (isUseStoredProc)
        command = Dbase.GetCommand(storedProcName, connection);
    else
        command = new SqlCommand(dynamicSQL, connection);
 
    // select, skip, take, sort parameters
    if (!String.IsNullOrEmpty(sortByExpression) && start != null && end != null)
    {
        command.Parameters.AddWithValue("@start", start.Value);
        command.Parameters.AddWithValue("@end", end.Value);
        command.Parameters.AddWithValue("@sortByExpression", sortByExpression);
    }
 
    // parameters
    switch (param)
    {
        case "supplierID":
            command.Parameters.AddWithValue("@supplierID", paramValue);
            break;
        case "categoryID":
            command.Parameters.AddWithValue("@categoryID", paramValue);
            break;
        default:
            break;
    }
 
    DataSet ds = Dbase.GetDbaseDataSet(command);
    ProductsCollection objProductsCol = new ProductsCollection();
 
    if (ds.Tables[0].Rows.Count > 0)
    {
        foreach(DataRow dr in ds.Tables[0].Rows)
        {
            Products objProducts = CreateProductsFromDataRowShared(dr);
            objProductsCol.Add(objProducts);
        }
    }
 
    command.Dispose();
    connection.Close();
    connection.Dispose();
    ds.Dispose();
 
    return objProductsCol;
}

Stored Procedure

The Stored Procedure knows what row of data to start getting from and the number of rows/records to return using these 2 lines.

OFFSET @start ROWS
FETCH NEXT @end ROWS ONLY

Let's say that there are a total of 30 sorted rows/records. If the client (Data Tier) is only asking for 10 (@end) records, starting from row 11 (@start), then SQL will Skip the first 10 rows and start Selecting from row 11, Taking just 15 rows. In short it will Select rows and Skip the first 10, and only Take 15 rows and return this to the calling client. Hence, Select/Skip/Take.

ALTER PROCEDURE [dbo].[aspx_Products_SelectSkipAndTake]

(

@start int,

@end int,

@sortByExpression varchar(200)

)

BEGIN

SET NOCOUNT ON;

SELECT

[ProductID],

[ProductName],

[SupplierID],

[CategoryID],

[QuantityPerUnit],

[UnitPrice],

[UnitsInStock],

[UnitsOnOrder],

[ReorderLevel],

[Discontinued]

FROM [dbo].[Products]

ORDER BY

CASE WHEN @sortByExpression = 'ProductID' THEN [ProductID] END,

CASE WHEN @sortByExpression = 'ProductID desc' THEN [ProductID] END DESC,

CASE WHEN @sortByExpression = 'ProductName' THEN [ProductName] END,

CASE WHEN @sortByExpression = 'ProductName desc' THEN [ProductName] END DESC,

CASE WHEN @sortByExpression = 'SupplierID' THEN [SupplierID] END,

CASE WHEN @sortByExpression = 'SupplierID desc' THEN [SupplierID] END DESC,

CASE WHEN @sortByExpression = 'CategoryID' THEN [CategoryID] END,

CASE WHEN @sortByExpression = 'CategoryID desc' THEN [CategoryID] END DESC,

CASE WHEN @sortByExpression = 'QuantityPerUnit' THEN [QuantityPerUnit] END,

CASE WHEN @sortByExpression = 'QuantityPerUnit desc' THEN [QuantityPerUnit] END DESC,

CASE WHEN @sortByExpression = 'UnitPrice' THEN [UnitPrice] END,

CASE WHEN @sortByExpression = 'UnitPrice desc' THEN [UnitPrice] END DESC,

CASE WHEN @sortByExpression = 'UnitsInStock' THEN [UnitsInStock] END,

CASE WHEN @sortByExpression = 'UnitsInStock desc' THEN [UnitsInStock] END DESC,

CASE WHEN @sortByExpression = 'UnitsOnOrder' THEN [UnitsOnOrder] END,

CASE WHEN @sortByExpression = 'UnitsOnOrder desc' THEN [UnitsOnOrder] END DESC,

CASE WHEN @sortByExpression = 'ReorderLevel' THEN [ReorderLevel] END,

CASE WHEN @sortByExpression = 'ReorderLevel desc' THEN [ReorderLevel] END DESC,

CASE WHEN @sortByExpression = 'Discontinued' THEN [Discontinued] END,

CASE WHEN @sortByExpression = 'Discontinued desc' THEN [Discontinued] END DESC

OFFSET @start ROWS

FETCH NEXT @end ROWS ONLY

END

Note: The OFFSET ROWS FETCH NEXT ROWS ONLY keywords are new to Microsoft T-SQL 2012. So if you're using an older version MS SQL, then these commands will not be available. The good news is that the script can be written in a way that it work the same way as the Stored Procedure above. If you used AspxFormsGen 4.5 Professional Plus to generate code, it will be smart enough to recognize that you're either using SQL 2012 or older and will generate the Stored Procedure accordingly.

Here's the Stored Procedure for MS SQL 2008 and older.

The main difference here is that the @end parameter is the actual end row. So it's like saying fetch data From @start to @end. The @end here is no longer the number of rows to return. Because of this change, you should also change what you're passing from the UI, Middle Tier, Data Tier, to this Stored Procedure for the @end value. AspxFormsGen 4.5 Professional Plus users need not worry, code is again generated based on the SQL version you have.