Pastable version of ExecuteCommandBatch

<pre class='prettyprint' language='cs'>static class SqlExec
    {
        public static void ExecCommandBatch(System.Data.Common.DbConnection conn, System.Data.IsolationLevel isolation, ExceptionFailSafeEventHandler&lt;System.Exception&gt; failSafe, params System.Data.Common.DbCommand[] cmds)
        {
            if ((conn == null))
                throw new System.ArgumentNullException(&quot;conn&quot;);
            if ((cmds.Length &lt; 2))
                throw new System.ArgumentException(&quot;Must supply more than one Command&quot;);
            // TODO: validate isolation?
            bool success = false;
            BatchCommandFailedException excp = null;
            ExceptionFailSafeEventArgs&lt;System.Exception&gt; args = new ExceptionFailSafeEventArgs&lt;System.Exception&gt;();
            int totalAffectedRows = 0;
            int length = cmds.Length - 1;
            int i;
            if ((conn.State != ConnectionState.Open))
                conn.Open();
            DbTransaction trans;
            try
            {
                trans = conn.BeginTransaction(isolation);
            }
            catch (System.Exception ex)
            {
                trans = conn.BeginTransaction();
            }
            for (i = 0; i &lt;= length; i++)
            {
                cmds[i].Connection = conn;
                cmds[i].Transaction = trans;
            }
            try
            {
                for (i = 0; i &lt;= length; i++)
                {
                    totalAffectedRows += cmds[i].ExecuteNonQuery();
                }
                success = true;
            }
            catch (System.Exception ex)
            {
                excp = new BatchCommandFailedException(cmds[i], i, ex);
            }
            finally
            {
                if (success)
                {
                    trans.Commit();
                }
                else
                {
                    trans.Rollback();
                    totalAffectedRows = 0;
                    args.Exception = excp.InnerException;
                    if (failSafe != null)
                        failSafe.Invoke(typeof(SqlExec), args);
                }
                trans.Dispose();
                foreach (DbCommand cmd in cmds)
                {
                    cmd.Dispose();
                }
                conn.Close();
                conn.Dispose();
                if (excp != null)
                    if (args.Throw)
                        throw excp;

}
            //Return totalAffectedRows?
        }
    }

public delegate void ExceptionFailSafeEventHandler&lt;T&gt;(object sender, ExceptionFailSafeEventArgs&lt;T&gt; e) where T : System.Exception;
    public class ExceptionFailSafeEventArgs&lt;T&gt; : System.EventArgs where T : System.Exception
    {

private T _data;
        private bool _throw = true;

public bool Throw
        {
            get { return _throw; }
            set { _throw = value; }
        }

public ExceptionFailSafeEventArgs()
            : this(null) { }
        public ExceptionFailSafeEventArgs(T ex)
            : base()
        { _data = ex; }

public T Exception
        { 
            get { return _data; }
            set { _data = value; }
        }
    }
    [Serializable()]
    public class BatchCommandFailedException : DbException
    {
        public BatchCommandFailedException(string message)
            : base(&quot;Command execution has failed.  Reason:  &quot; + message){ }
        public BatchCommandFailedException(string message, DbCommand cmd)
            : base(string.Format(&quot;Command execution for command '{0}' has failed.  Reason:  &quot; + message, cmd.CommandText)){ }
        public BatchCommandFailedException(DbCommand cmd)
            : base(string.Format(&quot;Command execution for command '{0}' has failed.&quot;, cmd.CommandText)){ }
        public BatchCommandFailedException(DbCommand cmd, int ordinal)
            : base(string.Format(&quot;Command execution for command {0}:  '{1}' has failed.&quot;, ordinal, cmd.CommandText)){ }
        public BatchCommandFailedException(DbCommand cmd, int ordinal, Exception inner)
            : base(string.Format(&quot;Command execution for command {0}:  '{1}' has failed.  Reason:  {2}&quot;, ordinal, cmd.CommandText, inner.Message), inner){ }
        public BatchCommandFailedException(string message, Exception inner)
            : base(message, inner){ }
        public BatchCommandFailedException(System.Runtime.Serialization.SerializationInfo info, System.Runtime.Serialization.StreamingContext context)
            : base(info, context){ }
    }</pre>
<a href="http://www.refactormycode.com/codes/96-executecommandbatch" style="color:#fff" title="As seen on RefactorMyCode.com"><img alt="Small_logo" src="http://www.refactormycode.com/images/small_logo.gif" style="border:0" /></a>

Refactor :my => 'code'

Recent

Popular

Pastable version of ExecuteCommandBatch