.netstored-proceduresdbconnection

Executing a stored procedure using a DbConnection


I need to execute a stored procedure on a database. This is what I got so far that works:

protected DbProviderFactory dbProviderFactory;
this.dbProviderFactory = DalFactory.GetFactory(this.adapterConfiguration);

DbConnection dbConnection = dbProviderFactory.CreateConnection();

dbConnection.ConnectionString = this.adapterConfiguration.DatabaseInformation.ExternalDatabaseInformation.connectionString;
            try
            {
                dbConnection.Open();
            }
            catch (Exception e)
            {

                throw;
            }

I suspect that DbCommand would do it, but haven't found anything working. Let's say that the stored procedure by the name "initialize" has to be executed. How do I do that?


Solution

  • For SqlServer, this could be like this :

    DbCommand command = new SqlCommand();
    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.CommandText = "<your stored proc>";
    command.Connection = dbConnection;
    
    SqlParameter param1 = new SqlParameter("<your parameter>", MyVar);
    command.Parameters.Add(param1);
    //[...]
    
    SqlParameter returnValue = new SqlParameter("ReturnValue", User);
    returnValue.Direction = System.Data.ParameterDirection.ReturnValue;
    command.Parameters.Add(returnValue);
    
    command.Connection.Open();
    command.ExecuteNonQuery();
    int result = (int)command.Parameters["ReturnValue"].Value;
    command.Connection.Close();