複数SQL更新構文を実行してデータベーストランザクションを実現

2014年1月18日

目的
複数SQL更新構文を実行して、データベーストランザクションを実現

サンプルコード：
using System;
using System.Data.SqlClient;

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

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

///

/// 複数SQL構文を実行する。
///

/// SQL文のハッシュテーブル（keyはsql文，valueこの文のSqlParameter[]） private static Result 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()
{
Success = false, Message = ex2.Message, StackTrace = ex2.StackTrace
};
}
return new Result()
{
Success = false, Message = ex.Message, StackTrace = ex.StackTrace
};
}
finally
{
// Attempt to roll back the transaction.
try
{
connection.Close();
}
catch (Exception ex)
{
}
}
return new Result()
{
Success = true, data = result
};
}
}

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