sqlserver跨数据库事物同步(转vinsonLu)
/// <summary> /// 多数据库服务器事务提交 /// </summary> /// <param name="sqlStrings">key为connName,value为Sql语句</param> /// <returns></returns> public bool ExecuteMultiTran(List<string[]> sqlStrings) { bool reval = true; SqlCommand cmd = new SqlCommand(); SqlTransaction tran; SqlConnection conn; //事务对象名,事务对象的集合 Dictionary<string, SqlTransaction> tranResult = new Dictionary<string, SqlTransaction>(); //conn对象名,对象 Dictionary<string, SqlConnection> connResult = new Dictionary<string, SqlConnection>(); //当前是否执行成功 bool isSuccess = true; // List<string> keys = new List<string>(); //通过connName进行循环执行事务 foreach (string[] sqls in sqlStrings) { string keyName = sqls[0]; //如果keys中已经存在当前 keyname,说明改conn的已经执行完毕,跳到下一keyname执行 if (!keys.Contains(keyName)) { keys.Add(keyName); //提交当前conn的事务,如果失败,标记当前事务失败 try { conn = CreateConnection(keyName); conn.Open(); cmd.Connection = conn; tran = conn.BeginTransaction(); cmd.Transaction = tran; //记录当前事务 tranResult.Add(keyName, tran); //记录当前conn connResult.Add(keyName, conn); //读取当前conn的sql,执行 foreach (string[] sql in sqlStrings) { if (sql[0] == keyName) { cmd.CommandText = sql[1]; cmd.ExecuteNonQuery(); } } } catch(Exception ex) { isSuccess = false; } if (!isSuccess) { break; } } } //如果当前事务失败,把执行过的所有事务对象rollBack if (!isSuccess) { foreach (SqlTransaction sqlTran in tranResult.Values) { sqlTran.Rollback(); } reval = false; } else { foreach (SqlTransaction sqlTran in tranResult.Values) { sqlTran.Commit(); } } //关闭conn foreach (SqlConnection value in connResult.Values) { if (value.State != ConnectionState.Closed) { value.Close(); } } return reval; } public SqlConnection CreateConnection(string keyName) { SqlConnection sqlconn = new SqlConnection(ConfigurationManager.AppSettings[keyName].ToString()); return sqlconn; }
调用
//1.扣减玩家拍币数 //2.增加玩家保险柜金豆数 //3.插入拍币兑换记录 private bool procData(int UserID, int pats, int ConvertRate, string ClientIP) { List<string[]> sqls = new List<string[]>(); //1.扣减玩家拍币数 string sql = "update AccountsInfo set UserPat=UserPat-" + pats + " where UserID=" + UserID; sqls.Add(new string[] { "DBAccounts", sql }); //2.增加玩家保险柜金豆数 sql = "update GameScoreInfo set InsureScore=InsureScore+" + pats * ConvertRate + " where UserID=" + UserID; sqls.Add(new string[] { "DBTreasure", sql }); //3.插入拍币兑换记录 sql = "insert into RecordConvertUserpat (RecordID,UserID,CurInsureScore,CurUserPat,ConvertUserPat,ConvertRate,IsGamePlaza,ClientIP,CollectDate) values("; sql += ((int)gData.SelectValue("select max(RecordID) from RecordConvertUserpat", "DBRecord", 0) + 1) + ","; sql += UserID + ","; sql += gData.SelectValue("select InsureScore from GameScoreInfo where UserID=" + UserID, "DBTreasure", 0) + ","; sql += gData.SelectValue("select UserPat from AccountsInfo where UserId=" + UserID, "DBAccounts", 0) + ","; sql += pats + ","; sql += ConvertRate + ","; sql += "1,"; sql += "'" + ClientIP + "',"; sql += "'" + System.DateTime.Now.ToString() + "'"; sql += ")"; sqls.Add(new string[] { "DBRecord", sql }); return gData.ExecuteMultiTran(sqls); }