This is default featured slide 4 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured slide 1 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured slide 2 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured slide 4 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

Me And My Respected Teacher Mr Kamal Sheel Mishra

Mr. K.S. Mishra is HOD of Computer Science from SMS Varanasi where I have completed my MCA

Me And My Respected Teacher Mr Udayan Maiti

Mr. Udayan Maiti is a senior .Net Expert and has guided many professionals of multi national Companies(MNC)

Me And My Best Friend Mr Ravinder Goel

Mr. Ravinder Goel is a senior Software Engineer and now he is working Wipro Technology

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;
}

}
}