How
to bind and implement search gridview records in asp.net | Use of If Else If in
Sql server
- 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="dataConfig" connectionString="Data Source=SKYWORLD-PC\SQLEXPRESS;Initial
Catalog=registerdata;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 align="Center">
<br />
<br /><br />
<fieldset style="width:415px;">
<legend>Bind and Search records example in gridview</legend>
<asp:DropDownList ID="ddlsearchby" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlsearchby_SelectedIndexChanged">
<asp:ListItem>All</asp:ListItem>
<asp:ListItem>EmpName</asp:ListItem>
<asp:ListItem>Salary</asp:ListItem>
<asp:ListItem>City</asp:ListItem>
</asp:DropDownList>
<asp:TextBox ID="txtuser" runat="server" ></asp:TextBox>
<asp:Button ID="btnsearch" runat="server" Text="Search" OnClick="btnsearch_Click" />
<br />
<br /><br />
<asp:GridView ID="grddatalist" runat="server" AutoGenerateColumns="False" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2">
<Columns>
<asp:TemplateField HeaderText="EmpId">
<ItemTemplate>
<asp:Label ID="lblempid" runat="server" Text='<%#Bind("Empid") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:Label ID="lblempname" runat="server" Text='<%#Bind("EmpName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Age">
<ItemTemplate>
<asp:Label ID="lblempage" runat="server" Text='<%#Bind("Empage") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Salary">
<ItemTemplate>
<asp:Label ID="lblempsalary" runat="server" Text='<%#Bind("Salary") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City">
<ItemTemplate>
<asp:Label ID="lblempcity" runat="server" Text='<%#Bind("city") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Address">
<ItemTemplate>
<asp:Label ID="lblempaddress" runat="server" Text='<%#Bind("addressdata") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#FFF1D4" />
<SortedAscendingHeaderStyle BackColor="#B95C30" />
<SortedDescendingCellStyle BackColor="#F1E5CE" />
<SortedDescendingHeaderStyle BackColor="#93451F" />
</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;
string configdata = ConfigurationManager.ConnectionStrings["dataConfig"].ToString();
SqlConnection sqlcon;
SqlCommand sqlcmd;
SqlDataAdapter sqldap;
DataSet ds;
DataTable dt;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack == true)
{
BindEmpGrid();
txtuser.Enabled = false;
}
}
private void BindEmpGrid()
{
sqlcon = new SqlConnection(configdata);
sqldap = new SqlDataAdapter();
dt = new DataTable();
try
{
sqldap = new SqlDataAdapter("BindEmpGrid_Sp",
sqlcon);
sqldap.Fill(dt);
if (dt.Rows.Count > 0)
{
grddatalist.DataSource =
dt;
grddatalist.DataBind();
}
else
{
grddatalist.DataSource = null;
grddatalist.DataBind();
}
}
catch (Exception ex)
{
}
finally
{
dt.Clear();
dt.Dispose();
sqlcon.Close();
}
}
protected void ddlsearchby_SelectedIndexChanged(object sender, EventArgs e)
{
if (ddlsearchby.SelectedItem.Text == "All")
{
txtuser.Text = string.Empty;
txtuser.Enabled = false;
}
else
{
txtuser.Enabled = true;
txtuser.Text = string.Empty;
txtuser.Focus();
}
}
protected void btnsearch_Click(object sender, EventArgs e)
{
DataTable dt2 = new DataTable();
sqlcmd = new SqlCommand();
sqldap = new SqlDataAdapter();
try
{
if (ddlsearchby.SelectedItem.Text == "EmpName")
{
getEmpRecords(ddlsearchby.SelectedItem.Text, txtuser.Text.Trim());
}
else if (ddlsearchby.SelectedItem.Text == "Salary")
{
getEmpRecords(ddlsearchby.SelectedItem.Text, txtuser.Text.Trim());
}
else if (ddlsearchby.SelectedItem.Text == "city")
{
getEmpRecords(ddlsearchby.SelectedItem.Text,
txtuser.Text.Trim());
}
}
catch (Exception ex)
{
}
finally
{
}
}
private void getEmpRecords(string searchBy, string searchVal)
{
sqlcon = new SqlConnection(configdata);
DataTable dt = new DataTable();
sqlcmd = new SqlCommand();
sqldap = new SqlDataAdapter();
try
{
sqlcmd = new SqlCommand("SearchEmpRecords_sp", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@SearchBy",
searchBy);
sqlcmd.Parameters.AddWithValue("@SearchVal",
searchVal);
sqldap = new SqlDataAdapter(sqlcmd);
sqldap.Fill(dt);
if (dt.Rows.Count > 0)
{
grddatalist.DataSource =
dt;
grddatalist.DataBind();
}
else
{
grddatalist.DataSource =
dt;
grddatalist.DataBind();
}
}
catch (Exception ex)
{
}
finally
{
dt.Clear();
dt.Dispose();
sqlcon.Close();
}
}
}
}
