Get output parameter value in ADO NET

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

public static class SqlParameterExtensions       public static T GetValueOrDefault lt T gt  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 lt int gt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

That looks more explicit for me      int  id   outputIdParam Value is DbNull   default int     outputIdParam Value

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

That looks more explicit for me      int  id   outputIdParam Value is DbNull   default int     outputIdParam Value

User · Answer

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

User · Answer

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

User · Answer

public static class SqlParameterExtensions       public static T GetValueOrDefault lt T gt  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 lt int gt

[c#] Get output parameter value in ADO.NET

Examples related to c#

Examples related to .net

Examples related to ado.net