ADO.net - Using Output Parameters in Stored Procedures

An output parameter is a parameter that is passed into the SQL stored procedure, but whose value can be set in the stored procedure. This assigned parameter, then is readable back from the application that called the stored procedure.



To use an output parameter you need to indicate that the parameter is intended for output via the OUTPUT keyword in stored procedure.
 
In this artical i will show you how to retrive output parameter value in ASP.net application.
 
Step 1
The follwing stored procedure return the total table count of current database,it is look like this

CREATE PROCEDURE dbo.TableCount
(
    @Count NUMERIC(18,0) OUTPUT -- Declarae OutPut Parameter
)
AS
    BEGIN
    
        SET @Count=(SELECT COUNT(*) FROM sys.tables)
        
    END
GO

Step 2
Create a Web Project.

Step 3
Create a TableCount static class in App_Code folder.

To access the value of an output parameter from your ASP.NET application you need to add a parameter and Direction property is set to Output. After you call the stored procedure the output parameter's value is accessible through the Value property, it is look like this

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


public static class TableCount
{

    #region Methods

    public static Nullable<long> CountTable()
    {
        long? Count = null;
        try
        {
            SqlConnection SqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString.Trim());
            SqlCon.Open();

            SqlCommand SqlComm = new SqlCommand();

            SqlComm.Connection = SqlCon;   

            // Add the parameter
            SqlComm.Parameters.Add("@Count", SqlDbType.BigInt).Value = 0;
            // Set Direction of paramter as output
            SqlComm.Parameters["@Count"].Direction = ParameterDirection.Output;

            SqlComm.CommandType = CommandType.StoredProcedure;
            SqlComm.CommandText = "TableCount";

            // Read the data
            SqlDataReader SqlDR = SqlComm.ExecuteReader();

            // Get the value from output parameter
            try
            {
                Count =(long ?)SqlComm.Parameters["@Count"].Value;    
            }
            catch (Exception)
            {
                Count = 0;
            }
  

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message); 
        }

        return Count; 
    }

    #endregion
}

Step 4
Write a value in page,it is look like this

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

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            long Count =(long) TableCount.CountTable();
            Response.Write("Total Table - " + Count);  
        }
        catch (Exception)
        { }
    }
}

Run the Project

Download
Download Source Code