How to create
Optional search in ADO.NET with Store Procedure in Asp.net
--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('Kush Tiwari','kushktiwari@gmail.com','9451119029','Ghazipur','M C A')
--create stored
procedure optionalSearch in sqltopquery
for optional
Search
create proc optionalSearch @SrNo int=0,@Name nvarchar(50)='',@MobileNo nvarchar(50)='',@Emailid nvarchar(50)='',@Address nvarchar(50)='',@Course nvarchar(50)=''
as
begin
declare @query nvarchar(200)=''
set @query='SELECT * FROM MyFriendList
where 1=1 '
if @SrNo!=0
set @query+=' and srno='+convert(varchar(10), @SrNo)
if @Name!=''
set @query+=' and Name='''+@Name+''''
if @MobileNo!=''
set @query+=' and MobileNo='''+@MobileNo+''''
if @Emailid!=''
set @query+=' and Emailid='''+@Emailid+''''
if @Address!=''
set @query+=' and Address='''+@Address+''''
if @Course!=''
set @query+=' and Course='''+@Course+''''
exec (@query)
end
--exec stored
procedure optionalSearch
exec
optionalSearch 1,'Kush Tiwari',default,default,default,default
exec
optionalSearch default,default,default,default,default,'M C A'
exec
optionalSearch 1
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 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
optionalSearch " + 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;
}
}
}
nice concept.....................
ReplyDelete