Thursday, 10 April 2014

Filter and Sorting in GridView using DataView in ASP.NET using C#/VB.NET

Hi friends,in this article i will explain about Filter and Sorting in GridView using DataView.
I already explained in the previous articles about Create your own captcha image generator in ASP.NET using C#.NET/VB.NET,How to create a RDLC report using asp.net with c# /VB.NET and Create ,list and Alter the trigger associated with a table with SQL Server?
Write the below code in ASP.NET
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Filter and Sorting in GridView using DataView</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table>
            <tr>
                <td>
                    Enter ID:
                </td>
                <td>
                    <asp:TextBox ID="txtId" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Enter User Name:
                </td>
                <td>
                    <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    <asp:Button ID="btnSearch" runat="server" Text="Search"
                        onclick="btnSearch_Click"></asp:Button>
                </td>
            </tr>
        </table>
        <asp:GridView ID="GridFilter" runat="server" BackColor="White" BorderColor="#CC9966"
            AllowPaging="True" PageSize="5" BorderStyle="Solid" AutoGenerateColumns="False"
            BorderWidth="1px" CellPadding="4" Font-Names="Georgia" DataKeyNames="User_ID"
            Font-Size="Small" OnSorting="GridFilter_Sorting" AllowSorting="true">
            <Columns>
                <asp:BoundField DataField="User_ID" HeaderText="User_ID" SortExpression="User_ID" />
                <asp:BoundField DataField="UserName" HeaderText="User Name" SortExpression="UserName" />
                <asp:BoundField DataField="Gender" HeaderText="Gender" SortExpression="Gender" />
                <asp:BoundField DataField="Country" HeaderText="Country" SortExpression="Country" />
            </Columns>
            <FooterStyle BackColor="Tan" />
            <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
        </asp:GridView>
    </div>
    </form>
</body>
</html>

In C#:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class GridRowFilter : System.Web.UI.Page
{
    DataSet ds;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            GridFilter.DataSource = BindUsers();
            GridFilter.DataBind();
        }
    }
    private DataSet BindUsers()
    {
        ds = new DataSet();
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
        SqlCommand cmd = new SqlCommand("Select User_ID,UserName,Gender,Country from User_Details", con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(ds);
        return ds;

    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        try
        {
            ds = BindUsers();
            DataTable dt = new DataTable();
            dt = ds.Tables[0];

            DataView dvData = dt.DefaultView;
            string strFilter = " 1=1 ";

            if (!string.IsNullOrEmpty(txtId.Text))
                strFilter = strFilter + " And User_ID = " + Convert.ToInt32(txtId.Text);
            if (!string.IsNullOrEmpty(txtUserName.Text))
                strFilter = strFilter + " And UserName Like '%" + txtUserName.Text + "%'";
            dvData.RowFilter = strFilter;
            GridFilter.DataSource = dvData;
            GridFilter.DataBind();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    protected void GridFilter_Sorting(object sender,GridViewSortEventArgs e)
    {
        try
        {
            DataSet ds = BindUsers();
            DataTable dt = new DataTable();
            dt = ds.Tables[0];
            DataView dvData = dt.DefaultView;
            if (ViewState["SortDirection"] != null && ViewState["SortDirection"].ToString() != "")
                dvData.Sort = e.SortExpression + " " + ConvertSortDirection(ViewState["SortDirection"].ToString());
            else
                dvData.Sort = e.SortExpression + " " + ConvertSortDirection("ASC");

            GridFilter.DataSource = dvData;
            GridFilter.DataBind();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    private string ConvertSortDirection(string sortDireciton)
    {
        try
        {
            if (sortDireciton != null)
            {
                switch (sortDireciton)
                {
                    case "ASC":
                        ViewState["SortDirection"] = "DESC";
                        break;
                    case "DESC":
                        ViewState["SortDirection"] = "ASC";
                        break;
                }
            }
            return sortDireciton;
        }
        catch (Exception err)
        {
            throw err;
        }

    }
}


In VB.NET:
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Partial Public Class GridRowFilter
    Inherits System.Web.UI.Page
    Private ds As DataSet
    Protected Sub Page_Load(sender As Object, e As EventArgs)
        If Not IsPostBack Then
            GridFilter.DataSource = BindUsers()
            GridFilter.DataBind()
        End If
    End Sub
    Private Function BindUsers() As DataSet
        ds = New DataSet()
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)
        Dim cmd As New SqlCommand("Select User_ID,UserName,Gender,Country from User_Details", con)
        Dim da As New SqlDataAdapter(cmd)
        da.Fill(ds)
        Return ds

    End Function
    Protected Sub btnSearch_Click(sender As Object, e As EventArgs)
        Try
            ds = BindUsers()
            Dim dt As New DataTable()
            dt = ds.Tables(0)

            Dim dvData As DataView = dt.DefaultView
            Dim strFilter As String = " 1=1 "

            If Not String.IsNullOrEmpty(txtId.Text) Then
                strFilter = strFilter & " And User_ID = " & Convert.ToInt32(txtId.Text)
            End If
            If Not String.IsNullOrEmpty(txtUserName.Text) Then
                strFilter = (strFilter & " And UserName Like '%") + txtUserName.Text & "%'"
            End If
            dvData.RowFilter = strFilter
            GridFilter.DataSource = dvData
            GridFilter.DataBind()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Protected Sub GridFilter_Sorting(sender As Object, e As GridViewSortEventArgs)
        Try
            Dim ds As DataSet = BindUsers()
            Dim dt As New DataTable()
            dt = ds.Tables(0)
            Dim dvData As DataView = dt.DefaultView
            If ViewState("SortDirection") IsNot Nothing AndAlso ViewState("SortDirection").ToString() <> "" Then
                dvData.Sort = e.SortExpression & " " & ConvertSortDirection(ViewState("SortDirection").ToString())
            Else
                dvData.Sort = e.SortExpression & " " & ConvertSortDirection("ASC")
            End If

            GridFilter.DataSource = dvData
            GridFilter.DataBind()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Private Function ConvertSortDirection(sortDireciton As String) As String
        Try
            If sortDireciton IsNot Nothing Then
                Select Case sortDireciton
                    Case "ASC"
                        ViewState("SortDirection") = "DESC"
                        Exit Select
                    Case "DESC"
                        ViewState("SortDirection") = "ASC"
                        Exit Select
                End Select
            End If
            Return sortDireciton
        Catch err As Exception
            Throw err
        End Try

    End Function
End Class


The output of above code is as shown in the below figures.
1.No sorting and no filtering.
2. When you search with both User id and User Name.

No comments:

Post a Comment

© 2012-2014 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.