Sunday, 30 October 2016

Calling stored procedure with OUTPUT parameter using C# (C-Sharp) in ASP.Net

In this article, I will explain you how to call stored procedure with OUTPUT parameter using ado.net code with asp.net. First, we need to create a table with three columns EmpId, Name, Gender. We will supply Male or Female as input parameter value and return count of Male or Female employees as output parameter value based on input parameter.

Creating Database Table

Below is the script to create table and insert some dummy records.

create table tblEmployee
(
[EmpId] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](20) NULL,
[Gender] [varchar](10) NULL
)
insert into tblEmployee values('Rahul','Male')
insert into tblEmployee values('Mary','Female')
insert into tblEmployee values('John','Male')
insert into tblEmployee values('Mathew','Male')

Creating Stored Procedure

Below is the stored procedure with two parameter. @Gender input parameter and @GenderCount is the output parameter which will return count of Male of Female employee. You can use OUTPUT or OUT keyword to return value.

create procedure proc_Employee
@Gender varchar(10)=null,
@GenderCount int output
as
begin
 select @GenderCount=COUNT(*) from tblEmployee where Gender=@Gender
end

In Designer File

In designer file, create a DropDownList control and Label control as shown below.

<asp:DropDownList ID="ddlGender" runat="server" AutoPostBack="true"
    OnSelectedIndexChanged="ddlGender_SelectedIndexChanged">
    <asp:ListItem>Select</asp:ListItem>
    <asp:ListItem>Male</asp:ListItem>
    <asp:ListItem>Female</asp:ListItem>
</asp:DropDownList><br />
<asp:Label ID="lblCount" runat="server"></asp:Label>

C# Code to call stored procedure with output parameter using ADO.Net

Below is the C# code to get male or female employee count. Set @GenderCount parameter's direction property to ParameterDirection.Output. Once the procedure is executed, you can get the value of @GenderCount using Value property.

protected void ddlGender_SelectedIndexChanged(object sender, EventArgs e)
{
    if (ddlGender.SelectedIndex != 0)
    {
        string ConnectionString = "Server=xxxx; database=xxxx; user id=xxxx; password=xxxx";

        SqlConnection con = new SqlConnection(ConnectionString);

        SqlCommand cmd = new SqlCommand("proc_Employee", con);
        cmd.CommandType = CommandType.StoredProcedure;

        // pass input parameter
        cmd.Parameters.AddWithValue("@Gender", ddlGender.SelectedItem.Text);

        cmd.Parameters.Add("@GenderCount", SqlDbType.Int);
        cmd.Parameters["@GenderCount"].Direction = ParameterDirection.Output;

        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();

        string GenderCount = cmd.Parameters["@GenderCount"].Value.ToString();

        lblCount.Text =   ddlGender.SelectedItem+" count is " +GenderCount;
    }
}

Below is the another way to add OUTPUT parameter.

// pass input parameter
cmd.Parameters.AddWithValue("@Gender", ddlGender.SelectedItem.Text);

SqlParameter outputParameter = new SqlParameter();
outputParameter.ParameterName = "@GenderCount";
outputParameter.SqlDbType = SqlDbType.Int;
outputParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outputParameter);

con.Open();
cmd.ExecuteNonQuery();
con.Close();

string GenderCount = outputParameter.Value.ToString();

lblCount.Text = ddlGender.SelectedItem + " count is " + GenderCount;
Share:

0 comments:

Post a Comment

Email Subscription

Subscribe to our newsletter to get the latest articles directly into your inbox

Blog Archive

BUY FROM AMAZON