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",";
        sql += pats + ",";
        sql += ConvertRate + ",";
        sql += "1,";
        sql += "‘" + ClientIP + "‘,";
        sql += "‘" + System.DateTime.Now.ToString() + "‘";
        sql += ")";
        sqls.Add(new string[] { "DBRecord",sql });

        return gData.ExecuteMultiTran(sqls);
    }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!