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