//加入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); }