Asp.Net and C sharp: CALLING A STORED PROCEDURE AND POPULATING DATA

--SCENARIO

--NOW CREATE A STORED PROCEDURE

--IF I EXECUTE THE STORED PROC WITHOUT PARAMETER THEN ERROR

----------some changes
--ADD NULL TO ALL PARAMETERS AND EXECUTE

--NOW JUST MODIFY AS BELOW
--NOW WHEN YOU EXECUTE THE SP ..YOU WILL GET THE RESULT

--exec SearchEmployee @gender='Male'
--this will fetch the male employees

--oops its not fetching....wait my table is having gender male as 'm'

update employee set Gender='Female' where Name in('ruchi','prachi','preeti')
update employee set Gender='Male' where Name in('anurag','abhi','siba','mukua')

--exec SearchEmployee @gender='female'
--this will fetch the male employees

-----------------------------------------------aspx page-------------------------------------

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication2._Default" %>

<!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>
</head>
<body>
<form id="form1" runat="server">
<div>
<table style="font-family: Arial; border: 1px solid black">
<tr>
<td colspan="4" style="border-bottom: 1px solid black">
<b>Search Employees </b>
</td>
</tr>
<tr>
<td>
<b>Name </b>
</td>
<td>
<asp:TextBox ID="txtname" runat="server"></asp:TextBox>
</td>
<td>
<b>Email </b>
</td>
<td>
<asp:TextBox ID="txtemail" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<b>Age </b>
</td>
<td>
<asp:TextBox ID="txtage" runat="server"></asp:TextBox>
</td>
<td>
<b>Gender </b>
</td>
<td>
<asp:DropDownList ID="ddlgender" runat="server">
<asp:ListItem Text="Any Gender" Value="-1"></asp:ListItem>
<asp:ListItem Text="Male" Value="Male"></asp:ListItem>
<asp:ListItem Text="Female" Value="Female"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td colspan="4">
<asp:Button ID="BtnSearch" runat="server" Text="Search"
onclick="BtnSearch_Click" />
</td>
</tr>
<tr>
<td colspan="4">
<asp:GridView ID="gvemploye" runat="server">
</asp:GridView>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>

-----------------------------------------------.cs page--------------------------------------------

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

namespace WebApplication2

{

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

getdata();

}

protected void BtnSearch_Click(object sender, EventArgs e)

{

getdata();

}

private void getdata()

{

string strcon = ConfigurationManager.ConnectionStrings["ConnectionStringdb"].ConnectionString;

using(SqlConnection con = new SqlConnection(strcon))

{

SqlCommand cmd = new SqlCommand("SPSEARCHEMPLOYEES",con);

cmd.CommandType = CommandType.StoredProcedure;

//ATTACH THE PARAMETERS

if (txtname.Text != string.Empty)

{

SqlParameter nameparameter = new SqlParameter("@Name", txtname.Text);

cmd.Parameters.Add(nameparameter);

}

if (txtemail.Text != string.Empty)

{

SqlParameter emailparameter = new SqlParameter("@Email", txtemail.Text);

cmd.Parameters.Add(emailparameter);

}

if (txtage.Text != string.Empty)

{

SqlParameter ageparameter = new SqlParameter("@Age", txtage.Text);

cmd.Parameters.Add(ageparameter);

}

if (ddlgender.SelectedValue != "-1")

{

SqlParameter genderparameter = new SqlParameter("@Gender", ddlgender.SelectedValue);

cmd.Parameters.Add(genderparameter);

}

con.Open();

gvemploye.DataSource = cmd.ExecuteReader();

gvemploye.DataBind();

}

--------------------------------------------------------------------------------------------------------

--if any gender is selected

--optimized code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace WebApplication2
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

if (!IsPostBack)
{

getdata();

}
}

private void attachparameter(SqlCommand cmd,string parametername, Control cnt)
{

if (cnt is TextBox && ((TextBox)cnt).Text != string.Empty)
{

SqlParameter param = new SqlParameter(parametername, ((TextBox)cnt).Text);
cmd.Parameters.Add(param);
}
else if (cnt is DropDownList && ((DropDownList)cnt).SelectedValue != "-1")

{

SqlParameter param = new SqlParameter(parametername, ((DropDownList)cnt).SelectedValue);
cmd.Parameters.Add(param);

}
}

private void getdata()
{
string strcon = ConfigurationManager.ConnectionStrings["ConnectionStringdb"].ConnectionString;
using(SqlConnection con = new SqlConnection(strcon))
{
SqlCommand cmd = new SqlCommand("SearchEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;

//ATTACH THE PARAMETERS

attachparameter(cmd, "@Name", txtname);

attachparameter(cmd, "@Email", txtemail);

attachparameter(cmd, "@Age", txtage);

attachparameter(cmd, "@Gender", ddlgender);

con.Open();
gvemploye.DataSource = cmd.ExecuteReader();
gvemploye.DataBind();

}

}

protected void BtnSearch_Click1(object sender, EventArgs e)
{
getdata();

}

}
}

===============another way to write the stored procedure========================

select
    *
from employee
where
    Name = isnull(@Name, Name)
    and Age = isnull(@Age, Age)
    and email = isnull(@Email, email)
    and Gender = isnull(@Gender, Gender)
or
 
select
    *
from employee
where
    (@Name is null or Name = @Name)
    and (@Age is null or Age = @Age)
    and (@Email is null or email = @Email)
    and (@Gender is null or Gender = @Gender)

--second solution using dynamic sql

Collapse | Copy Code

--exec SearchEmployee @Name='anurag',@Age=23 
--exec SearchEmployee @Age=23 
alter proc SearchEmployee-- '','','',''
@Name varchar(50)=null,
@Age int=null
as
begin
 
declare @sql varchar(max),@sqlwhere varchar(max)
  
set @sql='select * from employee' 
 
if ((@Name is not null) and @Name <> '')
begin
set @sqlwhere=' Name='+ '''' + @Name + ''''
print 'sqlwhere '
end
 
 if ((@Age is not null) and @Age <> '')
begin
 print 'i m in age block '
    set @sqlwhere =isnull(@sqlwhere + ' and ', '') + ' Age = ''' + cast(@Age as varchar(50)) + ''' ';
print 'sqlwhere ' + cast(@sqlwhere as varchar(50))
end
 
 
 
if (@sqlwhere is not null)
begin
set @sql=@sql+' where ' + @sqlwhere;
end
else
begin
set @sql=@sql;
end
 
print @sql
exec (@sql) 
 
end

Asp.Net and C sharp

Wednesday, 2 October 2013

CALLING A STORED PROCEDURE AND POPULATING DATA

No comments:

Post a Comment