/// <summary>
/// Helper method that handles the Execute* class of ADO.NET methods. Since the bulk of that logic
/// is identical, it has been factored into this single method.
/// </summary>
/// <param name="type">One of ExecuteReader, ExecuteScalar, ExecuteNonQuery</param>
/// <param name="connection">An open instance of the SqlConnection object</param>
/// <param name="transaction">Transaction (if any) associated with the open connection</param>
/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
/// <param name="args">Optional list of parameters to be passed to the stored procedure</param>
/// <returns>object</returns>
private static object Execute(ExecuteType type, SqlConnection connection, SqlTransaction transaction,
string storedProcedureName, params SqlParameter[] args)
{
Exception exc = null;
if (connection.State != ConnectionState.Open)
{
throw new CustomExceptionHandler("Connection is not open");
}
connection.FireInfoMessageEventOnUserErrors = true;
//since this method is a static method, we need to find a way to share the exc object back to this function.
//Previosly a a global static object was used but it cause cross talk between the threads as the static
//objects are share across the threads. By using anonymous delegates we can share the exc variable which is
//a stack variable above and when the delegate executes it is dealing with a local stack variable and which
//is going to be different for every thread.
SqlInfoMessageEventHandler evtHandler = delegate(object sender, SqlInfoMessageEventArgs e)
{
if (Convert.ToInt32(e.Errors[0].Class) != 0)
{
exc = new CustomExceptionHandler(e.Message);
}
};
connection.InfoMessage += evtHandler;
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = storedProcedureName;
// command.CommandTimeout = Settings.GetKeyValue("Database.CommandTimeout", 0);
command.CommandTimeout = 10020;
if (transaction != null)
{
command.Transaction = transaction;
}
if ((args != null) && (args.Length > 0))
{
foreach (SqlParameter p in args)
{
command.Parameters.Add(p);
}
}
switch (type)
{
case
ExecuteType.ExecuteNonQuery:
object o = command.ExecuteNonQuery();
if (exc != null)
{
throw exc;
}
return(o);
case ExecuteType.ExecuteReader:
return(command.ExecuteReader(CommandBehavior.CloseConnection));
case ExecuteType.ExecuteScalar:
return(command.ExecuteScalar());
}
throw new CustomExceptionHandler(String.Format("Unsupported ExecuteType '{0}'", type));
}
