EmpName varchar(100),
Age Int,
Salary Int,
City varchar(100),
Address varchar(500))
CREATEPROCEDURE
BindEmpGrid_Sp
AS
BEGIN
SELECT*FROM Emp_Tb
END
alterPROCEDURE
SearchEmpRecords1_Sp
@SearchBy varchar(50),
@SearchVal varchar(50)
AS
BEGIN
IF
@SearchBy ='Emp Name'
BEGIN
SELECT*FROM Emp_Tb WHERE EmpName like
@SearchVal +'%'
END
ELSEIF
@SearchBy ='City'
BEGIN
SELECT*FROM Emp_Tb WHERE City like
@SearchVal +'%'
END
ELSEIF
@SearchBy ='Salary'
BEGIN
SELECT*FROM Emp_Tb WHERE Salary =
@SearchVal
END
ELSE
BEGIN
SELECT*FROM Emp_Tb
END
END
----------------------------------------------------------------------------------------------------------
CREATEPROCEDURE
SearchEmpRecords1_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
EXECUTEsp_executesql@sql
<%@PageLanguage="C#"AutoEventWireup="true"CodeFile="Grid.aspx.cs"Inherits="Grid"%>
<!DOCTYPEhtmlPUBLIC"-//W3C//DTD
XHTML 1.0
Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<htmlxmlns="http://www.w3.org/1999/xhtml">
<headrunat="server">
<title></title>
</head>
<body>
<formid="form1"runat="server">
<div>
<fieldsetstyle="width:
415px;">
<legend>Bind and Search records
example in gridview</legend>
<table>
<tr>
<td>
Search By:
<asp:DropDownListID="ddlSearchBy"runat="server"AutoPostBack="True"OnSelectedIndexChanged="ddlSearchBy_SelectedIndexChanged">
<asp:ListItemText="All"></asp:ListItem>
<asp:ListItemText="Emp
Name"></asp:ListItem>
<asp:ListItemText="Salary"></asp:ListItem>
<asp:ListItemText="City"></asp:ListItem>
</asp:DropDownList>
</td>
<td>
<asp:TextBoxID="txtSearch"runat="server"></asp:TextBox>
</td>
<td>
<asp:ButtonID="btnSearch"runat="server"Text="Search"OnClick="btnSearch_Click"/>
</td>
</tr>
</table>
<asp:GridViewID="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%">
<AlternatingRowStyleBackColor="White"ForeColor="#284775"/>
<Columns>
<asp:BoundFieldHeaderText="Emp
Name"DataField="EmpName"ItemStyle-HorizontalAlign="Center"/>
<asp:BoundFieldHeaderText="Age"DataField="Age"ItemStyle-HorizontalAlign="Center"/>
<asp:BoundFieldHeaderText="Salary"DataField="Salary"ItemStyle-HorizontalAlign="Center"/>
<asp:BoundFieldHeaderText="City"DataField="City"ItemStyle-HorizontalAlign="Center"/>
<asp:BoundFieldHeaderText="Address"DataField="Address"ItemStyle-HorizontalAlign="Center"/>
</Columns>
<EditRowStyleBackColor="#999999"/>
<FooterStyleBackColor="#ffffff"Font-Bold="True"ForeColor="White"/>
<HeaderStyleBackColor="#5D7B9D"Font-Bold="True"ForeColor="White"/>
<PagerStyleBackColor="#284775"ForeColor="White"HorizontalAlign="Center"/>
<RowStyleCssClass="rowHover"BackColor="#F7F6F3"ForeColor="#333333"></RowStyle>
<SelectedRowStyleBackColor="#E2DED6"Font-Bold="True"ForeColor="#333333"/>
<%-- <sortedascendingcellstylebackcolor="#E9E7E2"
/>
<sortedascendingheaderstylebackcolor="#506C8C"
/>
<sorteddescendingcellstylebackcolor="#FFFDF8"
/>
<sorteddescendingheaderstylebackcolor="#6F8DAE"
/>--%>
<EmptyDataRowStyleWidth="550px"ForeColor="Red"Font-Bold="true"HorizontalAlign="Center"/>
</asp:GridView>
</fieldset>
</div>
</form>
</body>
</html>
using System;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Web;
usingSystem.Web.UI;
usingSystem.Web.UI.WebControls;
usingSystem.Data;
usingSystem.Data.SqlClient;
usingSystem.Configuration;
publicpartialclassGrid : System.Web.UI.Page
{
SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
protectedvoidPage_Load(object sender, EventArgs
e)
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
if (!Page.IsPostBack)
{
BindEmpGrid();
txtSearch.Enabled
= false;
}
}
privatevoidBindEmpGrid()
{
SqlDataAdapteradp = newSqlDataAdapter();
DataTabledt = newDataTable();
try
{
adp
= newSqlDataAdapter("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();
}
}
protectedvoidddlSearchBy_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();
}
}
protectedvoidbtnSearch_Click(object sender, EventArgs
e)
{
DataTabledt = newDataTable();
SqlCommandcmd = newSqlCommand();
SqlDataAdapteradp = newSqlDataAdapter();
try
{
if (ddlSearchBy.SelectedItem.Text == "Emp Name")
{
getEmpRecords(ddlSearchBy.SelectedItem.Text,
txtSearch.Text.Trim());
}
elseif (ddlSearchBy.SelectedItem.Text == "City")
{
getEmpRecords(ddlSearchBy.SelectedItem.Text,
txtSearch.Text.Trim());
}
elseif (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();
}
}
privatevoidgetEmpRecords(stringsearchBy, stringsearchVal)
{
DataTabledt = newDataTable();
SqlCommandcmd = newSqlCommand();
SqlDataAdapteradp = newSqlDataAdapter();
try
{
cmd
= newSqlCommand("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();
}
}
protectedvoidgrdEmp_PageIndexChanging(object sender, GridViewPageEventArgs
e)
{
grdEmp.PageIndex
= e.NewPageIndex;
BindEmpGrid();
}
}