Retrieve Store Procedure's Output parameter by C# (ASP.net)

How to retrieve store procedure's output parameter by C#? Here I have explained with a simple example. I created a simple store procedure with output parameter. Then I catch it from code behind page of asp.net and displayed it to a label. Here I used a class DBConnector to connect with database. Lets look on the demo.

Step 1: Set connection string in web.config.

Step 2: Create a store procedure with output parameter.

CREATE PROCEDURE [dbo].[sp_output_param]
(
     @input_param VARCHAR(200)
    , @Response VARCHAR(250) OUTPUT
)
AS
    --DECLARE @myName


BEGIN
    
    SET @Response = 'Welcome ' + @input_param
    
END

Step 3: Create a DBConnector Class to retrieve data from database.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public class DBConnector
{
    private string connectionString = null;
    private SqlConnection sqlConn = null;
    private SqlCommand cmd = null;

    public DBConnector()
    {
        connectionString = ConfigurationManager.ConnectionStrings["SQLServerConnectionString"].ToString();
    }


    public SqlCommand GetCommand()
    {
        cmd = new SqlCommand();
        cmd.Connection = sqlConn;
        return cmd;
    }

    public SqlConnection GetConn()
    {
        sqlConn = new SqlConnection(connectionString);
        return sqlConn;
    }

}

Step 4 : Create a label name lblMsg in asp page. Then in a button event, write the following code. You will see your desired output in the lebel which is actually the value of output parameter. Here btnGetOutputParam_Click is the click event of Button btnGetOutputParam.

protected void btnGetOutputParam_Click(object sender, EventArgs e)
{
        SqlConnection sqlConn;
        SqlCommand cmd;

        DBConnector objDBConnector = new DBConnector();
        sqlConn = objDBConnector.GetConn();

        cmd = objDBConnector.GetCommand();

        SqlDataReader rdr = null;

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_output_param";
        cmd.Parameters.AddWithValue("@input_param", "Mahedee");
        cmd.Parameters.Add("@Response", SqlDbType.VarChar, 250);
        cmd.Parameters["@Response"].Direction = ParameterDirection.Output;

        try
        {
            if (sqlConn.State == ConnectionState.Closed)
                sqlConn.Open();

            rdr = cmd.ExecuteReader();

            string outputValue = cmd.Parameters["@Response"].Value.ToString();
            this.lblMsg.Text = outputValue;
        }
        catch (Exception exp)
        {
            throw (exp);
        }
        finally
        {
            if (sqlConn.State == ConnectionState.Open)
                sqlConn.Close();
        }

    }

You will see in label - Welcome Mahedee which is provided by output parameter of store procedure.