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.

Please share, if it is helpfulShare on FacebookShare on LinkedInTweet about this on TwitterShare on Google+Email this to someonePrint this page

3 thoughts on “Retrieve Store Procedure’s Output parameter by C# (ASP.net)

  1. I just want to say I am all new to blogging and actually enjoyed your web page. More than likely I’m going to bookmark your site . You amazingly have superb stories. Regards for sharing your website page.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>