namespace ExecuteSqlTran
{
    class Program
    {
        class Result<T>
        {
            public T data;
            public string Message;
            public bool Success;
            public string StackTrace;
        }

        struct ExecuteableUnit
        {
            public string SQL;
            public SqlParameter[] param;
        }

        /// <summary>
        /// 執行多條SQL語句，實現數據庫事務。
        /// </summary>
        /// <param name="SQLStringList">SQL語句的哈希表（key為sql語句，value是該語句的SqlParameter[]）</param>
        private static Result<int> ExecuteSqlTransaction(params ExecuteableUnit[] executeableUnits)
        {
            using (SqlConnection connection = new SqlConnection(""))
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                SqlTransaction transaction = connection.BeginTransaction();
                command.Connection = connection;
                command.Transaction = transaction;
                int result = 0;
                try
                {
                    foreach(ExecuteableUnit exeUnit in executeableUnits)
                    {
                        command.CommandText = exeUnit.SQL;
                        if(exeUnit.param.GetLength(1) > 0)
                        {
                            foreach(SqlParameter p in exeUnit.param)
                                command.Parameters.Add(p);
                        }
                        result += command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    // Attempt to roll back the transaction.
                    try
                    {
                        transaction.Rollback();
                    }
                    catch (Exception ex2)
                    {
                        return new Result<int>()
                        {
                            Success = false, Message = ex2.Message, StackTrace = ex2.StackTrace
                        };
                    }
                    return new Result<int>()
                    {
                        Success = false, Message = ex.Message, StackTrace = ex.StackTrace
                    };
                }
                finally
                {
                    // Attempt to roll back the transaction.
                    try
                    {
                  connection.Close();
                    }
                    catch (Exception ex)
                    {
                    }
                }
                return new Result<int>()
                {
                    Success = true, data = result
                };
            }
        }

        public static void Main(string[] args)
        {
        }
    }
}