JUGGERNAUT: 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">

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>

<div>

<legend>Search Filter</legend>

<tr>

<td nowrap="nowrap">First Nmae</td>

<td>

<asp:TextBox runat="server" ID="txtFirstName"></asp:TextBox>

</td>

<td nowrap="nowrap"> UserName</td>

</tr>

<tr>

<td>Country

</td>

</td>

<td nowrap="nowrap">State </td>

</tr>

<tr>

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

<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">

<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]

/****** Object: StoredProcedure [dbo].[USP_SearchUsers] Script Date: 6/5/2013 2:08:25 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

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

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.