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.

<connectionstrings>
     <add connectionstring="Data Source=SOFT;Initial Catalog=TestDB;User ID=sa;Password=sa" name="SQLServerConnectionString" providername="System.Data.SqlClient">
</add></connectionstrings>

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.