Introduction: In this article I am going to explain with example How to bind Asp.Net gridview and implement filter/search functionality using Sql Sever CASE WHEN or NESTED IF-ELSE.
Implementation: Let’s create sample asp.net website to see it in action.
AS
BEGIN
SELECT * FROM Emp_Tb
END
 |
| Click on the image to view enlarged demo |
Description: You will learn the following through this article:
- How to Bind gridview from sql server database table using stored procedure
- How to implement the search/filter feature in grid view to search the records based on selected criteria. In this example i have implemented the functionality to filter the records by Emp Name, Salary and City.
- How to use nested If Else If in stored procedure in Sql server
- How to use CASE in stored procedure in Sql server
In previous articles i explained How to Bind,Save,Edit,Update,Cancel,Delete,Paging example in GridView and WCF Service to bind,insert,edit,update,delete from sql server database in asp.net and Delete multiple selected records/items based on CheckBox in GridView and Bind,upload,download,delete image files from the GridView and Display Serial/Row Number automatically in GridView and Highlight gridview row on mouse over using CSS in asp.net and Bind empty GridView with header and custom message
Implementation: Let’s create sample asp.net website to see it in action.
- First of all create a Database in Sql server e.g. “Emp_DB” and in this database create a table with the columns and data type as shown below and name it "Emp_Tb":
Column Name
|
Data Type
|
Emp_Id
|
Int(Primary Key. So set Is Identity=True)
|
EmpName
|
varchar(100)
|
Age
|
int
|
Salary
|
int
|
City
|
varchar(100)
|
Address
|
varchar(500)
|
- First create a stored procedure to bind data in GridView as:
AS
BEGIN
SELECT * FROM Emp_Tb
END
- Now create a Stored Procedure to get/fetch the employee records based on search criteria.
Note: In have mentioned two stored procedure for the same purpose. You can use any of the two. The first one which is simple, demonstrates the use of “IF.. ELSE IF..ELSE (Nested Else if)” to execute different queries based on conditions and the second one demonstrates the use of Case statement for the same purpose.
CREATE PROCEDURE SearchEmpRecords_Sp
@SearchBy varchar(50),
@SearchVal varchar(50)
AS
BEGIN
IF @SearchBy = 'Emp Name'
BEGIN
SELECT * FROM Emp_Tb WHERE EmpName like @SearchVal + '%'
END
ELSE IF @SearchBy = 'City'
BEGIN
SELECT * FROM Emp_Tb WHERE City like @SearchVal + '%'
END
ELSE IF @SearchBy = 'Salary'
BEGIN
SELECT * FROM Emp_Tb WHERE Salary = @SearchVal
END
ELSE
BEGIN
SELECT * FROM Emp_Tb
END
END
----------------------------------------------------------------------------------------------------------
CREATE PROCEDURE SearchEmpRecords_Sp
@SearchBy varchar(50),
@SearchVal varchar(50)
AS
BEGIN
DECLARE @sql NVARCHAR(1000)
SELECT @sql=CASE @SearchBy
WHEN 'Emp Name' THEN
'SELECT * FROM Emp_Tb WHERE EmpName LIKE '''+ @SearchVal +'%'''
WHEN 'City' THEN
'SELECT * FROM Emp_Tb WHERE City LIKE '''+ @SearchVal +'%'''
WHEN 'Salary' THEN
'SELECT * FROM Emp_Tb WHERE Salary = ' + @SearchVal + ''
ELSE
'(SELECT * FROM Emp_Tb)'
END
END
EXECUTE sp_executesql @sql
- In the web.config file create the connection string to connect the asp.net web application with the Sql server database as:
<connectionStrings>
<add name="con" connectionString="Data Source=Lalit;Initial Catalog=Emp_DB;Integrated Security=True"/>
</connectionStrings>
Note: Replace the Data source and Initial Catalog (i.e. Database Name) as per your application.
Source Code:
- In the design page(.aspx) design the page as:
<div>
<fieldset style="width:415px;">
<legend>Bind and Search records example in gridview</legend>
<table>
<tr><td>Search By:
<asp:DropDownList ID="ddlSearchBy" runat="server" AutoPostBack="True"
onselectedindexchanged="ddlSearchBy_SelectedIndexChanged">
<asp:ListItem Text="All"></asp:ListItem>
<asp:ListItem Text="Emp Name"></asp:ListItem>
<asp:ListItem Text="Salary"></asp:ListItem>
<asp:ListItem Text="City"></asp:ListItem>
</asp:DropDownList>
</td><td>
<asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
</td><td>
<asp:Button ID="btnSearch" runat="server" Text="Search"
onclick="btnSearch_Click" />
</td></tr>
</table>
<asp:GridView ID="grdEmp" runat="server" AllowSorting="True" EmptyDataText="No records found"
CssClass="rowHover" RowStyle-CssClass="rowHover" ShowHeader="true"
AutoGenerateColumns="False"
AllowPaging="True"
onpageindexchanging="grdEmp_PageIndexChanging"
PageSize="5"
CellPadding="4" ForeColor="#333333"
GridLines="None" Width="100%">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<Columns>
<asp:BoundField HeaderText="Emp Name" DataField="EmpName" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField HeaderText="Age" DataField="Age" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField HeaderText="Salary" DataField="Salary" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField HeaderText="City" DataField="City" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField HeaderText="Address" DataField="Address" ItemStyle-HorizontalAlign="Center" />
</Columns>
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#ffffff" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle CssClass="rowHover" BackColor="#F7F6F3" ForeColor="#333333"></RowStyle>
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
<EmptyDataRowStyle Width = "550px" ForeColor="Red" Font-Bold="true"
HorizontalAlign = "Center"/>
</asp:GridView>
</fieldset>
</div>
C#.Net Code to bind and implement searching in GridView
- In the code behind file (.aspx.cs) write the code as:
First include the following required namespaces
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
if (!Page.IsPostBack)
{
BindEmpGrid();
txtSearch.Enabled = false;
}
}
private void BindEmpGrid()
{
SqlDataAdapter adp = new SqlDataAdapter();
DataTable dt = new DataTable();
try
{
adp = new SqlDataAdapter("BindEmpGrid_Sp", con);
adp.Fill(dt);
if (dt.Rows.Count > 0)
{
grdEmp.DataSource = dt;
grdEmp.DataBind();
}
else
{
grdEmp.DataSource = null;
grdEmp.DataBind();
}
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Error occured : " + ex.Message.ToString() + "');", true);
}
finally
{
dt.Clear();
dt.Dispose();
adp.Dispose();
con.Close();
}
}
protected void ddlSearchBy_SelectedIndexChanged(object sender, EventArgs e)
{
if (ddlSearchBy.SelectedItem.Text == "All")
{
txtSearch.Text = string.Empty;
txtSearch.Enabled = false;
}
else
{
txtSearch.Enabled = true;
txtSearch.Text = string.Empty;
txtSearch.Focus();
}
}
protected void btnSearch_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand();
SqlDataAdapter adp=new SqlDataAdapter();
try
{
if (ddlSearchBy.SelectedItem.Text == "Emp Name")
{
getEmpRecords(ddlSearchBy.SelectedItem.Text, txtSearch.Text.Trim());
}
else if (ddlSearchBy.SelectedItem.Text == "City")
{
getEmpRecords(ddlSearchBy.SelectedItem.Text, txtSearch.Text.Trim());
}
else if (ddlSearchBy.SelectedItem.Text == "Salary")
{
getEmpRecords(ddlSearchBy.SelectedItem.Text, txtSearch.Text.Trim());
}
else
{
getEmpRecords(ddlSearchBy.SelectedItem.Text, txtSearch.Text.Trim());
}
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Error occured : " + ex.Message.ToString() + "');", true);
}
finally
{
dt.Clear();
dt.Dispose();
cmd.Dispose();
con.Close();
}
}
private void getEmpRecords(string searchBy, string searchVal)
{
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand();
SqlDataAdapter adp = new SqlDataAdapter();
try
{
cmd = new SqlCommand("SearchEmpRecords1_Sp", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SearchBy", searchBy);
cmd.Parameters.AddWithValue("@SearchVal", searchVal);
adp.SelectCommand = cmd;
adp.Fill(dt);
if (dt.Rows.Count > 0)
{
grdEmp.DataSource = dt;
grdEmp.DataBind();
}
else
{
grdEmp.DataSource = dt;
grdEmp.DataBind();
}
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Error occured : " + ex.Message.ToString() + "');", true);
}
finally
{
dt.Clear();
dt.Dispose();
cmd.Dispose();
con.Close();
}
}
protected void grdEmp_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
grdEmp.PageIndex = e.NewPageIndex;
BindEmpGrid();
}
VB.Net Code to bind and implement searching in GridView
- In the design page(.aspx) design the page as shown above in Source Code section but replace the line
<asp:DropDownList ID="ddlSearchBy" runat="server" AutoPostBack="True"
onselectedindexchanged="ddlSearchBy_SelectedIndexChanged">
with
<asp:DropDownList ID="ddlSearchBy" runat="server" AutoPostBack="True" >
Similarly replace the line
<asp:Button ID="btnSearch" runat="server" Text="Search"
onclick="btnSearch_Click" />
with
<asp:Button ID="btnSearch" runat="server" Text="Search" />
and also remove the onpageindexchanging="grdEmp_PageIndexChanging" from the Grid View source .
- In the code behind file (.aspx.vb) write the code as:
First import the following namespaces
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)
Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
If con.State = ConnectionState.Closed Then
con.Open()
End If
If Not Page.IsPostBack Then
BindEmpGrid()
txtSearch.Enabled = False
End If
End Sub
Private Sub BindEmpGrid()
Dim adp As New SqlDataAdapter()
Dim dt As New DataTable()
Try
adp = New SqlDataAdapter("BindEmpGrid_Sp", con)
adp.Fill(dt)
If dt.Rows.Count > 0 Then
grdEmp.DataSource = dt
grdEmp.DataBind()
Else
grdEmp.DataSource = Nothing
grdEmp.DataBind()
End If
Catch ex As Exception
ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Error occured : " & ex.Message.ToString() & "');", True)
Finally
dt.Clear()
dt.Dispose()
adp.Dispose()
con.Close()
End Try
End Sub
Protected Sub ddlSearchBy_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles ddlSearchBy.SelectedIndexChanged
If ddlSearchBy.SelectedItem.Text = "All" Then
txtSearch.Text = String.Empty
txtSearch.Enabled = False
Else
txtSearch.Enabled = True
txtSearch.Text = String.Empty
txtSearch.Focus()
End If
End Sub
Protected Sub btnSearch_Click(sender As Object, e As System.EventArgs) Handles btnSearch.Click
Dim dt As New DataTable()
Dim cmd As New SqlCommand()
Dim adp As New SqlDataAdapter()
Try
If ddlSearchBy.SelectedItem.Text = "Emp Name" Then
getEmpRecords(ddlSearchBy.SelectedItem.Text, txtSearch.Text.Trim())
ElseIf ddlSearchBy.SelectedItem.Text = "City" Then
getEmpRecords(ddlSearchBy.SelectedItem.Text, txtSearch.Text.Trim())
ElseIf ddlSearchBy.SelectedItem.Text = "Salary" Then
getEmpRecords(ddlSearchBy.SelectedItem.Text, txtSearch.Text.Trim())
Else
getEmpRecords(ddlSearchBy.SelectedItem.Text, txtSearch.Text.Trim())
End If
Catch ex As Exception
ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Error occured : " & ex.Message.ToString() & "');", True)
Finally
dt.Clear()
dt.Dispose()
cmd.Dispose()
con.Close()
End Try
End Sub
Private Sub getEmpRecords(searchBy As String, searchVal As String)
Dim dt As New DataTable()
Dim cmd As New SqlCommand()
Dim adp As New SqlDataAdapter()
Try
cmd = New SqlCommand("SearchEmpRecords1_Sp", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@SearchBy", searchBy)
cmd.Parameters.AddWithValue("@SearchVal", searchVal)
adp.SelectCommand = cmd
adp.Fill(dt)
If dt.Rows.Count > 0 Then
grdEmp.DataSource = dt
grdEmp.DataBind()
Else
grdEmp.DataSource = dt
grdEmp.DataBind()
End If
Catch ex As Exception
ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Error occured : " & ex.Message.ToString() & "');", True)
Finally
dt.Clear()
dt.Dispose()
cmd.Dispose()
con.Close()
End Try
End Sub
Protected Sub grdEmp_PageIndexChanging(sender As Object, e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles grdEmp.PageIndexChanging
grdEmp.PageIndex = e.NewPageIndex
BindEmpGrid()
End Sub
Now over to you:
Now over to you:
"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
57 comments
Click here for commentsGreat work
ReplyThanks for the appreciation..stay connected for more technical updates like this..
Replythanks for this post..great!!!great!!!great!!! Work!!! =)
Replyi am glad you liked this article..keep reading for more useful updates..
ReplyThanks.... From thailand ^^
Replyyour welcome..keep reading :)
ReplyRaghuvanshi sir,
ReplyI glad to take guidance from you and this place for my final project.
right now i'm working on my final project and for which i'm designing a bloodbank website and i want to impliment SMS facility in which as soon as one donor's blood transmits to any receiver Donor will get an message that your blood had fullfilled this person's life..
but this idea is just a idea i'm not knowing anything that how to impliment this idea so please give me your suggestion and please teach me about this concept.
it is very useful for me ........Great work .....
ReplyThanks Reddappa Chinthamani..stay connected and keep reading :)
ReplyThis is very useful,, thank you very much..
Replyi have a problem about edit the grid view.. i cannot edit this gridview after the taking data into the grid using this codes .. can you please give me a solution to edit delete update the gridview...
Hi, You can read my article:
ReplyBind,Save,Edit,Update,Cancel,Delete,Paging example in GridView in asp.net C#
http://www.webcodeexpert.com/2013/07/bindsaveeditupdatecanceldeletepaging.html
is this missing sp ?
ReplyBindEmpGrid_Sp
Hi, Thanks for notifying me the missing stored procedure..i have updated the article with that Stored procedure..stay connected and keep reading :)
ReplyHow can I make the Header name links and the columns sortable
ReplyI'm populating my GridView from 2 different tables. I'm using the Select Case When Stored Procedure (SearchEmpRecords_Sp). Could you show me how to Join 2 Tables in the SELECT line.
ReplyHello Michael Nacci..send me the tables script and specify what columns are required to be displayed in gridview..i will join them as per your requirement..
ReplyIs there a way to add a sorting to this Data Table. I would like to click on the header of the column to sort records?
ReplyIs there a way to add sorting to the column headers?
ReplyHi Michael..i am working on implementing sorting in GridView and publish that by tomorrow..so keep reading..:)
ReplyHi, Michael Nacci..read the article as per your requirement:
ReplySorting in Asp.Net GridView by column header in ascending descending order
http://www.webcodeexpert.com/2013/11/sorting-in-aspnet-gridview-by-column.html
Excellent man.....Bon courage.... Merci
Replythanks...keep reading..:)
Replyuseful demo.. thank you...
ReplyYour welcome pravin..it is always nice to hear that mt articles helped anyone...keep reading for more useful articles like this...
ReplyThanks a lot. I'hv done my work seeing this articles very easily whatever i cant before.
ReplyYou welcome..keep reading and stay tuned for more updates..:)
ReplySir I got a trouble like this
ReplyCompilation Error:
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: CS1061: 'ASP.studentsearch_aspx' does not contain a definition for 'grdEmp_SelectedIndexChanged' and no extension method 'grdEmp_SelectedIndexChanged' accepting a first argument of type 'ASP.studentsearch_aspx' could be found (are you missing a using directive or an assembly reference?)
Line 31: <asp:GridView ID="grdEmp" runat="server" AllowSorting="True" EmptyDataText="No records found"
remove grdEmp_SelectedIndexChanged from the gridview source code..it will solve your problem..and let me know the results..
Replyhi sir,
Replycan we implement it with the date also?
hello sir,
Replyhow to fetch the data from gridview and display those values in textbox for edit and delete,Am using vb.net web application,sql.I need coding for that.
hi jaya, i will create an article as per your requirement and publish very soon..so stay connected and keep reading..:)
ReplyI got a trouble like this Both DataSource and DataSourceID are defined on 'grdEmpop'. Remove one definition.
ReplyIn the .aspx page remove the DataSourceId from the gridview source..it will solve your problem..
ReplyNice work. Thank you for sharing your valuable time and knowledge.
ReplyHello Gaurav..thanks for appreciating my work..it is always nice to hear that someone found my articles helpful..stay connected and keep reading for more useful updates..:)
ReplyHow are you supposed edit the record that you have searched for when the edit event needs to rebind again?
ReplyHere is error:
Failed to load viewstate. The control tree into which viewstate is being loaded must match the control tree that was used to save viewstate during the previous request. For example, when adding controls dynamically, the controls added during a post-back must match the type and position of the controls added during the initial request.
thanks a lot
ReplyHi.Thanks a lot.
ReplyHi.i am glad you liked my article..stay connected and keep reading...:)
ReplyYour welcome java..:)
Replyhow to bind the data in grid view but the thing is tat i want to get the output in a grid view by clicking on to the radio button...pls help me out on tis
ReplyHello Sir, Thank You Very Much,This article is very help full for my current project.
ReplyYour welcome Ranjan..stay connected and keep reading for more useful updates like this..
Replynice job
ReplyThanks Mani Teja for your feedback.
ReplyHello sir! your code really helped... but i also have another problem... I wanted to add a delete button in the grid view... can you please help me? thanks :)
ReplyThanks for your feedback..Read the following article as per your requirement:
ReplyBind,Save,Edit,Update,Cancel,Delete,Paging example in GridView in asp.net
http://www.webcodeexpert.com/2013/07/bindsaveeditupdatecanceldeletepaging.html
Hi sir, nice job here. really appreciate it. Please am trying to include a edit button in this very implementation. I have enabled the edit button but when clicked, it shows text boxes with an update and cancel button. when i click the update button, it doesnt update obviously because i have not handled the event. I cant think of possible way of doing that because i dont know the name of the the textboxes generated when the edit button was clicked. remember it does not contain item template. Please help
ReplyThanks for your valuable feedback..Read the following article as per your requirement:
ReplyBind,Save,Edit,Update,Cancel,Delete,Paging example in GridView in asp.net
http://www.webcodeexpert.com/2013/07/bindsaveeditupdatecanceldeletepaging.html
Thankyou.......Its very useful.....
ReplyThankyou....Its very useful...
ReplyThanks for you feedback..I am glad you liked my article..stay connected and keep reading...
Replynice work for learners
ReplyI am glad you found this article helpful..stay connected and keep reading for more updates.
Replyto goood great to be got this coding thanks sir
ReplyIf you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..