Search with Multiple Filters Asp.Net,C#,SQL Server Web Application

Bangalore: In any web application development process you may face this situation as per your requirement, a web form which have four or five filter fields and a button to search. I am just writing this article for beginners those who starts developing in Asp.Net, C# feel free to comment in our blog if you have any doubt regarding this article.So first of all before writing any program think what you required to achieve your result.
Here we need Visual Studio 2010 and MS SQL Server 2008 as Development Tools.

First create an Empty Web Application in C#
And then we create a Asp.Net web form which is having 5 Fields and 1 Button to search  and one to Clear one grid view to list all the Results.
and In code behind aspx.cs page you have to write Logic for your Button click actions
and In web.config file we will declare the connection string details.

In web.config Inside <configuration> tag after <system.web> tag add the connection string syntax.
<connectionStrings>
    <add name="constr1" connectionString="Data Source=Tonz-PC; Initial Catalog=Search; Integrated Security=True" providerName="System.Data.SqlClient"/>
  </connectionStrings>

So Now we can check the code to create Aspx from.
Search.Aspx from


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="search.aspx.cs" Inherits="search.search" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<script language="javascript" type="text/javascript">

    function Clear() {
        document.getElementById("txtFirstName").value = "";
        document.getElementById("txtLastName").value = "";
        document.getElementById("txtUserName").value = "";
        document.getElementById("txtCountry").value = "";
        document.getElementById("txtState").value = "";
        return true;
    }
</script>
    <title>Search Form</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table width="100%" >
   
    <legend>Search Filter</legend>
    <tr>
    <td nowrap="nowrap">First Nmae</td>
    <td>
    <asp:TextBox runat="server" ID="txtFirstName"></asp:TextBox>
    </td>
    <td nowrap="nowrap">Last Name</td>
    <td> <asp:TextBox runat="server" ID="txtLastName"></asp:TextBox></td>
    <td nowrap="nowrap"> UserName</td>
    <td><asp:TextBox runat="server" ID="txtUserName"></asp:TextBox></td>

    </tr>

    <tr>
    <td>Country
    </td>
    <td><asp:TextBox runat="server" ID="txtCountry"></asp:TextBox>
    </td>

    <td nowrap="nowrap">State </td>
    <td> <asp:TextBox runat="server" id="txtState"></asp:TextBox></td>
    </tr>

    <tr>
    <td colspan="5" align="right">
    <asp:Button ID="btnSearch" runat="server" Text="Search" onclick="btnSearch_Click" />
    </td>
    <td>
    <asp:Button runat="server" ID="btnClear" Text="Clear" OnClientClick="javascript:Clear()"  CausesValidation="false"/>
    </td>
    </tr>

    <asp:Panel runat="server" ID="pnlgrid">
    <tr>
    <td colspan="6">
    <fieldset>
    <legend>Search Results</legend>
    <asp:GridView runat="server" ID="gdvSearch" AllowPaging="true"
    AutoGenerateColumns="false" PageSize="6" BackColor="White"  Width="100%"
    BorderColor="Black" BorderStyle="Ridge" BorderWidth="2px" CellPadding="8" CellSpacing="1"
     ForeColor="#330199" GridLines="None" HeaderStyle-BorderColor="Black" RowStyle-BackColor="White">

     <Columns>
     <asp:BoundField DataField="firstname" HeaderStyle-HorizontalAlign="Left" ItemStyle-Width="100px" ReadOnly="true" HeaderText="First Name" >
     </asp:BoundField>

     <asp:BoundField DataField="lastname" HeaderStyle-HorizontalAlign="Left" ItemStyle-Width="100px" ReadOnly="true" HeaderText="Last Name">
     </asp:BoundField>
     <asp:BoundField DataField="username" HeaderStyle-HorizontalAlign="Left" ItemStyle-Width="100px" ReadOnly="true" HeaderText="User Name" ></asp:BoundField>

     <asp:BoundField DataField="Country" HeaderStyle-HorizontalAlign="Left" ItemStyle-Width="100px" ReadOnly="true" HeaderText="Country" ></asp:BoundField>
        <asp:BoundField DataField="State" HeaderStyle-HorizontalAlign="Left" ItemStyle-Width="100px" ReadOnly="true"  HeaderText="Country" ></asp:BoundField>
     </Columns>

    </asp:GridView>
    
    </fieldset>
    </td>
    </tr>
    </asp:Panel>
    </table>

   
    </div>
    </form>
</body>
</html>



code ends here
In code behind Search.Aspx.cs have all c sharp codes and we will Create seachbus.cs class there we will write the database logic and all.
If string is Empty we are passing "%" so it will take care of all null entries

Search.Aspx.cs code



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;


namespace search
{
    public partial class search : System.Web.UI.Page
    {


        #region PageLoad
        protected void Page_Load(object sender, EventArgs e)
        {
            pnlgrid.Visible = false;  /* make grid Invisible first  */
        }
       #endregion
        #region Button Click
        protected void btnSearch_Click(object sender, EventArgs e)
        {

           DataTable dtsearch= getFilteredData(); 
           gdvSearch.DataSource = dtsearch;
           gdvSearch.DataBind();
           gdvSearch.Visible = true;
           pnlgrid.Visible = true;
           ViewState["file"] = dtsearch;
          
            
        }
        #endregion

        #region passing value to sp
        public DataTable getFilteredData()
        {
            searchbus objSearch = new searchbus();
 /* Creating object of  searchbus  class and passing value to there*/
            if (txtFirstName.Text == "")
            {
                objSearch.Fname = "%"; /* It will take care of Null Entries */
            }
            else
            {
                objSearch.Fname = txtFirstName.Text;
            }


            if (txtLastName.Text == "")
            {
                objSearch.Lname = "%";
            }
            else
            {
                objSearch.Lname = txtLastName.Text;
            }

            if (txtUserName.Text == "")
            {
                objSearch.Uname = "%";
            }
            else
            {
                objSearch.Uname = txtUserName.Text;
            }


            if (txtCountry.Text == "")
            {
                objSearch.Cntry = "%";
            }
            else
            {
                objSearch.Cntry = txtCountry.Text;
            }


            if (txtState.Text == "")
            {
                objSearch.Stat = "%";
            }
            else
            {
                objSearch.Stat = txtState.Text;
            }
            DataTable dt = objSearch.GetUsers(); /* passing all values into your sp  */
            return dt;
        }
        #endregion
       
    }
}



searchbus.cs class



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace search
{

    public class searchbus
    {

        private string FirstName;
        private string LastName;
        private string UserName;
        private string Country;
        private string State;

/*assigning values using get and set */
        public string Fname
        {
            get { return FirstName; }
            set { FirstName = value; }
        }

        public string Lname
        {
            get { return LastName; }
            set { LastName = value; }
        }

        public string Uname
        {
            get { return UserName; }
            set { UserName = value; }
        }

        public string Cntry
        {
            get { return Country; }
            set { Country = value; }
        }
        public string Stat
        {
            get { return State; }
            set { State = value; }
        }

        #region method search
        public DataTable GetUsers()
        {
            DataTable lrs;

            SqlCommand cmd = new SqlCommand();
            SqlParameter spm = null;

            cmd.CommandText = "USP_SearchUsers";
            cmd.CommandType = CommandType.StoredProcedure;

          
            spm = new SqlParameter("@FirstName", SqlDbType.VarChar);
            spm.Value = FirstName;
            cmd.Parameters.Add(spm);

            spm = new SqlParameter("@Lastname", SqlDbType.VarChar);
            spm.Value = LastName;
            cmd.Parameters.Add(spm);

            spm = new SqlParameter("@UserName", SqlDbType.VarChar);
            spm.Value = UserName;
            cmd.Parameters.Add(spm);

            spm = new SqlParameter("@Country", SqlDbType.VarChar);
            spm.Value = Country;
            cmd.Parameters.Add(spm);

            spm = new SqlParameter("@State", SqlDbType.VarChar);
            spm.Value = State;
            cmd.Parameters.Add(spm);

            lrs = ExecuteQuery(cmd);

            return lrs;
        }

        #endregion

        #region get connection string
        public string getConstring()
        {
            string constr = ConfigurationManager.ConnectionStrings["constr1"].ConnectionString;
            return constr;
        }
        #endregion

        #region ExecutingQuery
        public DataTable ExecuteQuery(SqlCommand SQLCommand)
        {
            string str = getConstring();
            SqlConnection SQLConn = new SqlConnection(str);
            if (SQLConn.State != ConnectionState.Open)
                SQLConn.Open();
            DataTable DataTable = null;
            DataTable = new DataTable();

            SQLCommand.Connection = SQLConn;
            SqlDataAdapter SqlData= new SqlDataAdapter(SQLCommand);

            SqlData.Fill(DataTable);

            SQLConn.Close();
            return DataTable;

        }
        #endregion

    }
}



Search Stored Procedure from MSSQL Database



USE [Search]
GO
/****** Object:  StoredProcedure [dbo].[USP_SearchUsers]    Script Date: 6/5/2013 2:08:25 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Tony Tom
-- Create date: 2-june-2013

-- =============================================
Create PROCEDURE [dbo].[USP_SearchUsers]      --
@FirstName varchar(50)=null,
@Username varchar(50)=null,
@Lastname varchar(100)=null,
@country varchar(100)=null,
@state varchar(100)=null

AS
Begin
 SELECT
      [firstname]
      ,[lastname]
      ,[username]
      ,[country]
      ,[state]
  FROM [userdata]
 where FirstName like @FirstName+'%' and  username like @Username+'%' and LastName like @Lastname+'%' and country like @country+'%' and [state] like @state+'%'
End



So Finally while running your project you will get the result like this.


/* androidsharp */

0 comments: