Author : Prakash Pradeep Gopu
In this post I am going to explain about the How to create a “Stored procedure with output variable” and How to use this kind of stored procedures in the c#.
In this post I am going to explain about the How to create a “Stored procedure with output variable” and How to use this kind of stored procedures in the c#.
Let us create a sample table called Tbl_Employee with the following Data
EmpId | Name | Salary | DeptId |
1 | Prakash | 15000 | 6 |
2 | Pradeep | 20000 | 6 |
3 | Sathya | 40000 | 5 |
4 | Sandeep | 22000 | 6 |
5 | Harsha | 12000 | 5 |
6 | Spandana | 14000 | 6 |
8 | Super | 18000 | 5 |
10 | Kaka | 22000 | 6 |
12 | Mary | 60000 | 5 |
13 | Joseph | 42000 | 5 |
Now I am creating a stored procedure with the following requirement : I am passing the empname and Deptid to check the employee is employed or not. If the name matches then it will return the empID else it will return the 0 (the person is not employed)
create procedure ISEmployee
(@Name varchar(50),@DeptId int,@EmpId as int output)
as
begin
if((select EmpId from dbo.Tbl_Employee where Name=@Name and DeptId=@DeptId) >0 )
begin
set @EmpId=(select EmpId from dbo.Tbl_Employee where Name=@Name and DeptId=@DeptId)
end
else
begin
set @EmpId=0
end
print @EmpId
return @EmpId
End
Now if you execute this stored procedure with following data it will give the folloing result :
exec ISEmployee 'Prakash',6,0
Output : 1
Because the Empname 'Prakash'is existed with the depatrtment name is 6 and his empid is 1.
Calling the stored procedure from C#.net
Create sample aspx page as follows :
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.style1
{
width: 100%;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<table class="style1">
<tr>
<td width="15%">
</td>
<td width="35%">
</td>
<td width="35%">
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td>
EmpName</td>
<td>
<asp:TextBox ID="Txtempname" runat="server"></asp:TextBox>
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td>
Dept</td>
<td>
<asp:TextBox ID="Txtdept" runat="server"></asp:TextBox>
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td align="center" colspan="2">
<asp:Label ID="Lblmsg" runat="server" Font-Bold="True" ForeColor="#CC0066"
Visible="False"></asp:Label>
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td align="center" colspan="2">
<asp:Button ID="BtnIsEmploee" runat="server" Text="IsEmployee"
onclick="BtnIsEmploee_Click" />
</td>
<td>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
In the button click event write the following code :
protected void BtnIsEmploee_Click(object sender, EventArgs e)
{
string connetion = ConfigurationManager.ConnectionStrings["Bloggerconnection"].ToString();
SqlConnection con = new SqlConnection(connetion);
SqlCommand cmd = new SqlCommand("ISEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name", Txtempname.Text);
cmd.Parameters.AddWithValue("@DeptId", Convert.ToInt32(Txtdept.Text));
cmd.Parameters.AddWithValue("@EmpId", 0);
//setting parameter direction
cmd.Parameters["@EmpId"].Direction = ParameterDirection.Output;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
//reading parameter value
int Isemp = (int)cmd.Parameters["@EmpId"].Value;
if (Isemp > 0)
{
Lblmsg.Visible = true;
Lblmsg.Text = "The emploee details exist with EmpID " + Isemp;
}
else
{
Lblmsg.Visible = true;
Lblmsg.Text = "The emploee details not exist ";
}
}
OutPut :
No comments:
Post a Comment