none
Access Stored Procedure Parameters RRS feed

  • Question

  • Hi,

     

    Does anyone know how to get the parameters of a stored procedure in C#?

     

    I can get the stored procedure details through getSchema("Procedures") but is there anyway of accessing the parameters of a particular stored procedure?

     

    Any help would be much appreciated.

     

    Cheers,

    Turbojohn

    Friday, August 3, 2007 2:08 PM

Answers

  • Hi Turbojohn,

     

    SqlCommandBuilder.DeriveParameters Method retrieves parameter information from the stored procedure specified in the SqlCommand and populates the Parameters collection of the specified SqlCommand object. Try something like the following:

    Code Snippet

                    private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)

                    {

                            if( connection == null ) throw new ArgumentNullException( "connection" );

                            if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

     

                            SqlCommand cmd = new SqlCommand(spName, connection);

                            cmd.CommandType = CommandType.StoredProcedure;

     

                            connection.Open();

                            SqlCommandBuilder.DeriveParameters(cmd);

                            connection.Close();

     

                            if (!includeReturnValueParameter)

                            {

                                    cmd.Parameters.RemoveAt(0);

                            }

                   

                            SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];

     

                            cmd.Parameters.CopyTo(discoveredParameters, 0);

     

                            // Init the parameters with a DBNull value

                            foreach (SqlParameter discoveredParameter in discoveredParameters)

                            {

                                    discoveredParameter.Value = DBNull.Value;

                            }

                            return discoveredParameters;

                    }

     

    Hope this helps.

    Regards

    Sunday, August 5, 2007 9:45 AM
  • Thanks for your help Rong-Chung.

     

    I found a solution through getSchema("ProcedureParameters");

    This can be used to fill a DataTable of a procedures parameters which can then be manipulated as desired. For example, I require the parameter name, argument number and sequence number of a parameter. These values can be taken from the DataTable. This works for the Oracle database I am using.

     

    Turbojohn

     

    Tuesday, August 7, 2007 11:44 AM

All replies

  • Hi Turbojohn,

     

    SqlCommandBuilder.DeriveParameters Method retrieves parameter information from the stored procedure specified in the SqlCommand and populates the Parameters collection of the specified SqlCommand object. Try something like the following:

    Code Snippet

                    private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)

                    {

                            if( connection == null ) throw new ArgumentNullException( "connection" );

                            if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

     

                            SqlCommand cmd = new SqlCommand(spName, connection);

                            cmd.CommandType = CommandType.StoredProcedure;

     

                            connection.Open();

                            SqlCommandBuilder.DeriveParameters(cmd);

                            connection.Close();

     

                            if (!includeReturnValueParameter)

                            {

                                    cmd.Parameters.RemoveAt(0);

                            }

                   

                            SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];

     

                            cmd.Parameters.CopyTo(discoveredParameters, 0);

     

                            // Init the parameters with a DBNull value

                            foreach (SqlParameter discoveredParameter in discoveredParameters)

                            {

                                    discoveredParameter.Value = DBNull.Value;

                            }

                            return discoveredParameters;

                    }

     

    Hope this helps.

    Regards

    Sunday, August 5, 2007 9:45 AM
  • Thanks for your help Rong-Chung.

     

    I found a solution through getSchema("ProcedureParameters");

    This can be used to fill a DataTable of a procedures parameters which can then be manipulated as desired. For example, I require the parameter name, argument number and sequence number of a parameter. These values can be taken from the DataTable. This works for the Oracle database I am using.

     

    Turbojohn

     

    Tuesday, August 7, 2007 11:44 AM