Get Output parameter value from Stored Procedure in Asp.Net using C#

Here I will going to explain how to use and get value from Stored Procedure using Output Parameter in ASP.Net C#.

 For this post I have a Table with named tbl_user is used which contains UserID and UserName columns. The name of the User is fetched by UserID using Output Parameter in SQL Server Stored Procedure in ASP.Net c#.

Database

For this example I have created a database named UserDB which has a table named tbl_User with the schema as follows.

In the tbl_user Table I have inserted few records as shown below

Connection String

Following is the connection string defined in the Connection Strings section of the Web.Config file.
You need to modify it as per your SQL Server Instance and Database name.

< connectionStrings >

 <addname="constr"connectionString="Data Source=.\SQL2008;Initial Catalog=UserDB;User id = sa;password=sa@123"/>

</connectionStrings>

HTML Markup

The HTML consists of an ASP.Net TextBox in which the User Id will be passed to the
SQL Server Stored Procedure, an ASP.Net Label within which the name of the User fetched using Output Parameter will be displayed and finally an ASP.Net Button press to trigger the process of fetching the User Name from tbl_User table based on supplied User Id.

Enter UserId:

<asp:TextBox ID="txtUserId" runat="server" />

<asp:Button ID="btnSubmit" OnClick="Submit" Text="Submit" runat="server" />

<br />

<br />

<asp:Label ID="lblUserName" runat="server" />

Stored Procedure
 The Stored Procedure accepts the following two Parameters
1. UserId – This is an INPUT Parameter used to pass the Id of the User.
2. UserName – This is an OUTPUT Parameter used to fetch the Name of the User based on its UserId.

Note: Output Parameter is identified by the keyword OUTPUT.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[GetUserName]

      @UserId INT,

      @UserName VARCHAR(50) OUTPUT

AS

BEGIN

      SET NOCOUNT ON;

     

      SELECT @UserName = UserName

      FROM tbl_User

      WHERE UserID = @UserId

END

Get Output parameter from Stored Procedure in ASP.Net

The following event handler is execute when the Button is pressed, it simply makes a database call to the stored procedure GetUserName.

First the Input Parameter @UserId is added along with Value i.e. the User Id entered in the TextBox using AddWithValue method.

Next the second Parameter @UserName is added. Since @UserName is an Output Parameter we cannot use AddWithValue function hence it is added using the Add method of SqlCommand with its Data Type and Size specified.

Once the @UserName Parameter is added, then its Direction is set to Output since by default the Direction of all Parameter is Input.

Once the Stored Procedure is executed, the value fetched from the Stored Procedure is stored in the Value property of the @UserName Output Parameter.

Finally the name of the User is displayed on page.

protected void GetUserName(object sender, EventArgs e)

{

    string constring = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;

    using (SqlConnection con = new SqlConnection(constring))

    {

        using (SqlCommand cmd = new SqlCommand("GetUserName", con))

        {

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@UserId",                  int.Parse(txtUserId.Text.Trim()));

            cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 50);

            cmd.Parameters["@UserName"].Direction = ParameterDirection.Output;

            con.Open();

            cmd.ExecuteNonQuery();

            con.Close();

            lblUserName.Text = "User Name : " + cmd.Parameters["@UserName"].Value.ToString();

        }

    }

}