ASP.NET Core, Ajax.Net, C#, VB.NET, MS SQL Server, Oracle, JavaScript, JQuery
Today's Date: 12/11/2018

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: GridView using Select, Skip, Take data fetching approach

Requirements:

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: 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;
public static ProductsCollection SelectSkipAndTake(string sortByExpression, int start, int end)
{
    return SelectShared("[dbo].[aspx_Products_SelectSkipAndTake]"nullnulltruenull, 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 = truestring dynamicSQL = nullstring sortByExpression = nullint? start = nullint? 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)

)

AS

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.

ALTER PROCEDURE [dbo].[aspx_Products_SelectSkipAndTake]

(

    @start int,

    @end int,

    @sortByExpression varchar(200)

)

AS

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



Last Words:

Model Binding played a major role in making the Select, Skip, Take approach on the GridView. Now, anytime you need to do something more custom to the GridView's Sorting and/or Paging functionalities, all you have to do is use this little piece of technology advancement. So the only thing I did here is to explain how things worked. For a Senior Developer you probably don't even need my explanation, just look at the code and you get it.

Note: In the code download make sure to change the following to your database credentials.

string connectionString = @"Data Source=localhost;Initial Catalog=Northwind;User ID=YOURUSERNAMEHERE;Password=YOURPASSWORDHERE";


Code Download (VB): Click here to download the code


Demonstration: Click here to see the demo

Code Download: Click here to download the code

As always, the code and the article are provided "As Is", there is absolutely no warranties. Use at your own risk.

Happy Coding!!!

Date Created: Friday, June 14, 2013



Post A Comment
Everything is required. Note: Your Email will not be posted along with the comment. We do not send unsolicited emails.