 In this article I will explain how to bind gridview
and implement filter/search functionality
using Stored procedure (Sql Sever NESTED IF-ELSE). |
Description:
- Bind gridview from SQL server database table using stored procedure
- Implement the search/filter
functionality in grid view to search the records based on some criteria.
In this example: filter the records by Employee Name, Salary and City.
- Use nested If Else If in stored procedure in Sql server to search record.
Implementation:
First of all create a Database in Sql server e.g. “Test”.
Now create Table "EmDetails” as
given below:
Column
Name
|
Data
Type
|
EmpId
|
Int(Primary Key. So set Is
Identity=True)
|
Name
|
varchar(100)
|
Salary
|
int
|
City
|
varchar(100)
|
Address
|
varchar(500)
|
First create a stored procedure to show data in GridView as:
create PROCEDURE [dbo].[BindEmp]
AS
BEGIN
SELECT * FROM EmDetails
END
Now create a Stored Procedure to get the employee records based on search criteria.
CREATE PROCEDURE
SearchEmpRecord
@SearchBy varchar(50),
@SearchVal varchar(50)
AS
BEGIN
IF @SearchBy =
'Name'
BEGIN
SELECT * FROM EmDetails WHERE
Name like @SearchVal +
'%'
END
ELSE IF
@SearchBy = 'City'
BEGIN
SELECT
* FROM
EmDetails WHERE City like
@SearchVal + '%'
END
ELSE IF
@SearchBy = 'Salary'
BEGIN
SELECT * FROM EmDetails WHERE
Salary = @SearchVal
END
ELSE
BEGIN
SELECT * FROM EmDetails
END
END
Now create connection String to connect application with database:
<connectionStrings>
<add name="Empcon" connectionString="Data Source=localhost;Initial
Catalog=Test;IntegratedSecurity=True"/>
</connectionStrings>
Source
Code:
Deesign page:
<div>
<fieldset
style="width:415px;">
<legend> <strong>Search
Records in Gridview</strong></legend>
<table>
<tr><td> </td><td>
</td><td>
</td></tr>
<tr><td>Search By:
<asp:DropDownList ID="ddlSearchBy" runat="server" AutoPostBack="True"
onselectedindexchanged="ddlSearchBy_SelectedIndexChanged">
<asp:ListItem Text="All"></asp:ListItem>
<asp:ListItem Text="Name"></asp:ListItem>
<asp:ListItem Text="Salary"></asp:ListItem>
<asp:ListItem Text="City"></asp:ListItem>
</asp:DropDownList>
</td><td>
<asp:TextBox ID="txtSearch" placeholder="Enter Value" runat="server"></asp:TextBox>
</td><td>
<asp:Button ID="btnSearch" runat="server" Text="Search"
onclick="btnSearch_Click" />
</td></tr>
<tr><td> </td><td>
</td><td>
</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="Name" DataField="Name" 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="#7B0606" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#7B0606" 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:
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 Searchgrid : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Empcon"].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("BindEmp",
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 == "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("SearchEmpRecord",
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
(Design & Code behind File)
Do following changes in design page in case of
vb.net.
<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:
Imports System.Data
Imports
System.Data.SqlClient
Imports
System.Configuration
Partial Public Class Searchgrid
Inherits System.Web.UI.Page
Dim con As
SqlConnection = New
SqlConnection(ConfigurationManager.ConnectionStrings("Empcon").ConnectionString)
Protected Sub
Page_Load(ByVal sender As
Object, ByVal e
As EventArgs)
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 SqlDataAdapter
= New SqlDataAdapter()
Dim dt As DataTable = New DataTable()
Try
adp = New
SqlDataAdapter("BindEmp",
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(this,
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(ByVal sender As Object, ByVal e As EventArgs)
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(ByVal sender As Object, ByVal e As EventArgs)
Dim dt As DataTable = New DataTable()
Dim cmd
As SqlCommand
= New SqlCommand()
Dim adp
As SqlDataAdapter
= New SqlDataAdapter()
Try
If
ddlSearchBy.SelectedItem.Text = "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(this,
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(ByVal searchBy As String, ByVal searchVal As String)
Dim dt As DataTable = New DataTable()
Dim cmd
As SqlCommand
= New SqlCommand()
Dim adp
As SqlDataAdapter
= New SqlDataAdapter()
Try
cmd = New
SqlCommand("SearchEmpRecord",
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(this,
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(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
grdEmp.PageIndex = e.NewPageIndex
BindEmpGrid()
End Sub
End Class
0 comments:
Post a Comment