/// <summary>
/// Run a stored proceedure
/// </summary>
/// <param name="transaction">transaction to use</param>
/// <param name="storedProcName">Name of Stored Proceedure to run</param>
/// <param name="sqlParams">Parameters to add to query</param>
/// <param name="opts">Options that track how logging should be handled</param>
/// <param name="logger">App logger reference to use</param>
/// <param name="exceptionOnError">should an exception be throw on negative return code</param>
/// <returns>Filled dataset</returns>
public static DataSetResult GetDataSet(SqlTransaction transaction, string storedProcName, List <SqlParameter> sqlParams, IStoredProcOpts opts, ILogger logger, bool exceptionOnError = true)
{
DataSetResult ds = null;
try
{
SqlConnection conn = transaction.Connection;
using (SqlCommand cmd = new SqlCommand(storedProcName, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
if (sqlParams != null && sqlParams.Count > 0)
{
cmd.Parameters.AddRange(sqlParams.ToArray());
}
//Add the return value
SqlParameter returnParameter = new SqlParameter("@return_value", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(returnParameter);
//verify we have an open connection or the query will fail
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
ds = new DataSetResult();
adapter.Fill(ds);
ds.Result = (int)cmd.Parameters["@return_value"].Value;
if (ds.Tables.Count > 1)
{
DataBind.SetDataTableNames(ds);
}
else
{
throw new DataBindException("Incorrect number of datatables returned");
}
}
//prevent 'The SqlParameter is already contained by another SqlParameterCollection' exception when params are reused
cmd.Parameters.Clear();
}
CheckForErrors(storedProcName, sqlParams, ds, opts, exceptionOnError, logger);
}
catch (Exception ex)
{
string error = string.Format("Unable to run {0}, Params:: {1}", storedProcName, ParamsToString(sqlParams));
DataAccessException e = new DataAccessException(error, ex);
//logger.LogCritical(new EventId(), e, error);
throw e;
}
return(ds);
}
/// <summary>
/// Checks all datatables in a DataSetResult for any errors
/// </summary>
static void CheckForErrors(string storedProcName, List <SqlParameter> sqlParams, DataSetResult ds, IStoredProcOpts opts, bool exceptionOnError, ILogger logger)
{
//check for db error
if (exceptionOnError && ds.Result < 0)
{
throw new StoredProcedureException("Stored procedure returned an error code:" + ds.Result.ToString(), ds.Result);
}
foreach (DataTable dt in ds.Tables)
{
if (opts.MaxDBRowsException != null && dt.Rows.Count >= opts.MaxDBRowsException)
{
string message = $"Stored proceedure {storedProcName} has exceeded the MaxDBRowsException threshold value. Params: {ParamsToString(sqlParams)}";
logger.LogCritical(message);
throw new StoredProcedureException(message, ds.Result);
}
if (opts.LogWarningMaxDBRows != null && dt.Rows.Count >= opts.LogWarningMaxDBRows)
{
logger.LogWarning($"Stored proceedure {storedProcName} has exceeded the LogWarningMaxDBRows threshold value. Params: {ParamsToString(sqlParams)}");
}
}
}
