SqlParameter w/ ParameterDirection.ReturnValue not returning value

Topics: Data Access Application Block, Enterprise Library Core
Sep 19, 2010 at 7:23 AM

did a simple test:

                string sprocName = @"dbo.PsLineClassLoadById";
                SqlParameter[] parameters =
                        new SqlParameter( "@rowId", SqlDbType.Int, 4, ParameterDirection.Input, false,
                               0, 0, string.Empty, DataRowVersion.Default, rowId ),
                        new SqlParameter( "@ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false,
                               0, 0, string.Empty, DataRowVersion.Default, null )
                SqlDatabase sqlDatabase = new SqlDatabase(connectionString);
                DbCommand dbCommand = (DbCommand)BuildDbCommand(sqlDatabase, sprocName, parameters);
                DataSet dataSet = sqlDatabase.ExecuteDataSet(dbCommand);
                DataTable dataTable = dataSet.Tables[0];
                return (int)parameters[parameters.Length - 1].Value; // throw exception!

        public static IDbCommand BuildDbCommand(Database pDb, string sprocName, IDataParameter[] pParameters)
            DbCommand dbCommand = pDb.GetStoredProcCommand(sprocName);
            foreach (SqlParameter sqlParameter in pParameters)
                if (sqlParameter != null)
                    // Check for derived output value with no value assigned
                    if ((sqlParameter.Direction == ParameterDirection.InputOutput ||
                        sqlParameter.Direction == ParameterDirection.Input) &&
                        (sqlParameter.Value == null))
                        sqlParameter.Value = DBNull.Value;
                    pDb.AddParameter(dbCommand, sqlParameter.ParameterName, sqlParameter.DbType, sqlParameter.Direction,
                        sqlParameter.SourceColumn, sqlParameter.SourceVersion, sqlParameter.Value);
            return dbCommand;                      

Store Procedure has "return (88)" at the end, and DataSet returns successfully w/ data populated, but parameters[parameters.Length - 1].Value returns null, did I do sth wrong? (Or it's a bug)

Sep 20, 2010 at 2:44 AM

I think you're using the approach of creating a sqlparameter because of the question you posted in your other thread.  Anyway, the way to get the value of your return value or output parameter is to use the GetParameterValue method of the Database object:

object returnValue = db.GetParameterValue(command, "ReturnValue");

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.