//加入Transaction 並指定回傳的資料(預設影響個數)
private static int QueryWithTransaction(SqlConnection connection, SqlCommand command, Dictionary <string, object> sqlParmDic, TransactionResultType resultType = TransactionResultType.EffectNum)
{
int result = 0;
command.Connection = connection;
foreach (KeyValuePair <string, object> item in sqlParmDic)
{
command.Parameters.AddWithValue(item.Key, item.Value ?? DBNull.Value);
}
SqlParameter pmtLogId = new SqlParameter("@LogId", SqlDbType.Int);
if (resultType == TransactionResultType.EffectId)
{
command.CommandText += " SET @LogId = SCOPE_IDENTITY() ";
pmtLogId.Direction = ParameterDirection.Output;
command.Parameters.Add(pmtLogId);
}
connection.Open();
SqlTransaction tran = connection.BeginTransaction();
command.Transaction = tran;
try
{
switch (resultType)
{
case TransactionResultType.EffectNum:
result = command.ExecuteNonQuery();
break;
case TransactionResultType.EffectId:
command.ExecuteScalar();
result = (int)pmtLogId.Value;
break;
}
tran.Commit();
}
catch (SqlException sqlException)
{
tran.Rollback();
if (sqlException.Number == 2627)
{
result = -1; // 違反 %ls 條件約束 '%.ls'。無法在物件 '%.ls' 中插入重複的索引鍵。
}
}
catch (Exception e)
{
tran.Rollback();
}
finally
{
connection.Close();
}
return(result);
}
public static int Execute(string sqlStr, Dictionary <string, object> sqlParmDic = null, TransactionResultType resultType = TransactionResultType.EffectNum)
{
int id = -1;
using (SqlConnection conn = new SqlConnection(DBConnectString))
{
SqlCommand sql = new SqlCommand(sqlStr);
id = QueryWithTransaction(conn, sql, sqlParmDic, resultType);
}
return(id);
}
