Blog Detail

Home
Blog
Detail

Integrating Inline Search in a GridView's Header

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

Introduction:

This tutorial will show you how to add an inline search capability to an ASP.NET 4.5 GridView web control. Or for AspxFormsGen 4.5 Professional Plus owners, this will show you what's happening in the generated code. 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, jquery UI, and a jquery plug-in used for validation, other than that, the controls used should be very familiar, they're either an ASP.NET web control or an html control. The example will show how to search an MS SQL Server database table using a stored procedure and then filling the GridView with the results using a Select/Skip/Take approach. The code itself will be in a N-Tier structure:

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

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.

Front End (Web Form):

The front end uses an ASP.NET 4.5 Web Form, both the .aspx and .aspx.cs (code behind) files. In it is a GridView web control. The search header located just below the field titles looks integrated in the GridView, but this is not the case. To make it look this way, we need to:

Make the GridView's width fixed. E.g. Width="1400"
Make each of the GridView's Columns fixed. E.g. the ItemStyle-CssClass="inlineGridViewItemStyle" style for each GridView column has a fixed width: 100px;
Hide the GridView's header. E.g. ShowHeader="false" . Although this is hidden, the sort and delete functionalities will still work.
Add a table just before the GridView also with the same fixed width and style as the GridView. This table will contain the ASP.NET web controls used for searching.
Because we hid the GridView's header, we loose the clickable/sortable column titles. We add the titles and sort image back in the same table as the search control header.
We also set the AllowPaging="False" on the GridView as seen in the SkinFile.skin. This will hide the pager normally seen in the footer and remove the built-in paging functionality.
To add back the paging functionality, we need to add another table just after the GridView setting the style to the same style attributes as the GridView's footer.

Let me point out a few things in the .aspx page from top to bottom.

On the very top we set EnableEventValidation="false" this is because we manually placed javascript calls used for sorting (e.g. href="javascript:__doPostBack('<%=GridView1.UniqueID %>','Sort$ProductID');" ) in anchor links inside the UpdatePanel control, and everytime the anchor control is clicked, it issues a javascript call that does a postback from inside the UpdatePanel which will trigger a runtime error if we don't set EnableEventValidation="false". By default this is set to true.
```
<%@ Page Title="Products" Language="C#" EnableEventValidation="false" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="GridViewSearch_Products.aspx.cs" Inherits="Northwind.GridViewSearch_Products" %>
```
The hidden literal controls; LitSortExpression, LitSortDirection, LitCurrentPage, and ListLastParam are used for remembering sorting and paging variables. We could have as easily used a Session for each one of these, the only problem is that when you leave this page, the Session(s) will still be alive. One of the cons of using ASP.NET web controls instead of Session(s) is that it will add viewstate information on the page, which will make the page a bit more bloated than it should be.
```
<asp:Literal ID="LitSortExpression" Visible="false" runat="server" />
<asp:Literal ID="LitSortDirection" Visible="false" runat="server" />
<asp:Literal ID="LitCurrentPage" Text="1" Visible="false" runat="server" />
<asp:Literal ID="ListLastParam" Visible="false" runat="server" />
```
The deleteConfirmationDialog and errorDialog divs are used by jquery UI to show a modal box for delete confirmation and when an error occurs during deletion respectively.
```
<div id="deleteConfirmationDialog"></div>
<div id="errorDialog" title="An error occured during item deletion!"></div>
```

The HyperLink controls; HlnkAdd and HlnkAddNewRecord will redirect the user to a web page for addition of a new record.

<asp:HyperLink ID="HlnkAdd" NavigateUrl="~/AddEdit_Products.aspx?operation=add" ImageUrl="~/Images/Add.gif" ToolTip="Add New Products" runat="server" />
<asp:HyperLink ID="HlnkAddNewRecord" Text="Add New Products" NavigateUrl="~/AddEdit_Products.aspx?operation=add" runat="server" />

The UpdatePanel houses the GridView with inline search functionality. To make the search work and make the search look integrated into the GridView, we need to create a table as the header, a GridView which contains the data, and then another table as the footer which will contain the paging functionality.

GridView with inline search structure

1. Clickable links (column titles) and Image controls used to show the sort direction image can be found in the first table's 1st row.

     <table class="gridviewGridLines" cellspacing="0" cellpadding="8" rules="all" border="1" id="MainContent_GridView1" style="color:Black; width: 1400px;border-collapse:collapse;">
	<tr style="color:White;background-color:#5D7B9D;font-weight:bold;">
	    <th scope="col">
	        <a href="javascript:__doPostBack('<%=GridView1.UniqueID %>','Sort$ProductID');" style="color:White;">Product ID</a>
	        <asp:Image ID="ImgSortProductID" ImageUrl="~/Images/Spacer.gif" AlternateText="" runat="server" />
	    </th>
	    <th scope="col">
	        <a href="javascript:__doPostBack('<%=GridView1.UniqueID %>','Sort$ProductName');" style="color:White;">Product Name</a>
	        <asp:Image ID="ImgSortProductName" ImageUrl="~/Images/Spacer.gif" AlternateText="" runat="server" />
	    </th>
                .

                .

                .

The 2nd row contains fixed-width ASP.NET web controls used to enter search filters. You will also find the Search Button and Cancel Search Button here.

     <tr>
	<td style="width:100px; text-align: center;"><asp:TextBox ID="TxtProductID" SkinID="TextBoxInline" runat="server" /></td>
	<td style="width:100px; text-align: center;"><asp:TextBox ID="TxtProductName" SkinID="TextBoxInline" runat="server" /></td>
	<td style="width:100px; text-align: center;"><asp:DropDownList ID="DdlSupplierID" SkinID="DropDownInline" SelectMethod="GetSuppliersDropDownListData" DataValueField="SupplierID" DataTextField="CompanyName" AppendDataBoundItems="true" runat="server"><asp:ListItem Value="">Select One</asp:ListItem></asp:DropDownList></td>
	<td style="width:100px; text-align: center;"><asp:DropDownList ID="DdlCategoryID" SkinID="DropDownInline" SelectMethod="GetCategoriesDropDownListData" DataValueField="CategoryID" DataTextField="CategoryName" AppendDataBoundItems="true" runat="server"><asp:ListItem Value="">Select One</asp:ListItem></asp:DropDownList></td>
	<td style="width:100px; text-align: center;"><asp:TextBox ID="TxtQuantityPerUnit" SkinID="TextBoxInline" runat="server" /></td>
	<td style="width:100px; text-align: center;"><asp:TextBox ID="TxtUnitPrice" SkinID="TextBoxInline" runat="server" /></td>
	<td style="width:100px; text-align: center;"><asp:TextBox ID="TxtUnitsInStock" SkinID="TextBoxInline" runat="server" /></td>
	<td style="width:100px; text-align: center;"><asp:TextBox ID="TxtUnitsOnOrder" SkinID="TextBoxInline" runat="server" /></td>
	<td style="width:100px; text-align: center;"><asp:TextBox ID="TxtReorderLevel" SkinID="TextBoxInline" runat="server" /></td>
	<td style="text-align: center;">
	    <asp:DropDownList ID="DdlDiscontinued" SkinID="DropDownInline" runat="server">
	        <asp:ListItem Value="">Select One</asp:ListItem>
	        <asp:ListItem Value="true">True</asp:ListItem>
	        <asp:ListItem Value="false">False</asp:ListItem>
	    </asp:DropDownList>
	</td>
	<td style="width:60px; text-align: center;">
	    <asp:ImageButton ID="IBtnSearch" runat="server" ToolTip="Click to search" ImageUrl="~/Images/Search.png" Width="16" Height="16" BorderStyle="None" BackColor="Transparent" OnClick="IBtnSearch_Click" />
	    <asp:ImageButton ID="IBtnCancelSearch" Visible="false" runat="server" ToolTip="Cancel search" ImageUrl="~/Images/Cancel.png" Width="16" Height="16" BorderStyle="None" BackColor="Transparent" OnClick="IBtnCancelSearch_Click" CausesValidation="false" />
	</td>
	<td style="width:30px;"></td>
    </tr>

2. After the table is the GridView. One thing to notice here is that the GridView is not using Model Binding even though this is an ASP.NET 4.5 web form. The reason is because it is databound in the code behind everytime the Search Button, Cancel Search Button, Column Titles, and Paging Links is clicked. I'll explain more on this when we discuss the Code Behind code.

<asp:GridView ID="GridView1" runat="server" DataKeyNames="ProductID"
    onsorting="GridView1_Sorting" OnRowDeleting="GridView1_RowDeleting" ShowHeader="false"
	onrowdatabound="GridView1_RowDataBound" SkinID="GridViewProfessionalFixedNoPaging" Width="1400">
	<Columns>
		<asp:BoundField DataField="ProductID" HeaderText="Product ID" ReadOnly="true" SortExpression="ProductID" ItemStyle-HorizontalAlign="Right" ItemStyle-CssClass="inlineGridViewItemStyle" />
		<asp:BoundField DataField="ProductName" HeaderText="Product Name" ReadOnly="true" SortExpression="ProductName" ItemStyle-CssClass="inlineGridViewItemStyle" />
		<asp:HyperLinkField DataTextField="SupplierID" HeaderText="Supplier ID" DataNavigateUrlFields="SupplierID" DataNavigateUrlFormatString="~/Detail_Suppliers.aspx?supplierid={0}" SortExpression="SupplierID" ItemStyle-HorizontalAlign="Right" ItemStyle-CssClass="inlineGridViewItemStyle" />
		<asp:HyperLinkField DataTextField="CategoryID" HeaderText="Category ID" DataNavigateUrlFields="CategoryID" DataNavigateUrlFormatString="~/Detail_Categories.aspx?categoryid={0}" SortExpression="CategoryID" ItemStyle-HorizontalAlign="Right" ItemStyle-CssClass="inlineGridViewItemStyle" />
		<asp:BoundField DataField="QuantityPerUnit" HeaderText="Quantity Per Unit" ReadOnly="true" SortExpression="QuantityPerUnit" ItemStyle-CssClass="inlineGridViewItemStyle" />
		<asp:BoundField DataField="UnitPrice" HeaderText="Unit Price" ReadOnly="true" SortExpression="UnitPrice" DataFormatString="{0:c}" HtmlEncode="false" ItemStyle-HorizontalAlign="Right" ItemStyle-CssClass="inlineGridViewItemStyle" />
		<asp:BoundField DataField="UnitsInStock" HeaderText="Units In Stock" ReadOnly="true" SortExpression="UnitsInStock" DataFormatString="{0:N0}" HtmlEncode="false" ItemStyle-HorizontalAlign="Right" ItemStyle-CssClass="inlineGridViewItemStyle" />
		<asp:BoundField DataField="UnitsOnOrder" HeaderText="Units On Order" ReadOnly="true" SortExpression="UnitsOnOrder" DataFormatString="{0:N0}" HtmlEncode="false" ItemStyle-HorizontalAlign="Right" ItemStyle-CssClass="inlineGridViewItemStyle" />
		<asp:BoundField DataField="ReorderLevel" HeaderText="Reorder Level" ReadOnly="true" SortExpression="ReorderLevel" DataFormatString="{0:N0}" HtmlEncode="false" ItemStyle-HorizontalAlign="Right" ItemStyle-CssClass="inlineGridViewItemStyle" />
		<asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued" ReadOnly="true" SortExpression="Discontinued" ItemStyle-HorizontalAlign="Center" />
        <asp:TemplateField>
            <ItemStyle Width="60px" HorizontalAlign="Center" />
            <ItemTemplate>
                <a href="AddEdit_Products.aspx?operation=update&productid=<%# Eval("ProductID") %>" title="Click to edit"><img src="Images/Edit.gif" alt="" style="border: none;" /></a>
            </ItemTemplate>
        </asp:TemplateField>
		<asp:TemplateField>
			<ItemStyle Width="30px" HorizontalAlign="Center" />
			<ItemTemplate>
				<asp:ImageButton ID="IBtnDelete" runat="server" ToolTip="Click to delete"
					CommandArgument='<%# Eval("ProductID") %>' BorderStyle="None" BackColor="Transparent"
					OnClientClick="javascript:return deleteItem(this.name, this.alt);"
					ImageUrl="~/Images/Delete.png" AlternateText='<%# Eval("ProductID") %>'
					Width="16" Height="16"
					CommandName="Delete" />
			</ItemTemplate>
		</asp:TemplateField>
	</Columns>
	<EmptyDataTemplate>No records found!</EmptyDataTemplate>
</asp:GridView>

3. The 3rd part is another table that contains a Literal Control which shows the pager links. The pager links are dynamically computed from the Code Behind file so it can show the right page the GridView is currently on.

    <table class="gridviewGridLines" cellspacing="0" cellpadding="8" rules="all" border="1" style="color:Black; width: 1400px;border-collapse:collapse;">
	<tr class="gridviewPagerStyle" align="center" style="color:White;background-color:#5D7B9D;">
	    <td><asp:Literal ID="LitPager" runat="server" /></td>
	</tr>
    </table>

The Code Behind is where most of the exciting things are happening.

The GridView will fetch just the number of rows it needs just to fill the current page it is on. The Page Size for the GridView is the example is 16, therefore, it will only fetch 16 or less items at a time. It will fetch less than 16 if the search results is less than the Page Size (16). If there's more than 16 results and you're on Page 2, it will skip the first 16 rows and fetch the next 16. This is where the Select, Skip, Take methodology is used. For the Data-Tier to know how many rows we need (Select), how many rows to Skip, and how many row to Take, we need to pass this information to the Middle Tier, and then the Middle-Tier class will pass it on to the Data Tier. Of course, the Data Tier would then pass this information as parameters to the respective Stored Procedure. See the n-tier flow here: https://www.junnark.com/Products/AspxCodeGen4/NTierLayerApproach.

The numbers we need to pass all the way to the Stored Procedure will come from various things and are affected by the events coming from the the UI Layer (Front End). Other than the numbers needed by the Select/Skip/Take, we also need to know if the results or data are sorted on a specific field, and whether it should be in Ascending or Descending order. With these in mind, let's look at the Page Load event.

One of the most important part of this search integration is the ability to know what operation was called when one of the ASP.NET web controls does a post back. The Request["__EVENTARGUMENT"] tells us exactly this. For our purposes, we need to know if a Sorting or Paging event was thrown.

protected void Page_Load(object sender, EventArgs e)
{
    string parameter = Request["__EVENTARGUMENT"];
 
    SetSortImage(parameter);
    SetPaging(parameter);
}

When the sorting event is called, the Request["__EVENTARGUMENT"] will return the word "Sort$" followed by the field name being sorted. E.g. "Sort$ProductName", which means sort the ProductName field. When a paging event is called the Request["__EVENTARGUMENT"] will return the word "Page$" followed by the Page Number. E.g. "Page$2" which means go to page 2.

The SetSortImage method simply sets the Image used to show the Sort Direction for the respective field being sorted.

The SetPaging method will fill the GridView with data based on the filters we talked about above. It will also dynamically build the pager.

private void SetPaging(string parameter)
{
    if (!String.IsNullOrEmpty(parameter) && parameter.Contains("Page$"))
        LitCurrentPage.Text = parameter.Replace("Page$", "");
 
    if (parameter != ListLastParam.Text)
    {
        if (IBtnCancelSearch.Visible)
        {
            FillGridViewDataSourceUsingSearch();
        }
        else
        {
            FillGridView((LitSortExpression.Text + " " + LitSortDirection.Text).Trim());
            ListLastParam.Text = parameter;
 
            int pageCount = (Products.GetRecordCount() - 1) / GridView1.PageSize + 1;
            BuildPager(pageCount);
        }
    }
}
 
private void BuildPager(int pageCount)
{
    int currentPage = Convert.ToInt32(LitCurrentPage.Text);
    int nearestTen = Functions.RoundToNearestTens(currentPage);
 
    StringBuilder pager = new StringBuilder();
    pager.Append("<table><tr>");
 
    if (nearestTen > 10)
    {
        int previousPage = nearestTen - 10;
        pager.Append("<td><a href='javascript:__doPostBack(&#39;ctl00$MainContent$GridView1&#39;,&#39;Page$1&#39;)' style='color:#333333;'>< First</a></td>");
        pager.Append("<td><a href='javascript:__doPostBack(&#39;ctl00$MainContent$GridView1&#39;,&#39;Page$" + previousPage + "&#39;)' style='color:#333333;'>...</a></td>");
    }
 
    for (int i = (nearestTen - 9); i <= nearestTen; i++)
    {
        if (i == currentPage)
            pager.Append("<td><span style='font-size:12px;'>" + i + "</span></td>");
        else
            pager.Append("<td><a href='javascript:__doPostBack(&#39;ctl00$MainContent$GridView1&#39;,&#39;Page$" + i + "&#39;)' style='color:#333333;'>" + i + "</a></td>");
 
        if (nearestTen > pageCount && i == pageCount)
            break;
    }
 
    if (pageCount > nearestTen)
    {
        int nextPage = nearestTen + 1;
        pager.Append("<td><a href='javascript:__doPostBack(&#39;ctl00$MainContent$GridView1&#39;,&#39;Page$" + nextPage + "&#39;)' style='color:#333333;'>...</a></td>");
        pager.Append("<td><a href='javascript:__doPostBack(&#39;ctl00$MainContent$GridView1&#39;,&#39;Page$" + pageCount + "&#39;)' style='color:#333333;'>Last ></a></td>");
    }
 
    pager.Append("</tr></table>");
    LitPager.Text = pager.ToString();
}

Search Field(s) Validation

Search Field Validation

Validation is done server side but have a client side look to it using jquery UI's modal box. When the search icon is clicked, we first validate user's entries or whether they entered anything at all before we actually do the search.

protected void IBtnSearch_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
    if (IsSearchFieldsValid())
    {
        FillGridViewDataSourceUsingSearch(true);
 
        if (!IBtnCancelSearch.Visible)
            IBtnCancelSearch.Visible = true;
    }
    else
    {
        Functions.ShowModalHtmlError(_validationErrors, this, "Validation error occured");
    }
}

private bool IsSearchFieldsValid()
{
    bool isValid = true;
    StringBuilder sb = new StringBuilder();
    _validationErrors = String.Empty;
 
    // check if at least one control has a value
    if(String.IsNullOrEmpty(TxtProductID.Text) && String.IsNullOrEmpty(TxtProductName.Text) && String.IsNullOrEmpty(DdlSupplierID.SelectedValue) && String.IsNullOrEmpty(DdlCategoryID.SelectedValue) && String.IsNullOrEmpty(TxtQuantityPerUnit.Text) && String.IsNullOrEmpty(TxtUnitPrice.Text) && String.IsNullOrEmpty(TxtUnitsInStock.Text) && String.IsNullOrEmpty(TxtUnitsOnOrder.Text) && String.IsNullOrEmpty(TxtReorderLevel.Text) && String.IsNullOrEmpty(DdlDiscontinued.SelectedValue))
    {
        sb.Append("- At least one search value must be filled<br>");
        isValid = false;
    }
    else
    {
        if (!String.IsNullOrEmpty(TxtProductID.Text))
        {
            int productID;
            bool isProductIDValid = Int32.TryParse(TxtProductID.Text, out productID);
 
            if (!isProductIDValid)
            {
                sb.Append("- Product ID is an invalid number<br>");
                isValid = false;
            }
        }
 
        if (!String.IsNullOrEmpty(TxtUnitPrice.Text))
        {
            decimal unitPrice;
            bool isUnitPriceValid = Decimal.TryParse(TxtUnitPrice.Text, out unitPrice);
 
            if (!isUnitPriceValid)
            {
                sb.Append("- Unit Price is an invalid number<br>");
                isValid = false;
            }
        }
 
        if (!String.IsNullOrEmpty(TxtUnitsInStock.Text))
        {
            Int16 unitsInStock;
            bool isUnitsInStockValid = Int16.TryParse(TxtUnitsInStock.Text, out unitsInStock);
 
            if (!isUnitsInStockValid)
            {
                sb.Append("- Units In Stock is an invalid number<br>");
                isValid = false;
            }
        }
 
        if (!String.IsNullOrEmpty(TxtUnitsOnOrder.Text))
        {
            Int16 unitsOnOrder;
            bool isUnitsOnOrderValid = Int16.TryParse(TxtUnitsOnOrder.Text, out unitsOnOrder);
 
            if (!isUnitsOnOrderValid)
            {
                sb.Append("- Units On Order is an invalid number<br>");
                isValid = false;
            }
        }
 
        if (!String.IsNullOrEmpty(TxtReorderLevel.Text))
        {
            Int16 reorderLevel;
            bool isReorderLevelValid = Int16.TryParse(TxtReorderLevel.Text, out reorderLevel);
 
            if (!isReorderLevelValid)
            {
                sb.Append("- Reorder Level is an invalid number<br>");
                isValid = false;
            }
        }
 
    }
 
    if (!isValid)
        _validationErrors = sb.ToString();
 
    return isValid;
}

Search

There are some rules we established based on the search built here. Note: Of course you can change these by adding more controls on the search header and more logic to the Code Behind file and Stored Procedure.

Each column you fill with a search value creates a chain of "AND" parameters. E.g. if you fill ProductName and QuantityPerUnit: Search Where ProductName is Like 'Value' AND QuantityPerUnit is like 'Value'
String value columns such as the ProductName, QuantityPerUnit will be search using SQL's LIKE keyword. E.g. Select * From TableName Where ProductName Like '%ProductName%'.
Number, Date, Boolean, and Foreign Keys are searched using the exact EQUAL value. E.g. Select * From TableName Where ProductID = 1.
Nullable Boolean fields whose value is a null will be considered a FALSE value.

The code below shows that every variable which is respective of each search web control is Nullable and a value is only assigned for the one's the user is searching or filtering for.

Another important part of this code is highlighted below. This is the part where we're assigning data to the GridView control. Notice Products.SelectSkipAndTakeDynamicWhere , this line of code is calling the Middle Tier Class.

private void FillGridViewDataSourceUsingSearch(bool isFromSearchButton = false)
{
    // everything is nullable, only items being searched for should be filled
    int? productID = null;
    string productName = null;
    int? supplierID = null;
    int? categoryID = null;
    string quantityPerUnit = null;
    decimal? unitPrice = null;
    Int16? unitsInStock = null;
    Int16? unitsOnOrder = null;
    Int16? reorderLevel = null;
    bool? discontinued = null;
 
    if (!String.IsNullOrEmpty(TxtProductID.Text))
        productID = Convert.ToInt32(TxtProductID.Text);
 
    if (!String.IsNullOrEmpty(TxtProductName.Text))
        productName = TxtProductName.Text;
 
    if (!String.IsNullOrEmpty(DdlSupplierID.SelectedValue))
        supplierID = Convert.ToInt32(DdlSupplierID.SelectedValue);
 
    if (!String.IsNullOrEmpty(DdlCategoryID.SelectedValue))
        categoryID = Convert.ToInt32(DdlCategoryID.SelectedValue);
 
    if (!String.IsNullOrEmpty(TxtQuantityPerUnit.Text))
        quantityPerUnit = TxtQuantityPerUnit.Text;
 
    if (!String.IsNullOrEmpty(TxtUnitPrice.Text))
        unitPrice = Convert.ToDecimal(TxtUnitPrice.Text);
 
    if (!String.IsNullOrEmpty(TxtUnitsInStock.Text))
        unitsInStock = Convert.ToInt16(TxtUnitsInStock.Text);
 
    if (!String.IsNullOrEmpty(TxtUnitsOnOrder.Text))
        unitsOnOrder = Convert.ToInt16(TxtUnitsOnOrder.Text);
 
    if (!String.IsNullOrEmpty(TxtReorderLevel.Text))
        reorderLevel = Convert.ToInt16(TxtReorderLevel.Text);
 
    if (!String.IsNullOrEmpty(DdlDiscontinued.SelectedValue))
        discontinued = Convert.ToBoolean(DdlDiscontinued.SelectedValue);
 
    string sortExpression = (LitSortExpression.Text + " " + LitSortDirection.Text).Trim();
    string parameter = Request["__EVENTARGUMENT"];
    int totalRowCount;
    int startRowIndex;
 
    if (isFromSearchButton || (!String.IsNullOrEmpty(parameter) && parameter.Contains("Sort$")))
    {
        startRowIndex = 0;
        LitCurrentPage.Text = "1";
    }
    else
        startRowIndex = (Convert.ToInt32(LitCurrentPage.Text) - 1) * GridView1.PageSize;
 
    GridView1.DataSource = Products.SelectSkipAndTakeDynamicWhere(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued, GridView1.PageSize, startRowIndex, out totalRowCount, sortExpression);
    GridView1.DataBind();
 
    int pageCount = (totalRowCount - 1) / GridView1.PageSize + 1;
    BuildPager(pageCount);
}

Middle Tier

The Middle Tier or alternatively Business Object code is where we put business computations/logic. Here we return the totalRecordCount 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 search result. We also assign the field to be sorted if none was passed, this happens when you first load the web page or when you cancel the search. After doing these very simple computations, we then call the respective Data Tier/Data Layer code ProductsDataLayer.SelectSkipAndTakeDynamicWhere.

public static ProductsCollection SelectSkipAndTakeDynamicWhere(int? productID, string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice, Int16? unitsInStock, Int16? unitsOnOrder, Int16? reorderLevel, bool? discontinued, int maximumRows, int startRowIndex, out int totalRowCount, string sortByExpression)
{
    totalRowCount = GetRecordCountDynamicWhere(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued);
 
    if (String.IsNullOrEmpty(sortByExpression))
        sortByExpression = "ProductID";
 
    return ProductsDataLayer.SelectSkipAndTakeDynamicWhere(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued, sortByExpression, startRowIndex, maximumRows);
}

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_SelectSkipAndTakeWhereDynamic] and passing parameters to it. The first 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 SelectSkipAndTakeDynamicWhere(int? productID, string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice, Int16? unitsInStock, Int16? unitsOnOrder, Int16? reorderLevel, bool? discontinued, string sortByExpression, int start, int end)
{
    string storedProcName = "[dbo].[aspx_Products_SelectSkipAndTakeWhereDynamic]";
    SqlConnection connection = Dbase.GetConnection();
    SqlCommand command = Dbase.GetCommand(storedProcName, connection);
 
    // select, skip, take, sort parameters
    command.Parameters.AddWithValue("@start", start);
    command.Parameters.AddWithValue("@end", end);
    command.Parameters.AddWithValue("@sortByExpression", sortByExpression);
 
    // search parameters
    AddSearchCommandParamsShared(command, productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued);
 
    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;
}

And then we also pass each one of the search parameter which is nullable as mentioned above. We pass a System.DBNull.Value when we're not filtering for that specific value, otherwise, we we pass the search value.

private static void AddSearchCommandParamsShared(SqlCommand command, int? productID, string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice, Int16? unitsInStock, Int16? unitsOnOrder, Int16? reorderLevel, bool? discontinued)
{
    if(productID != null)
        command.Parameters.AddWithValue("@productID", productID);
    else
        command.Parameters.AddWithValue("@productID", System.DBNull.Value);
 
    if(!String.IsNullOrEmpty(productName))
        command.Parameters.AddWithValue("@productName", productName);
    else
        command.Parameters.AddWithValue("@productName", System.DBNull.Value);
 
    if(supplierID != null)
        command.Parameters.AddWithValue("@supplierID", supplierID);
    else
        command.Parameters.AddWithValue("@supplierID", System.DBNull.Value);
 
    if(categoryID != null)
        command.Parameters.AddWithValue("@categoryID", categoryID);
    else
        command.Parameters.AddWithValue("@categoryID", System.DBNull.Value);
 
    if(!String.IsNullOrEmpty(quantityPerUnit))
        command.Parameters.AddWithValue("@quantityPerUnit", quantityPerUnit);
    else
        command.Parameters.AddWithValue("@quantityPerUnit", System.DBNull.Value);
 
    if(unitPrice != null)
        command.Parameters.AddWithValue("@unitPrice", unitPrice);
    else
        command.Parameters.AddWithValue("@unitPrice", System.DBNull.Value);
 
    if(unitsInStock != null)
        command.Parameters.AddWithValue("@unitsInStock", unitsInStock);
    else
        command.Parameters.AddWithValue("@unitsInStock", System.DBNull.Value);
 
    if(unitsOnOrder != null)
        command.Parameters.AddWithValue("@unitsOnOrder", unitsOnOrder);
    else
        command.Parameters.AddWithValue("@unitsOnOrder", System.DBNull.Value);
 
    if(reorderLevel != null)
        command.Parameters.AddWithValue("@reorderLevel", reorderLevel);
    else
        command.Parameters.AddWithValue("@reorderLevel", System.DBNull.Value);
 
    if(discontinued != null)
        command.Parameters.AddWithValue("@discontinued", discontinued);
    else
        command.Parameters.AddWithValue("@discontinued", System.DBNull.Value);
}

Once all parameters are passed, we then fill a DataSet, then from the DataSet we fill our strongly-typed ojbect, ProductsCollection objProductsCol which is really just a List<Products> generic collection which is then returned to the calling client, the Middle Tier.

Stored Procedure

The Stored Procedure grabs data from the database based on the parameters passed to it from the Data Tier. Notice that each one of the search parameters are nullable. Pay particular attention to the WHERE filters, this is where the search magic happens. Everything inside the open and closing parenthesis will always be True. For example:

([ProductID] = @productID OR @productID IS NULL)

if a @productID is passed then it's , or when @productID is a NULL, or both are satisfied, in any of the 3 instances it will still return a true. So even though there's an AND keyword for each parameter, each parameter filter will return a True.

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 the search result came back with 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_SelectSkipAndTakeWhereDynamic]

(

@start int,

@end int,

@sortByExpression varchar(200),

@productID int = NULL,

@productName nvarchar(80) = NULL,

@supplierID int = NULL,

@categoryID int = NULL,

@quantityPerUnit nvarchar(40) = NULL,

@unitPrice money = NULL,

@unitsInStock smallint = NULL,

@unitsOnOrder smallint = NULL,

@reorderLevel smallint = NULL,

@discontinued bit = NULL

)

BEGIN

SET NOCOUNT ON;

SELECT

[ProductID],

[ProductName],

[SupplierID],

[CategoryID],

[QuantityPerUnit],

[UnitPrice],

[UnitsInStock],

[UnitsOnOrder],

[ReorderLevel],

[Discontinued]

FROM [dbo].[Products]

WHERE

([ProductID] = @productID OR @productID IS NULL) AND

([ProductName] LIKE '%' + @productName + '%' OR @productName IS NULL) AND

([SupplierID] = @supplierID OR @supplierID IS NULL) AND

([CategoryID] = @categoryID OR @categoryID IS NULL) AND

([QuantityPerUnit] LIKE '%' + @quantityPerUnit + '%' OR @quantityPerUnit IS NULL) AND

([UnitPrice] = @unitPrice OR @unitPrice IS NULL) AND

([UnitsInStock] = @unitsInStock OR @unitsInStock IS NULL) AND

([UnitsOnOrder] = @unitsOnOrder OR @unitsOnOrder IS NULL) AND

([ReorderLevel] = @reorderLevel OR @reorderLevel IS NULL) AND

([Discontinued] = @discontinued OR @discontinued IS NULL)

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.