How to create
Optional search in ADO.NET with Store Procedure in Asp.net with coalesce Function in Sql Server
--create
database sqltopquery
use
sqltopquery
--create table
MyFriendList in database sqltopquery
create table MyFriendList(SrNo int
identity(1,1) primary
key,Name nvarchar(50),Emailid
nvarchar(50) unique
,MobileNo nvarchar(50)unique,Address nvarchar(50),Course
nvarchar(50))
--insert into table
insert into
MyFriendList values('Mrityunjay
Kumar','ritumk.kumar902@gmail.com','9971684069','Bhagalpur','B C A')
--create stored procedure OpSearch…………………………………………
create proc OpSearch @SrNo int=0,@Name nvarchar(50)='',@MobileNo nvarchar(50)='',@Emailid nvarchar(50)='',@Address nvarchar(50)='',@Course nvarchar(50)=''
as
begin
SELECT * FROM
MyFriendList where 1=1
and (SrNo=@SrNo or coalesce(@SrNo,'')=0)
and (Name=@Name or coalesce(@Name,'')='')
and (MobileNo=@MobileNo or coalesce(@MobileNo,'')='')
and (Emailid=@Emailid or coalesce(@Emailid,'')='')
and (Address=@Address or coalesce(@Address,'')='')
and (Course=@Course or coalesce(@Course,'')='')
end
--exec stored procedure OpSearch
exec OpSearch 1,'Kush Tiwari',default,default,default,default
exec OpSearch default,default,default,default,default,'B. Tech'
exec OpSearch 2
Source code for Search.aspx ………………………………………………..
<%@ Page
Language="C#"
AutoEventWireup="true"
CodeFile="Search.aspx.cs"
Inherits="Search"
%>
<!DOCTYPE html
PUBLIC "-//W3C//DTD
XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.style1
{
width: 70%;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<table class="style1" >
<tr>
<td colspan="2">
<center> <b><font color="Greeen">How to create Optional search
in ADO.NET with Store Procedure with coalesce Function in Sql Server in Asp.net </font></b></center> </td>
</tr>
<tr>
<td>
Search By Name</td>
<td>
Search By EmailId</td>
</tr>
<tr>
<td>
<asp:TextBox ID="txtname" runat="server"></asp:TextBox>
</td>
<td>
<asp:TextBox ID="txtemailid" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
Search By Mobile No</td>
<td>
Search By
Course</td>
</tr>
<tr>
<td>
<asp:TextBox ID="txtmobileno" runat="server"></asp:TextBox>
</td>
<td>
<asp:DropDownList ID="ddlcourse" runat="server" Height="19px" Width="126px">
<asp:ListItem>--Select--</asp:ListItem>
<asp:ListItem>M C A</asp:ListItem>
<asp:ListItem>B C A</asp:ListItem>
<asp:ListItem>B Tech</asp:ListItem>
<asp:ListItem>M Tech</asp:ListItem>
<asp:ListItem>M A</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
Search
By Address</td>
<td>
Search By SrNo</td>
</tr>
<tr>
<td>
<asp:TextBox ID="txtaddress" runat="server"></asp:TextBox>
</td>
<td>
<asp:TextBox ID="txtsrno" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID="btnSearch" runat="server" onclick="btnSearch_Click"
Text="Search" />
</td>
</tr>
<tr>
<td colspan="2">
<center> <asp:Label ID="lblerrormessage" runat="server" Text="Label" Visible="False"></asp:Label>
</center>
</td>
</tr>
<tr>
<td colspan="2"><center>
<asp:GridView ID="GridView1" runat="server">
<EmptyDataTemplate>
There is
no data for this search
</EmptyDataTemplate>
</asp:GridView>
</center>
</td>
</tr>
</table>
<div>
</div>
</form>
</body>
</html>
Source code
for Search.aspx.cs ………………………………………………..
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Drawing;
public partial class Search :
System.Web.UI.Page
{
protected void
Page_Load(object sender, EventArgs e)
{
}
protected void
btnSearch_Click(object sender, EventArgs e)
{
try
{
// using ternary-operator ...
string q = (txtsrno.Text == "")
? "default" + "," : "'"
+ txtsrno.Text + "'" + ",";
q += (txtname.Text == "")
? "default" + "," : "'"
+ txtname.Text + "'" + ",";
q += (txtmobileno.Text == "") ? "default"
+ "," : "'"
+ txtmobileno.Text + "'" + ",";
q += (txtemailid.Text == "") ? "default"
+ "," : "'"
+ txtemailid.Text + "'" + ",";
q += (txtaddress.Text == "") ? "default"
+ "," : "'"
+ txtaddress.Text + "'" + ",";
q += (ddlcourse.SelectedIndex == 0) ? "default" : "'"
+ ddlcourse.SelectedItem.Text + "'";
string query = "exec
OpSearch " + q;
SqlConnection con = new SqlConnection(@"Data Source=KUSH-PC\KUSH;Initial
Catalog=SqlTopQuery;Integrated Security=True");
SqlCommand cmd = new SqlCommand(query, con);
SqlDataAdapter da = new
SqlDataAdapter(cmd);
DataSet ds = new
DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
catch (Exception
ex)
{
lblerrormessage.Visible = true;
lblerrormessage.ForeColor = Color.Red;
lblerrormessage.Text = ex.Message;
}
}
}
thank u sir........
ReplyDelete