How to create
Dynamic Query for search in ADO.NET without any If else Condition
--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')
Code For Insert Data in Table………………………………………………..
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.Drawing;
public partial class _Default :
System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs
e)
{
}
protected void
btnSave_Click(object sender, EventArgs e)
{
try
{
SqlConnection con = new SqlConnection(@"Data Source=KUSH-PC\KUSH;Initial
Catalog=sqltopquery;Integrated Security=True");
SqlCommand cmd = new SqlCommand("insert
into MyFriendList values('" + txtname.Text + "','" + txtemailid.Text + "','" + txtmobileno.Text + "','" + txtaddress.Text + "','" + ddlcourse.SelectedItem.Text + "')", con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
lblmessage.Visible
= true;
lblmessage.ForeColor
= Color.Green;
lblmessage.Text
= "Save Successfully";
}
catch (Exception
ex)
{
lblmessage.Visible
= true;
lblmessage.ForeColor
= Color.Green;
lblmessage.Text
= ex.Message;
}
}
}
--Code for Search from table with any condition(using
dynamic query) without any if else condition
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>
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>
</td>
</tr>
<tr>
<td>
<asp:TextBox ID="txtaddress"
runat="server"></asp:TextBox>
</td>
<td>
<asp:Button ID="btnSearch"
runat="server"
onclick="btnSearch_Click"
Text="Search" />
</td>
</tr>
<tr>
<td colspan="2"><center>
<asp:GridView ID="GridView1"
runat="server">
</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;
public partial class Search :
System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs
e)
{
}
protected void
btnSearch_Click(object sender, EventArgs e)
{
// using ternary-operator……………………………………..
SqlConnection con = new SqlConnection(@"Data Source=KUSH-PC\KUSH;Initial Catalog=sqltopquery;Integrated
Security=True");
string qry = "Select
* from MyFriendList Where 1=1";
qry +=
txtname.Text.Trim() != "" ? (" And Name = '" + txtname.Text.Trim()) +
"'" : "";
qry +=
txtaddress.Text.Trim() != "" ? (" And Address = '" +
txtaddress.Text.Trim()) + "'" : "";
qry +=
txtmobileno.Text.Trim() != "" ? (" And MobileNo = '" +
txtmobileno.Text.Trim()) + "'" : "";
qry +=
txtemailid.Text.Trim() != "" ? (" And Emailid = '" +
txtemailid.Text.Trim()) + "'" : "";
qry +=
ddlcourse.SelectedIndex != 0 ? (" And Course =
'" + ddlcourse.SelectedItem.Text.Trim()) + "'"
: "";
SqlDataAdapter da = new SqlDataAdapter(qry,
con); ;
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource
= ds;
GridView1.DataBind();
}
}
--What is
ternary operator
well done......................kush
ReplyDeleteRealyy u r doing very fantastic job.....keep doing..god bless u....
ReplyDelete