By Blankman


2008-11-14 16:28:33 8 Comments

My stored procedure has an output parameter:

@ID INT OUT

How can I retrieve this using ado.net?

using (SqlConnection conn = new SqlConnection(...))
{
    SqlCommand cmd = new SqlCommand("sproc", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    // add parameters

    conn.Open();

    // *** read output parameter here, how?
    conn.Close();
}

8 comments

@Sandeep Pandey 2018-05-14 07:20:17

Create the SqlParamObject which would give you control to access methods on the parameters

:

SqlParameter param = new SqlParameter();

SET the Name for your paramter (it should b same as you would have declared a variable to hold the value in your DataBase)

: param.ParameterName = "@yourParamterName";

Clear the value holder to hold you output data

: param.Value = 0;

Set the Direction of your Choice (In your case it should be Output)

: param.Direction = System.Data.ParameterDirection.Output;

@Greg R Taylor 2017-02-18 05:45:09

public static class SqlParameterExtensions
{
    public static T GetValueOrDefault<T>(this SqlParameter sqlParameter)
    {
        if (sqlParameter.Value == DBNull.Value 
            || sqlParameter.Value == null)
        {
            if (typeof(T).IsValueType)
                return (T)Activator.CreateInstance(typeof(T));

            return (default(T));
        }

        return (T)sqlParameter.Value;
    }
}


// Usage
using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand("storedProcedure", conn))
{
   SqlParameter outputIdParam = new SqlParameter("@ID", SqlDbType.Int)
   { 
      Direction = ParameterDirection.Output 
   };

   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Parameters.Add(outputIdParam);

   conn.Open();
   cmd.ExecuteNonQuery();

   int result = outputIdParam.GetValueOrDefault<int>();
}

@Genn 2016-06-17 18:27:23

That looks more explicit for me:

int? id = outputIdParam.Value is DbNull ? default(int?) : outputIdParam.Value;

@Vinícius Todesco 2015-11-11 12:49:55

string ConnectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(ConnectionString))
{
//Create the SqlCommand object
SqlCommand cmd = new SqlCommand(“spAddEmployee”, con);

//Specify that the SqlCommand is a stored procedure
cmd.CommandType = System.Data.CommandType.StoredProcedure;

//Add the input parameters to the command object
cmd.Parameters.AddWithValue(“@Name”, txtEmployeeName.Text);
cmd.Parameters.AddWithValue(“@Gender”, ddlGender.SelectedValue);
cmd.Parameters.AddWithValue(“@Salary”, txtSalary.Text);

//Add the output parameter to the command object
SqlParameter outPutParameter = new SqlParameter();
outPutParameter.ParameterName = “@EmployeeId”;
outPutParameter.SqlDbType = System.Data.SqlDbType.Int;
outPutParameter.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(outPutParameter);

//Open the connection and execute the query
con.Open();
cmd.ExecuteNonQuery();

//Retrieve the value of the output parameter
string EmployeeId = outPutParameter.Value.ToString();
}

Font http://www.codeproject.com/Articles/748619/ADO-NET-How-to-call-a-stored-procedure-with-output

@user1527246 2015-10-08 07:14:11

You can get your result by below code::

using (SqlConnection conn = new SqlConnection(...))
{
    SqlCommand cmd = new SqlCommand("sproc", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    // add other parameters parameters

    //Add the output parameter to the command object
    SqlParameter outPutParameter = new SqlParameter();
    outPutParameter.ParameterName = "@Id";
    outPutParameter.SqlDbType = System.Data.SqlDbType.Int;
    outPutParameter.Direction = System.Data.ParameterDirection.Output;
    cmd.Parameters.Add(outPutParameter);

    conn.Open();
    cmd.ExecuteNonQuery();

    //Retrieve the value of the output parameter
    string Id = outPutParameter.Value.ToString();

    // *** read output parameter here, how?
    conn.Close();
}

@BQ. 2008-11-14 17:24:28

The other response shows this, but essentially you just need to create a SqlParameter, set the Direction to Output, and add it to the SqlCommand's Parameters collection. Then execute the stored procedure and get the value of the parameter.

Using your code sample:

// SqlConnection and SqlCommand are IDisposable, so stack a couple using()'s
using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand("sproc", conn))
{
   // Create parameter with Direction as Output (and correct name and type)
   SqlParameter outputIdParam = new SqlParameter("@ID", SqlDbType.Int)
   { 
      Direction = ParameterDirection.Output 
   };

   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Parameters.Add(outputIdParam);

   conn.Open();
   cmd.ExecuteNonQuery();

   // Some various ways to grab the output depending on how you would like to
   // handle a null value returned from the query (shown in comment for each).

   // Note: You can use either the SqlParameter variable declared
   // above or access it through the Parameters collection by name:
   //   outputIdParam.Value == cmd.Parameters["@ID"].Value

   // Throws FormatException
   int idFromString = int.Parse(outputIdParam.Value.ToString());

   // Throws InvalidCastException
   int idFromCast = (int)outputIdParam.Value; 

   // idAsNullableInt remains null
   int? idAsNullableInt = outputIdParam.Value as int?; 

   // idOrDefaultValue is 0 (or any other value specified to the ?? operator)
   int idOrDefaultValue = outputIdParam.Value as int? ?? default(int); 

   conn.Close();
}

Be careful when getting the Parameters[].Value, since the type needs to be cast from object to what you're declaring it as. And the SqlDbType used when you create the SqlParameter needs to match the type in the database. If you're going to just output it to the console, you may just be using Parameters["@Param"].Value.ToString() (either explictly or implicitly via a Console.Write() or String.Format() call).

EDIT: Over 3.5 years and almost 20k views and nobody had bothered to mention that it didn't even compile for the reason specified in my "be careful" comment in the original post. Nice. Fixed it based on good comments from @Walter Stabosz and @Stephen Kennedy and to match the update code edit in the question from @abatishchev.

@Marcus 2011-12-28 15:13:22

You don't need conn.Close() as its inside a using block

@Walter Stabosz 2012-03-15 13:41:36

I think your use of int.MaxValue as the Size property is incorrect. int.MaxValue is a constant with the value 2,147,483,647. msdn.microsoft.com/en-us/library/… . The mistake is harmless in this example because the datatype is Int and "For fixed length data types, the value of Size is ignored.", but a zero would have sufficed.

@Stephen Kennedy 2012-05-26 16:05:50

.Value is of type object, so assigning it directly to an int without casting isn't going to work.

@Garry English 2015-04-22 05:52:04

For those that are using a DataReader, you must close it or read to the end of the data before you can view the output parameters.

@WACM161 2008-11-14 16:36:13

Not my code, but a good example i think

source: http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=624

using System; 
using System.Data; 
using System.Data.SqlClient; 


class OutputParams 
{ 
    [STAThread] 
    static void Main(string[] args) 
    { 

    using( SqlConnection cn = new SqlConnection("server=(local);Database=Northwind;user id=sa;password=;")) 
    { 
        SqlCommand cmd = new SqlCommand("CustOrderOne", cn); 
        cmd.CommandType=CommandType.StoredProcedure ; 

        SqlParameter parm= new SqlParameter("@CustomerID",SqlDbType.NChar) ; 
        parm.Value="ALFKI"; 
        parm.Direction =ParameterDirection.Input ; 
        cmd.Parameters.Add(parm); 

        SqlParameter parm2= new SqlParameter("@ProductName",SqlDbType.VarChar); 
        parm2.Size=50; 
        parm2.Direction=ParameterDirection.Output; 
        cmd.Parameters.Add(parm2); 

        SqlParameter parm3=new SqlParameter("@Quantity",SqlDbType.Int); 
        parm3.Direction=ParameterDirection.Output; 
        cmd.Parameters.Add(parm3);

        cn.Open(); 
        cmd.ExecuteNonQuery(); 
        cn.Close(); 

        Console.WriteLine(cmd.Parameters["@ProductName"].Value); 
        Console.WriteLine(cmd.Parameters["@Quantity"].Value.ToString());
        Console.ReadLine(); 
    } 
} 

@MusiGenesis 2008-11-14 17:07:33

Yep, this is correct. Just set the ParameterDirection property of the parameter. You don't need the cn.Close() line - the using{} block takes care of that.

@Nate Kindrew 2012-02-14 12:46:33

For anyone looking to do something similar using a reader with the stored procedure, note that the reader must be closed to retrieve the output value.

using (SqlConnection conn = new SqlConnection())
{
    SqlCommand cmd = new SqlCommand("sproc", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    // add parameters
    SqlParameter outputParam = cmd.Parameters.Add("@ID", SqlDbType.Int);
    outputParam.Direction = ParameterDirection.Output;

    conn.Open();

    using(IDataReader reader = cmd.ExecuteReader())
    {
        while(reader.Read())
        {
            //read in data
        }
    }
    // reader is closed/disposed after exiting the using statement
    int id = outputParam.Value;
}

@Nicklas Møller Jepsen 2017-02-15 21:23:52

I missed the fact that the reader must be closed before reading the output parameter. Thanks for pointing that out!

Related Questions

Sponsored Content

28 Answered Questions

[SOLVED] Get int value from enum in C#

  • 2009-06-03 06:46:39
  • jim
  • 1613830 View
  • 1892 Score
  • 28 Answer
  • Tags:   c# enums casting int

21 Answered Questions

40 Answered Questions

22 Answered Questions

4 Answered Questions

[SOLVED] Get output value in ADO.Net without stored procedure

6 Answered Questions

2 Answered Questions

[SOLVED] dataadapter fill missing parameter

  • 2012-12-14 13:54:32
  • jpavlov
  • 3000 View
  • 1 Score
  • 2 Answer
  • Tags:   c# asp.net

4 Answered Questions

[SOLVED] c# closing sqlconnection and sqldatareader or not?

Sponsored Content