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

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: GridView with inline search

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.


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:
Let me point out a few things in the .aspx page from top to bottom.

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. 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;
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

)

AS

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.

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

)

AS

BEGIN

  SET NOCOUNT ON;

 

  WITH temporaryTableOnly AS

  (

         SELECT

         [ProductID],

         [ProductName],

         [SupplierID],

         [CategoryID],

         [QuantityPerUnit],

         [UnitPrice],

         [UnitsInStock],

         [UnitsOnOrder],

         [ReorderLevel],

         [Discontinued],

         ROW_NUMBER() OVER

           (

            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

         ) AS 'RowNum'

     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)

  )

  SELECT * FROM temporaryTableOnly

  WHERE RowNum BETWEEN @start AND @end

END


Cancel Search:

When you click the Search Image Button and the search is valid, the Cancel Search Image Button shows up. And you probably already guessed it, all it does is cancel the search when clicked. It will:
protected void IBtnCancelSearch_Click(object sender, ImageClickEventArgs e)
{
    IBtnCancelSearch.Visible = false;
 
    // clear control values
    TxtProductID.Text = String.Empty;
    TxtProductName.Text = String.Empty;
    DdlSupplierID.SelectedValue = String.Empty;
    DdlCategoryID.SelectedValue = String.Empty;
    TxtQuantityPerUnit.Text = String.Empty;
    TxtUnitPrice.Text = String.Empty;
    TxtUnitsInStock.Text = String.Empty;
    TxtUnitsOnOrder.Text = String.Empty;
    TxtReorderLevel.Text = String.Empty;
    DdlDiscontinued.SelectedValue = String.Empty;
 
    // clear sorting and paging
    LitSortExpression.Text = String.Empty;
    LitSortDirection.Text = String.Empty;
 
    FillGridView(String.Empty);
 
    // reset paging
    int pageCount = (Products.GetRecordCount() - 1) / GridView1.PageSize + 1;
    BuildPager(pageCount);
 
    // clear sort images
    ImgSortProductID.ImageUrl = "~/Images/Spacer.gif";
    ImgSortProductName.ImageUrl = "~/Images/Spacer.gif";
    ImgSortSupplierID.ImageUrl = "~/Images/Spacer.gif";
    ImgSortCategoryID.ImageUrl = "~/Images/Spacer.gif";
    ImgSortQuantityPerUnit.ImageUrl = "~/Images/Spacer.gif";
    ImgSortUnitPrice.ImageUrl = "~/Images/Spacer.gif";
    ImgSortUnitsInStock.ImageUrl = "~/Images/Spacer.gif";
    ImgSortUnitsOnOrder.ImageUrl = "~/Images/Spacer.gif";
    ImgSortReorderLevel.ImageUrl = "~/Images/Spacer.gif";
    ImgSortDiscontinued.ImageUrl = "~/Images/Spacer.gif";
}



The GridView is filled using another Middle tier call, the same made when this page first loaded. We will not discuss the Middle Tier, Data Tier, and Stored Procedure calls here, but it very similar to the search process we discussed above, except that this one is not passing any search parameters. Please see the downloadable code for more information on this.

private void FillGridView(string sortExpression)
{
    int startRowIndex = (Convert.ToInt32(LitCurrentPage.Text) - 1) * GridView1.PageSize;
    GridView1.DataSource = Products.SelectSkipAndTake(GridView1.PageSize, startRowIndex, sortExpression);
    GridView1.DataBind();
}


Last Words:

The search header in the GridView web control looked liked it was integrated but was actually built using regular ASP.NET web controls we all know and love with very simple JavaScripting. Because it's not a component, as ASP.NET developers we pretty much knew things that are happening, for example we knew that when you click the search button that it will call the OnClick delegate for that button. 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. The good thing here is that even though we disabled some functionalities in the GridView by hiding the title header and disabling Paging, we were able to put it back by adding a few methods, and most of all, the user would never have suspected that we did anything special at all. So sorting and paging still worked like it was stock functionality.

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 7, 2013



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