Tuesday 29 October 2013

How to create Optional search in ADO.NET with Store Procedure in Asp.net

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&nbsp; 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&nbsp; 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>
&nbsp;</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&nbsp; 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;
}

}
}


1 comments: