/// <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)}"); } } }