Get output parameter value in ADO.NET

Question

Hi,

My stored procedure has an output paramter:

@ID INT OUT

How can I retrieve this using ado.net?

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

            // add parameters


 conn.Open();

 // *** GRAB output paramter here, how?????????
 conn.Close();
    }

    }

Answer 1

Not my code, but a good example i think

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(); 
} 
} 
}

Answer 2

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

Using your code sample:

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

     // add parameters
     cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, int.MaxValue, ParameterDirection.Output));

     conn.Open();

      // *** GRAB output paramter here, how?????????
     cmd.ExecuteNonQuery();
     int id = cmd.Parameters["@ID"].Value;

     conn.Close();
   }
}

Be careful when getting the Parameters[].Value, since the type needs to match what you're declaring it as when you create the SqlParameter. And if you're going to just output it to the console, you may just be using Parameters["@Param"].Value.ToString() if it's some other type. This will bite you more often if you're not using the SQL Provider (i.e. using Oracle or some other provider, where the types are non-.NET native).