Tuesday, December 16, 2008

HOWTO: SQL Server Return Value with Enterprise Library

    int returncode = 0;
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand("<your procedure>");
db.AddParameter(dbCommand, "@RETURN_VALUE", DbType.Int32, ParameterDirection.ReturnValue, String.Empty, DataRowVersion.Default, null);
db.ExecuteNonQuery(dbCommand);
if (!Int32.TryParse(Convert.ToString(dbCommand.Parameters["@RETURN_VALUE"].Value), out returncode))
returncode = -1;

The returncode local variable is useful, but generally not necessary. It's good to state your assumptions about the operation of external code explicitly.