1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116

    static class SqlExec
    {
        public static void ExecCommandBatch(System.Data.Common.DbConnection conn, System.Data.IsolationLevel isolation, ExceptionFailSafeEventHandler<System.Exception> failSafe, params System.Data.Common.DbCommand[] cmds)
        {
            if ((conn == null))
                throw new System.ArgumentNullException("conn");
            if ((cmds.Length < 2))
                throw new System.ArgumentException("Must supply more than one Command");
            // TODO: validate isolation?
            bool success = false;
            BatchCommandFailedException excp = null;
            ExceptionFailSafeEventArgs<System.Exception> args = new ExceptionFailSafeEventArgs<System.Exception>();
            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 <= length; i++)
            {
                cmds[i].Connection = conn;
                cmds[i].Transaction = trans;
            }
            try
            {
                for (i = 0; i <= 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<T>(object sender, ExceptionFailSafeEventArgs<T> e) where T : System.Exception;
    public class ExceptionFailSafeEventArgs<T> : 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("Command execution has failed.  Reason:  " + message){ }
        public BatchCommandFailedException(string message, DbCommand cmd)
            : base(string.Format("Command execution for command '{0}' has failed.  Reason:  " + message, cmd.CommandText)){ }
        public BatchCommandFailedException(DbCommand cmd)
            : base(string.Format("Command execution for command '{0}' has failed.", cmd.CommandText)){ }
        public BatchCommandFailedException(DbCommand cmd, int ordinal)
            : base(string.Format("Command execution for command {0}:  '{1}' has failed.", ordinal, cmd.CommandText)){ }
        public BatchCommandFailedException(DbCommand cmd, int ordinal, Exception inner)
            : base(string.Format("Command execution for command {0}:  '{1}' has failed.  Reason:  {2}", 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){ }
    }