Filtering on each column in GridView as Excel Sheet functionality - Part 2

Tweet

Previously, we had seen a post taking about filtering on each GridView column as like we do in Excel sheet. That post provides basic functionalities as below –

1. The GridView should show down arrow on each column, which can be decided by the programmer.
2. When clicking the down arrow on a particular column, the system should show distinct list of the values in a dropdown window (each of the value much have a check box to select the value).
3. There should be two button OK and Cancel in that dropdown window.
4. The user can select one or more values in the list and press OK button. When clicking the OK button, the system should query the records based on the selected values in the dropdown list and show in the GridView.
5. When selecting multiple columns, the values must be filtered as per the multiple column values. Note: The filtering will be based on AND operator on all the columns.
   For Ex: When the user selects some countries on Country column and some courier types in CourierType column, then the query will be selected the rows where the countries are in selected countries and courier types are in selected courier types. (There is no OR operator involved anywhere here)
6. When clicking Cancel button, the window should hide. When pressing mouse outside of the window the dropdown window must hide.
7. There should be a clear all icon in the dropdown window, which helps the user to clear all the selection done in that particular column. So, by pressing that clear all icon, the system should clear the selected values and call the server to bind the records by clearing the selection.

In previous post, we did not see the values in the dropdown itself are filtered as per the available records in the GridView (as Excel sheet does). So this post talks about two main functionalities which are missing in previous post.

1. When user selecting some values on a particular column, the records will be filtered as per the values selected. When user selecting some other column, the dropdown list shows all the values from the database irrespective of the values which contains the previous filtered record values or not.
   What it means that, a GridView have three columns in a GridView such as Country, State, City. When user selects country as India and press OK, the GridView records will be filtered which contains India. When user wants to filter on State, the user needs to see the states on the dropdown list which are only under India and not other countries. (In previous post example we did not seen any filtering condition on dropdown values).
2. In sometime, the GridView needs to show different database column and the return values are in different.
   For Ex: The GridView shows Customer Name, but after selecting some customer name in the filtering dropdown list the code needs to build the SQL Query by having Customer Ids instead of Name.

Addition to this, I have one cosmetic change in the Example –

1. Once filtering done on a particular column, the dropdown icon on that column needs to be changed to different icon as Excel sheet shown.

The implementation follows –

The OrderView
I have a view for selecting rows

CREATE VIEW OrderView
AS
SELECT dbo.Orders.OrderID,
  dbo.Orders.CustomerID,
  dbo.Customers.CompanyName AS CustomerName,
  dbo.Orders.ShipVia,
  dbo.Shippers.CompanyName AS ShipViaName,
        dbo.Orders.OrderDate,
        dbo.Orders.ShipName,
        dbo.Orders.ShipCity,
        dbo.Orders.ShipCountry
FROM    dbo.Orders INNER JOIN
dbo.Shippers ON dbo.Shippers.ShipperID = dbo.Orders.ShipVia INNER JOIN
dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID

The GridView source

<asp:GridView ID="grdViewOrders" runat="server"
    AllowPaging="True" AutoGenerateColumns="False" TabIndex="1"
    DataKeyNames="OrderID" Width="100%" GridLines="None"
    CellPadding="3" CellSpacing="1" AllowSorting="false" BorderWidth="1px" 
    onpageindexchanging="grdViewOrders_PageIndexChanging">
    <Columns>
        <asp:BoundField DataField="OrderID" HeaderText="Order ID" SortExpression="OrderID" >
            <ItemStyle Width="7%" />
        </asp:BoundField>
        <asp:BoundField DataField="OrderDate" HeaderText="Order Date" SortExpression="OrderDate" DataFormatString="{0:dd-MMM-yyyy}" >
            <ItemStyle Width="10%" />
        </asp:BoundField>
        <asp:BoundField DataField="CustomerID" HeaderText="Customer ID" SortExpression="CustomerID" >
            <ItemStyle Width="7%" />
        </asp:BoundField>
        <asp:BoundField DataField="CustomerName" HeaderText="Customer Name" SortExpression="CustomerName" >
            <ItemStyle Width="20%" />
        </asp:BoundField>
        <asp:BoundField DataField="ShipViaName" HeaderText="Ship Via" SortExpression="ShipVia" >
            <ItemStyle Width="16%" />
        </asp:BoundField>
        <asp:BoundField DataField="ShipName" HeaderText="Ship Name" SortExpression="ShipName" >
            <ItemStyle Width="20%" />
        </asp:BoundField>
        <asp:BoundField DataField="ShipCity" HeaderText="Ship City" SortExpression="ShipCity" >
            <ItemStyle Width="10%" />
        </asp:BoundField>
        <asp:BoundField DataField="ShipCountry" HeaderText="Ship Country" SortExpression="ShipCountry" >
            <ItemStyle Width="10%" />
        </asp:BoundField>
    </Columns>
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Right" />
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <EditRowStyle BackColor="#999999" />
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
<asp:Button ID="Refresh" runat="server" Text="Refresh" OnClick="Refresh_Click" />
<asp:HiddenField ID="hndSelectedValue" runat="server" Value="" />

The C# code behind

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindGrid();
        }
    }

    private string ArrayToString(IEnumerable strValues)
    {
        string strValue = string.Empty;
        foreach (string strVal in strValues)
            strValue = strValue + "'" + strVal + "', ";
        if (strValue.Length > 0)
            strValue = "(" + strValue.Substring(0, strValue.Length - 2) + ")";

        return strValue;
    }

    private void BindGrid()
    {
        string strQuery = @"SELECT * FROM OrderView";
        var objJavaScriptSerializer = new JavaScriptSerializer();

        SelectedDataCollection objSelectedDataCollection = new SelectedDataCollection();;
        if (hndSelectedValue.Value.Trim().Length > 0)
        {
            objSelectedDataCollection = objJavaScriptSerializer.Deserialize(hndSelectedValue.Value);

            foreach (SelectedData selectedData in objSelectedDataCollection.DataCollection)
            {
                if (selectedData.SelectedValue.Count() > 0)
                {
                    string strColumnName = selectedData.ColumnName;
                    if (selectedData.ColumnName == "CustomerName") // This If condition required only for columns which are dublicated values (CustomerID, CustomerName - both are same values)
                        strColumnName = "CustomerID";

                    strQuery = strQuery + ((strQuery.Contains(" WHERE ") == true) ? " AND " : " WHERE ") + strColumnName + " IN " + ArrayToString(selectedData.SelectedValue);
                }
            }
        }

        OrderDAO objProductDAO = new OrderDAO();

        IList orderViewList = objProductDAO.GetOrders(strQuery);
        if (orderViewList.Count == 0)
            orderViewList.Add(new OrderView());

        grdViewOrders.DataSource = orderViewList;
        grdViewOrders.DataBind();

        for (int intRowIndex = 0; intRowIndex < grdViewOrders.Columns.Count; intRowIndex++)
        {
            TableCell tblCell = ((TableCell)grdViewOrders.HeaderRow.Cells[intRowIndex]);
            tblCell.CssClass = "filter";
            tblCell.Attributes.Add("alt", grdViewOrders.Columns[intRowIndex].SortExpression);
                
            if (objSelectedDataCollection.DataCollection != null)
            {
                foreach (SelectedData selectedData in objSelectedDataCollection.DataCollection)
                {
                    if ((selectedData.ColumnName == grdViewOrders.Columns[intRowIndex].SortExpression) &&
                        (selectedData.SelectedValue.Count() > 0))
                    {
                        tblCell.CssClass = "filterExist";
                    }
                }
            }
        }
    }

    protected void Refresh_Click(object sender, EventArgs e)
    {
        BindGrid();
    }

    protected void grdViewOrders_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        grdViewOrders.PageIndex = e.NewPageIndex;
        BindGrid();
    }
}

The Style Sheet

th.filter
{
 background: url(../../images/ShowFilter.png) right bottom no-repeat;
 cursor:pointer;
}
th.filterExist
{
 background: url(../../images/FilterExist.png) right bottom no-repeat;
 cursor:pointer;
}

The JQuery script

$(document).ready(function () {
    $('[class^="filter"]').click(function () {

        var pos = $(this).position();
        var width = $(this).outerWidth();
        var height = $(this).outerHeight();
        var colName = $(this).attr("alt");
        var hiddenControlValue = $('#MainContent_hndSelectedValue').val();

        jQuery.support.cors = true;

        $.ajax({

            type: "GET",
            url: "http://localhost:1609/Service1.svc/data?ColumnName=" + colName + "&AlreadySelectedValue=" + hiddenControlValue,
            processData: false,
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            data: "{}",

            success: function (response) {

                var filterdiv = $("<div id='divFilterList'></div>");
                var div = $("<div></div>");
                var valuesdiv = $("<div id='filterChkBox'></div>");
                var table = $("<table />");
                var SelectedValue = {};

                if (hiddenControlValue.length > 0) {
                    var alreadySelectedValueInfo = jQuery.parseJSON(hiddenControlValue);
                    for (var index = 0; index < alreadySelectedValueInfo.DataCollection.length; index++) {
                        if (alreadySelectedValueInfo.DataCollection[index].ColumnName == colName) {
                            SelectedValue = alreadySelectedValueInfo.DataCollection[index].SelectedValue;
                        }
                    }
                }

                $.each(response, function (index, obj) {
                    var IsSelected = false;
                    for (var index = 0; index < SelectedValue.length; index++) {
                        if (SelectedValue[index] == obj.Id)
                            IsSelected = true;
                    }

                    if (IsSelected == true)
                        table.append("<tr><td><input name='options' checked='checked' type='checkbox' value='" + obj.Id + "'>" + obj.Value + "</td></tr>");
                    else
                        table.append("<tr><td><input name='options' type='checkbox' value='" + obj.Id + "'>" + obj.Value + "</td></tr>");
                });

                valuesdiv.append(table);
                div.append(valuesdiv);
                filterdiv.append(div);
                $('body').append(filterdiv);

                var buttondiv = $("<div></div>").css({
                    textAlign: "right",
                    paddingTop: "5px",
                    paddingRight: "5px"
                }).show();

                var clearFilterBtn = $("<button>.</button>");
                clearFilterBtn.css({ height: "22px",
                    width: "25px",
                    border: "none",
                    background: "url(../../images/ClearFilter.png) center center no-repeat",
                    cursor: "pointer"
                });
                buttondiv.append(clearFilterBtn);

                var okBtn = $("<button>OK</button>");
                okBtn.css({ height: "22px", width: "70px" });
                buttondiv.append(okBtn);

                var cancelBtn = $("<button>Cancel</button>");
                cancelBtn.css({ height: "22px", width: "70px" });
                buttondiv.append(cancelBtn);

                cancelBtn.click(function () {
                    var container = $("#divFilterList");
                    container.detach();
                });

                okBtn.click(function () {

                    var SelectedDataCollection = {};

                    var ArrSelectedColumnsInfo = new Array(); // It hold all column selected info

                    var SelectedData = {};
                    SelectedData["ColumnName"] = colName;

                    var ArrSelectedValue = new Array();
                    $('#filterChkBox table tr td input:checked').each(function () {
                        ArrSelectedValue.push($(this).val());
                    });
                    SelectedData["SelectedValue"] = ArrSelectedValue;

                    var IsExist = false;
                    var alreadySelectedValueInfo;

                    if (hiddenControlValue.length > 0) {
                        var alreadySelectedValueInfo = jQuery.parseJSON(hiddenControlValue);
                        for (var index = 0; index < alreadySelectedValueInfo.DataCollection.length; index++) {
                            if (alreadySelectedValueInfo.DataCollection[index].ColumnName == colName) {
                                alreadySelectedValueInfo.DataCollection[index] = SelectedData;
                                SelectedDataCollection = alreadySelectedValueInfo;
                                        
                                IsExist = true;
                                break;
                            }
                        }
                    }

                    if (IsExist == false) {
                        var alreadySelectedValueInfo = new Array();
                        if (hiddenControlValue.length > 0) {
                            alreadySelectedValueInfo = jQuery.parseJSON(hiddenControlValue);
                            if (SelectedData.SelectedValue.length > 0) {
                                alreadySelectedValueInfo["DataCollection"].push(SelectedData);
                            }
                            SelectedDataCollection = alreadySelectedValueInfo;
                        }
                        else {
                            alreadySelectedValueInfo.push(SelectedData);
                            SelectedDataCollection["DataCollection"] = alreadySelectedValueInfo;
                        }
                    }

                    $('#<%= hndSelectedValue.ClientID %>').val(JSON.stringify(SelectedDataCollection));
                    document.getElementById('<%= Refresh.ClientID %>').click();
                });

                clearFilterBtn.click(function () {
                    if (hiddenControlValue.length > 0) {
                        var alreadySelectedValueInfo = jQuery.parseJSON(hiddenControlValue);
                        alreadySelectedValueInfo.DataCollection = jQuery.grep(alreadySelectedValueInfo.DataCollection, function (value) {
                            return value.ColumnName != colName;
                        });

                        $('#<%= hndSelectedValue.ClientID %>').val(JSON.stringify(alreadySelectedValueInfo));
                    }
                    document.getElementById('<%= Refresh.ClientID %>').click();
                });

                filterdiv.append(buttondiv);

                var tblwidth = table.outerWidth();
                valuesdiv.css({ width: tblwidth + "px" }).show();

                div.css({
                    overflow: "auto",
                    textAlign: "left",
                    paddingLeft: "5px",
                    paddingTop: "1px",
                    marginLeft: "1px",
                    marginTop: "1px",
                    backgroundColor: "White",
                    border: "1px solid gray",
                    height: "207px",
                    width: "200px"
                }).show();

                var left = 0;
                if (pos.left + width - 200 - 10 < 0)
                    left = 0;
                else
                    left = pos.left + width - 200 - 10;

                filterdiv.css({
                    position: "absolute",
                    textAlign: "left",
                    backgroundColor: "white",
                    border: "1px solid black",
                    color: "black",
                    height: "243px",
                    width: "209px",
                    top: pos.top + height + 1 + "px",
                    left: left + "px"
                }).show();
            },
            error: function (xhr, status, error) {
                alert(error);
            }
        });

    });
    $(document).mouseup(function (e) {
        var container = $("#divFilterList");

        if (container.has(e.target).length === 0) {
            container.detach();
        }
    });
});

The OrderDAO class

public class OrderDAO
{
    public IList<OrderView> GetOrders()
    {
        return GetOrders(string.Empty);
    }
    public IList<OrderView> GetOrders(string strQuery)
    {
        SqlConnection connection = null;

        try
        {
            connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            if (strQuery == string.Empty)
                strQuery = @"SELECT * FROM OrderView";

            SqlCommand command = new SqlCommand();
            command.CommandText = strQuery;
            command.Connection = connection;

            command.Connection.Open();
            SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);

            IList<OrderView> orderViewList = new List<OrderView>();

            while (dataReader.Read())
            {
                OrderView orderView = new OrderView();
                orderView.OrderID = Convert.ToInt32(dataReader["OrderID"].ToString());
                orderView.CustomerID = dataReader["CustomerID"].ToString();
                orderView.CustomerName = dataReader["CustomerName"].ToString();
                orderView.ShipViaName = dataReader["ShipViaName"].ToString();
                orderView.OrderDate = Convert.ToDateTime(dataReader["OrderDate"].ToString());
                orderView.ShipVia = Convert.ToInt16(dataReader["ShipVia"].ToString());
                orderView.ShipName = dataReader["ShipName"].ToString();
                orderView.ShipCity = dataReader["ShipCity"].ToString();
                orderView.ShipCountry = dataReader["ShipCountry"].ToString();

                orderViewList.Add(orderView);
            }

            connection.Close();

            //return
            return orderViewList;
        }
        catch (Exception ex)
        {
            if (connection != null) connection.Close();

            // Log
            throw ex;
        }
    }
}

The service Service1 used for getting the distinct list of values for the dropdown window.

[ServiceContract]
public interface IService1
{
    [OperationContract]
    [WebGet(UriTemplate = "/data?ColumnName={strColumnName}&AlreadySelectedValue={strAlreadySelectedValue}", ResponseFormat = WebMessageFormat.Json)]
    FilterValueSet[] GetDistinctValue(string strColumnName, string strAlreadySelectedValue);
}

public class Service1 : IService1
{
    public FilterValueSet[] GetDistinctValue(string strColumnName, string strAlreadySelectedValue)
    {
        SqlConnection connection = null;

        try
        {

            connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());

            string strSQL = string.Empty;
            switch (strColumnName)
            {
                case "OrderID":
                    strSQL = @"SELECT distinct OrderID, OrderID FROM OrderView ";
                    break;
                case "OrderDate":
                    strSQL = @"SELECT distinct OrderDate, CONVERT(VARCHAR(11), OrderDate, 106) AS [OrderDate] FROM OrderView ";
                    break;
                case "CustomerID":
                    strSQL = @"SELECT distinct CustomerID, CustomerID FROM OrderView ";
                    break;
                case "CustomerName":
                    strSQL = @"SELECT distinct CustomerID, CustomerName FROM OrderView ";
                    break;
                case "ShipVia":
                    strSQL = @"SELECT distinct ShipVia, ShipViaName FROM OrderView ";
                    break;
                case "ShipName":
                    strSQL = @"SELECT distinct ShipName, ShipName FROM OrderView ";
                    break;
                case "ShipCity":
                    strSQL = @"SELECT distinct ShipCity, ShipCity FROM OrderView ";
                    break;
                case "ShipCountry":
                    strSQL = @"SELECT distinct ShipCountry, ShipCountry FROM OrderView ";
                    break;
            }

            SelectedDataCollection objSelectedDataCollection = null;
                
            if (strAlreadySelectedValue.Trim().Length > 0)
            {
                var objJavaScriptSerializer = new JavaScriptSerializer();
                objSelectedDataCollection = new SelectedDataCollection();
                objSelectedDataCollection = objJavaScriptSerializer.Deserialize<SelectedDataCollection>(strAlreadySelectedValue.Trim());

                foreach (SelectedData selectedData in objSelectedDataCollection.DataCollection)
                {
                    if (selectedData.ColumnName != strColumnName)
                    {
                        if (selectedData.SelectedValue.Count<string>() > 0)
                        {
                            string strTempColumnName = selectedData.ColumnName;
                            if (selectedData.ColumnName == "CustomerName") // This If condition required only for columns which are dublicated values (CustomerID, CustomerName - both are same values)
                                strTempColumnName = "CustomerID";
                            strSQL = strSQL + ((strSQL.Contains(" WHERE ") == true) ? " AND " : " WHERE ") + strTempColumnName + " IN " + ArrayToString(selectedData.SelectedValue);
                        }
                    }
                }
            }

            SqlCommand command = new SqlCommand();
            command.CommandText = strSQL;
            command.Connection = connection;

            command.Connection.Open();

            SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);

            IList<FilterValueSet> filterValueList = new List<FilterValueSet>();

            while (dataReader.Read())
            {
                filterValueList.Add(new FilterValueSet
                {
                    Id = dataReader[0].ToString(),
                    Value = dataReader[1].ToString()
                });
            }
            connection.Close();

            return filterValueList.ToArray<FilterValueSet>();
        }
        catch (Exception ex)
        {
            if (connection != null) connection.Close();

            // Log
            throw ex;
        }
    }
    private string ArrayToString(IEnumerable<string> strValues)
    {
        string strValue = string.Empty;
        foreach (string strVal in strValues)
            strValue = strValue + "'" + strVal + "', ";
        if (strValue.Length > 0)
            strValue = "(" + strValue.Substring(0, strValue.Length - 2) + ")";

        return strValue;
    }
}

The OrderView entity class

public class OrderView
{
    public int OrderID { get; set; }
    public string CustomerID { get; set; }
    public string CustomerName { get; set; }
    public string ShipViaName { get; set; }
    public DateTime OrderDate { get; set; }
    public int ShipVia { get; set; }
    public string ShipName { get; set; }
    public string ShipCity { get; set; }
    public string ShipCountry { get; set; }
}

Helper classes

public class SelectedData
{
    public string ColumnName { get; set; }
    public IEnumerable<string> SelectedValue { get; set; }
}
public class SelectedDataCollection
{
    public IEnumerable<SelectedData> DataCollection { get; set; }
}
public class FilterValueSet
{
    public string Id { get; set; }
    public string Value { get; set; }
}

Note: The Column Name to return when selecting values in the dropdown window require to mention as SortExpression property in the GridView Column.

When a column needs to show one values (Name) and to return as different values (Id), mention the column to return as SortExpression column. The .net code (in the service) will fetch Id (to return value) and Name (to show value). In this exaple, the ShipVia column shows the Shipper Name and returning Shipper Id. The same way, Customer Name column shows Customer Name and returning Customer ID.

When there are two column returns same values from the dropdown window (Customer Id, Customer Name returning same column - Customer Id), the code behind code needs to handle to get the column name.

This implementation works well with the IE 9.0, Firefox 10.0 and Chrome 21.0 (other browsers I have not tested).

download the working example of the source code in C# here and in VB here.

Important Note: I call the service with localhost port number 1609 (http://localhost:1609/Service1.svc) as the service published like that in my system. If your service running in different port, please change the port number. If you are downloading the source file and running, make sure the port number is same, or change the source code as per the new port number the service running. (To know the port number, you can run the application and get to know from localhost url or check in the Web tab in project properties.)

The output of the example shown as below