/// <summary>
/// <para>Executes a SQL stored procedure on a provided connection.</para>
/// </summary>
/// <param name="database">
/// <para>The <see cref="Database"/> to which the specified connection
/// object belongs. This object is for informational purposes only,
/// and will not be used to create new connections.</para>
/// </param>
/// <param name="connection">
/// <para>The <see cref="SqlConnection"/> on which to execute sproc.
/// If this connection is not yet open, it will be opened and closed.
/// If this connection is already open, it will NOT be closed. In this case,
/// the caller is responsible for its disposal.</para>
/// </param>
/// <param name="procedureName">
/// <para>The name of the sproc to execute.</para>
/// </param>
/// <param name="parameterMapper">
/// <para>A delegate that will populate the parameters in the sproc call.
/// Specify <see langword="null"/> if the sproc does not require parameters.</para>
/// </param>
/// <param name="outputMapper">
/// <para>A delegate that will read the value of the parameters returned
/// by the sproc call. Specify <see langword="null"/> if no output parameters
/// have been provided by the <paramref name="parameterMapper"/> delegate.</para>
/// </param>
/// <returns>
/// <para>An <see cref="Int32"/> value indicating the number of rows
/// affected by this sproc call.</para>
/// </returns>
/// <exception cref="ArgumentNullException">
/// <para>The argument <paramref name="database"/> is <see langword="null"/>.</para>
/// <para>-or-</para>
/// <para>The argument <paramref name="connection"/> is <see langword="null"/>.</para>
/// <para>-or-</para>
/// <para>The argument <paramref name="procedureName"/> is <see langword="null"/>.</para>
/// </exception>
/// <exception cref="SafeProcedureException">
/// <para>An unexpected exception has been encountered during the sproc call.</para>
/// </exception>
public static int ExecuteNonQuery(Database database, SqlConnection connection, string procedureName,
ParameterMapper parameterMapper, OutputParameterMapper outputMapper)
{
if (database == null)
{
throw new ArgumentNullException("database");
}
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if (procedureName == null)
{
throw new ArgumentNullException("procedureName");
}
int result = 0;
try
{
SqlCommand command = CommandFactory.CreateParameterMappedCommand(connection, procedureName, parameterMapper);
bool doClose = false;
if (connection.State != ConnectionState.Open)
{
doClose = true;
connection.Open();
}
result = command.ExecuteNonQuery();
if (doClose)
{
connection.Close();
}
if (outputMapper != null)
{
ParameterSet outputParams = new ParameterSet(command.Parameters);
outputMapper(outputParams);
}
}
catch (Exception e)
{
throw new SafeProcedureException(database, procedureName, e);
}
return(result);
}
/// <summary>
/// Executes a single-result procedure and fires a mapping delegate for each row that is returned and then fires a parameter mapping delegate for output params.
/// </summary>
/// <param name="database"></param>
/// <param name="procedureName"></param>
/// <param name="parameterMapper"></param>
/// <param name="recordMapper"></param>
/// <param name="outputMapper"></param>
public static void ExecuteAndMapRecords(Database database, string procedureName, ParameterMapper parameterMapper, RecordMapper recordMapper, OutputParameterMapper outputMapper)
{
try
{
using (SqlConnection connection = database.GetConnection())
{
SqlCommand command = CommandFactory.CreateParameterMappedCommand(connection, procedureName, parameterMapper);
connection.Open();
IRecordSet reader = new DataRecord(command.ExecuteReader(CommandBehavior.CloseConnection));
while (reader.Read())
{
recordMapper(reader);
}
connection.Close();
if (outputMapper != null)
{
outputMapper(new ParameterSet(command.Parameters));
}
}
}
catch (Exception e)
{
throw new SafeProcedureException(database, procedureName, e);
}
}
/// <summary>
/// Executes a procedure and allows the caller to inject a resultset mapper and an output parameter mapper.
/// </summary>
/// <param name="database"></param>
/// <param name="procedureName"></param>
/// <param name="parameterMapper"></param>
/// <param name="result"></param>
public static void ExecuteAndMapResults(Database database, string procedureName, StoredProcedureParameterList parameterList, ResultMapper result, OutputParameterMapper outputMapper)
{
try
{
using (SqlConnection connection = database.GetConnection())
{
SqlCommand command = CommandFactory.CreateCommand(connection, database.InstanceName, procedureName, parameterList);
connection.Open();
IRecordSet reader = new DataRecord(command.ExecuteReader(CommandBehavior.CloseConnection));
result(reader);
connection.Close();
if (outputMapper != null)
{
outputMapper(new ParameterSet(command.Parameters));
}
}
}
catch (Exception e)
{
throw new SafeProcedureException(database, procedureName, e);
}
}
/// <summary>
/// <para>Executes a stored procedure on the specified database.</para>
/// </summary>
/// <param name="database">
/// <para>The <see cref="Database"/> on which the sproc should be executed.</para>
/// </param>
/// <param name="procedureName">
/// <para>The name of the sproc to execute.</para>
/// </param>
/// <param name="parameterMapper">
/// <para>A delegate that will populate the parameters in the sproc call.
/// Specify <see langword="null"/> if the sproc does not require parameters.</para>
/// </param>
/// <param name="outputMapper">
/// <para>A delegate that will read the value of the parameters returned
/// by the sproc call. Specify <see langword="null"/> if no output parameters
/// have been provided by the <paramref name="parameterMapper"/> delegate.</para>
/// </param>
/// <returns>
/// <para>The value returned by as part of the result set.</para>
/// </returns>
/// <exception cref="ArgumentNullException">
/// <para>The argument <paramref name="database"/> is <see langword="null"/>.</para>
/// <para>-or-</para>
/// <para>The argument <paramref name="procedureName"/> is <see langword="null"/>.</para>
/// </exception>
/// <exception cref="SafeProcedureException">
/// <para>An unexpected exception has been encountered during the sproc call.</para>
/// </exception>
public static object ExecuteScalar(Database database, string procedureName, ParameterMapper parameterMapper, OutputParameterMapper outputMapper)
{
if (database == null)
{
throw new ArgumentNullException("database");
}
if (procedureName == null)
{
throw new ArgumentNullException("procedureName");
}
object result;
try
{
using (SqlConnection connection = database.GetOpenConnection())
{
result = ExecuteScalar(database, connection, procedureName, parameterMapper, outputMapper);
}
}
catch (SafeProcedureException)
{
throw;
}
catch (Exception e)
{
throw new SafeProcedureException(database, procedureName, e);
}
return(result);
}
