【经验分享】Sqlserver和Oracle混合事务

直接使用:

 

添加基本信息#region 添加基本信息
/**//// <summary>
/// 添加基本信息
/// </summary>
/// <param name="model">基本信息(Sqlserver)</param>
/// <param name="modelCR">那边记录的对象(Oracle)</param>
/// <returns>添加结果字符串</returns>
public string AddBaseInfoNew(Model.PPC.PPCMan model, Temp.Model.PPC.CallRedirect modelCR)
{
    事务头#region 事务头
    string sqlConn = PubConstant.GetConnectionString("ConnectionString");//SQLSERVER2005的连接字符串
    SqlConnection aSqlConnection = new SqlConnection(sqlConn);
    SqlTransaction aSqlTransaction = null;
    aSqlConnection.Open();//打开SQLSERVER2005数据库连接
    aSqlTransaction = aSqlConnection.BeginTransaction();//这里可以设置隔离级别
    #endregion

    try
    {
        添加基本信息#region 添加基本信息
        string manGuid = Guid.NewGuid().ToString().Replace("-", "");
        SqlCommand SqlCommand1 = aSqlConnection.CreateCommand();
        SqlCommand1.Transaction = aSqlTransaction;
        SqlCommand1.CommandText = @"insert into PPC_Man ([Name],[Description],[GroupID],[SupplierID]) 
            values(@Name,@Description,@GroupID,@SupplierID);set @ReturnValue= @@IDENTITY";

        SqlParameter[] parameters1 = new SqlParameter[] { 
        new SqlParameter("@Name",SqlDbType.VarChar),
        new SqlParameter("@Description",SqlDbType.VarChar),
        new SqlParameter("@GroupID",SqlDbType.Int),
        new SqlParameter("@SupplierID",SqlDbType.Int)};
        parameters1[0].Value = model.Name;
        parameters1[1].Value = model.Description;
        parameters1[2].Value = model.GroupID;
        parameters1[3].Value = model.SupplierID;

        SqlCommand1.Parameters.AddRange(parameters1);
        SqlCommand1.CommandTimeout = 150;
        int count1 = SqlCommand1.ExecuteNonQuery();

        //如果没有写入数据，事务回滚
        if (count1 == 0)
        {
            aSqlTransaction.Rollback();
            return "写入信息失败！";
        }
        #endregion

        添加辅助信息#region 添加辅助信息
        SqlCommand SqlCommand3 = aSqlConnection.CreateCommand();
        SqlCommand3.Transaction = aSqlTransaction;
        SqlCommand3.CommandText = @"insert into PPC_Mans (ManGuid,BizCode,SubCode,ManID,Status,Type) values (@ManGuid,@BizCode,@SubCode,@ManID," + status + ",0)";

        SqlParameter[] parameters3 = new SqlParameter[]{
                    new SqlParameter("@ManID",SqlDbType.Int),
                    new SqlParameter("@ManGuid",SqlDbType.VarChar),
                    new SqlParameter("@BizCode",SqlDbType.VarChar,50),
                    new SqlParameter("@SubCode",SqlDbType.VarChar,50)};
        parameters3[0].Value = parameters1[6].Value;//ManID由第一步添加基本信息的时候获得的
        parameters3[1].Value = manGuid;
        parameters3[2].Value = bizCode;
        parameters3[3].Value = subCode;

        SqlCommand3.Parameters.AddRange(parameters3);
        SqlCommand3.CommandTimeout = 150;
        int count3 = SqlCommand3.ExecuteNonQuery();
        //如果写入数据，事务回滚
        if (count3 == 0)
        {
            aSqlTransaction.Rollback();
            return "写入失败！";
        }
        #endregion

        写入#region 写入
        if (modelCR == null)
        {
            aSqlTransaction.Rollback();
            return "写入失败！";
        }

        try
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = connection;
                OracleTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    foreach (string code in list400)
                    {
                        string[] ss = code.Split(',');
                        if (ss.Length == 2)
                        {
                            string bizNum = ss[0];
                            string subNum = ss[1];
                            modelCR.SUBNUM = subNum;
                            modelCR.BUSINESSNUM = bizNum;

                            StringBuilder strSql = new StringBuilder();
                            strSql.Append("insert into CALLREDIRECT(");
                            strSql.Append("SUBNUM,PHONUM,TTSWORD,STATUS");
                            strSql.Append(")");
                            strSql.Append(" values (");
                            strSql.Append("'" + modelCR.SUBNUM + "',");
                            strSql.Append("'" + modelCR.PHONUM + "',");
                            strSql.Append("'" + modelCR.TTSWORD + "',");
                            strSql.Append("" + modelCR.STATUS + "");
                            strSql.Append(")");
                            cmd.CommandText = strSql.ToString();
                            int count = cmd.ExecuteNonQuery();
                            if (count == 0)
                            {
                                tx.Rollback();
                                aSqlTransaction.Rollback();
                                return "写入失败！";
                            }
                        }
                    }
                    tx.Commit();
                }
                catch
                {
                    tx.Rollback();
                    aSqlTransaction.Rollback();
                    return "写入失败！";
                }
                finally
                {
                    connection.Close();
                }
            }
        }
        catch
        {
            aSqlTransaction.Rollback();
            return "连接oracle失败！";
        }
        #endregion

        事务尾#region 事务尾
        aSqlTransaction.Commit();//提交事务
        return "成功";
        #endregion
    }
    catch (Exception ex)
    {
        aSqlTransaction.Rollback();//发生异常回滚事务
        return "发生异常:" + ex.Message + "\r\n" + ex.StackTrace;
    }
    finally
    {
        aSqlConnection.Close();//关闭SQLSERVER2005数据库连接
    }
}
#endregion

 

对事务操作进行封装

优点：将不同的数据源进行事务级的操作

缺点：该方法是用.net编写，代码适用于.net环境

思路：将不同数据源的操作，封闭成不同的命令集。在这里用oracle和sqlserver为例。然后用事务的方法处理。

 

详见代码：

 

命令集参数设置：

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

namespace DBUtility
{
    public enum EffentNextType
    {
        /**//// <summary>
        /// 对其他语句无任何影响 
        /// </summary>
        None,
        /**//// <summary>
        /// 当前语句必须为"select count(1) from .."格式，如果存在则继续执行，不存在回滚事务
        /// </summary>
        WhenHaveContine,
        /**//// <summary>
        /// 当前语句必须为"select count(1) from .."格式，如果不存在则继续执行，存在回滚事务
        /// </summary>
        WhenNoHaveContine,
        /**//// <summary>
        /// 当前语句影响到的行数必须大于0，否则回滚事务
        /// </summary>
        ExcuteEffectRows,
        /**//// <summary>
        /// 引发事件-当前语句必须为"select count(1) from .."格式，如果不存在则继续执行，存在回滚事务
        /// </summary>
        SolicitationEvent
    }
    public class CommandInfo
    {
        public object ShareObject = null;
        public object OriginalData = null;
        event EventHandler _solicitationEvent;
        public event EventHandler SolicitationEvent
        {
            add
            {
                _solicitationEvent += value;
            }
            remove
            {
                _solicitationEvent -= value;
            }
        }
        public void OnSolicitationEvent()
        {
            if (_solicitationEvent != null)
            {
                _solicitationEvent(this, new EventArgs());
            }
        }
        public string CommandText;
        public System.Data.Common.DbParameter[] Parameters;
        public EffentNextType EffentNextType = EffentNextType.None;
        public CommandInfo()
        {

        }
        public CommandInfo(string sqlText, SqlParameter[] para)
        {
            this.CommandText = sqlText;
            this.Parameters = para;
        }
        public CommandInfo(string sqlText, SqlParameter[] para, EffentNextType type)
        {
            this.CommandText = sqlText;
            this.Parameters = para;
            this.EffentNextType = type;
        }
    }
}

 

 调用函数：

using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Common;
using System.Collections.Generic;

namespace DBUtility
{
    public class DbHelperSQL
    {
        public static string connectionString = "server=192.168.1.1;database=sqlserverdb;uid=sqlserverdb;pwd=sqlserverdb";
        public static string oraConnectionString = "Password=oracledb;User ID=oracledb;Data Source=oracledb";
        
        /**//// <summary>
        /// 执行Sql和Oracle滴混合事务
        /// </summary>
        /// <param name="list">SQL命令行列表</param>
        /// <param name="oracleCmdSqlList">Oracle命令行列表</param>
        /// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>
        public static int ExecuteSqlTran(List<CommandInfo> list, List<CommandInfo> oracleCmdSqlList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    foreach (CommandInfo myDE in list)
                    {
                        string cmdText = myDE.CommandText;
                        SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
                        PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
                        if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
                        {
                            if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
                            {
                                tx.Rollback();
                                throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式");
                                //return 0;
                            }

                            object obj = cmd.ExecuteScalar();
                            bool isHave = false;
                            if (obj == null && obj == DBNull.Value)
                            {
                                isHave = false;
                            }
                            isHave = Convert.ToInt32(obj) > 0;
                            if (isHave)
                            {
                                //引发事件
                                myDE.OnSolicitationEvent();
                            }
                        }
                        if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
                        {
                            if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
                            {
                                tx.Rollback();
                                throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
                                //return 0;
                            }

                            object obj = cmd.ExecuteScalar();
                            bool isHave = false;
                            if (obj == null && obj == DBNull.Value)
                            {
                                isHave = false;
                            }
                            isHave = Convert.ToInt32(obj) > 0;

                            if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
                            {
                                tx.Rollback();
                                throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
                                //return 0;
                            }
                            if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
                            {
                                tx.Rollback();
                                throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
                                //return 0;
                            }
                            continue;
                        }
                        int val = cmd.ExecuteNonQuery();
                        if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
                        {
                            tx.Rollback();
                            throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
                            //return 0;
                        }
                        cmd.Parameters.Clear();
                    }

                    bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
                    if (!res)
                    {
                        tx.Rollback();
                        throw new Exception("Oracle执行失败");
                        // return -1;
                    }
                    tx.Commit();
                    return 1;
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    tx.Rollback();
                    throw e;
                }
                catch (Exception e)
                {
                    tx.Rollback();
                    throw e;
                }
            }
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

    }
}

oracle and sqlserver operator class from microsoft sqlhelper class.

 

 调用demo.

 

更新基本信息#region 更新基本信息
/**//// <summary>
/// 更新基本信息
/// </summary>
/// <param name="bindTel">绑定电话</param>
/// <param name="list400">号码集合</param>
/// <param name="TTSWORD">问候语</param>
public void UpdateActive(string manGuid, string bindTel)
{
    //获取oracle连接字符串
    string oraConnectionString = "Password=oracledb;User ID=oracledb;Data Source=oracledb";
   
    //实例化sqlserver命令集
    List<CommandInfo> list = new List<CommandInfo>();
    //实例化oracle命令集
    List<CommandInfo> oraList = new List<CommandInfo>();

    //为sqlserver命令集添加命令
    string upStr = "update PPC_Man set Tel1=@Tel1 where ManGuid=@ManGuid";
    SqlParameter[] para = new SqlParameter[] {
        new SqlParameter("@Tel1",SqlDbType.VarChar),
        new SqlParameter("@ManGuid",SqlDbType.VarChar)
    };
    para[0].Value = bindTel;
    para[1].Value = manGuid;
    CommandInfo cmd = new CommandInfo(upStr, para, EffentNextType.ExcuteEffectRows);
    list.Add(cmd);
   
    //为oracle命令集添加命令
    using (OracleConnection connection = new OracleConnection(connectionString))
    {
        connection.Open();
        OracleCommand oracmd = new OracleCommand();
        oracmd.Connection = connection;
        OracleTransaction tx = connection.BeginTransaction();
        oracmd.Transaction = tx;
        try
        {
            sql = "update CALLREDIRECT set PHONUM='" + phonum + "'  where  SUBNUM='" + subNum + "' and BUSINESSNUM='" + bizNum + "'";
            cmd = new CommandInfo(sql, null, EffentNextType.ExcuteEffectRows);
            oraList.Add(cmd);

            tx.Commit();
            DbHelperSQL.ExecuteSqlTran(list, oraList);
        }
        catch (Exception ex)
        {
            tx.Rollback();
            throw ex;
        }
        finally
        {
            connection.Close();
        }
    }
}
#endregion

 

------------------------------------------------------------------------------------------------------------------

感谢回贴的朋友,让我认识到这种方法的弊端.并且提供了新的思路.

 

用.NET Framework 2.0 中的Transactions类提供的机制来处理.

 

现将代码贴出如下:

 

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using System.Text;
using System.Transactions;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        //同一个数据库的事务操作
        using (TransactionScope tran = new TransactionScope(TransactionScopeOption.RequiresNew))
        {
            using (SqlConnection conn = new SqlConnection("server=192.168.1.10;database=aiweidb;uid=mmyeead;pwd="))
             {
                 conn.Open();
                 string strInert = "insert into Role(RoleName) values('test2') ";
                 string strUp = "insert into Role(RoleName) values('test3')";
                 SqlCommand cmd1 = new SqlCommand(strInert,conn);
                 cmd1.ExecuteNonQuery();
                 SqlCommand cmd2 = new SqlCommand(strUp,conn);
                 cmd2.ExecuteNonQuery();
              }
              tran.Complete();
        }

        //不同数据库的事务操作
        string strConnAiwei = "server=192.168.1.10;database=aiweidb;uid=mmyeead;pwd=";
        string strConnWebSite = "server=192.168.1.10;database=MmyeeAd;uid=mmyeead;pwd=";
        string strSqlAiwei = "insert into Role(RoleName) values('test1') ";
        string strSqlWebSite = "insert into Ad_Class1(ClassDesc) values('1') ";

        using (TransactionScope transScope = new TransactionScope())
        {
            using (SqlConnection connection1 = new
               SqlConnection(strConnAiwei))
            {
                // Opening connection1 automatically enlists it in the 
                // TransactionScope as a lightweight transaction.
                connection1.Open();
                SqlCommand cmd1 = new SqlCommand(strSqlAiwei, connection1);
                cmd1.ExecuteNonQuery();
                // Do work in the first connection.

                // Assumes conditional logic in place where the second
                // connection will only be opened as needed.
                using (SqlConnection connection2 = new
                    SqlConnection(strConnWebSite))
                {
                    // Open the second connection, which enlists the 
                    // second connection and promotes the transaction to
                    // a full distributed transaction. 
                    connection2.Open();
                    SqlCommand cmd2 = new SqlCommand(strSqlWebSite, connection2);
                    cmd2.ExecuteNonQuery();
                    // Do work in the second connection.
                }
            }
            //  The Complete method commits the transaction.
            transScope.Complete();
        }

    }
}

 

 在运行时,会出现DTC相关错误提示.通过以下配置方法来解决该问题.

 

1.打开命令提示，运行"net stop msdtc"，然后运行"net start msdtc"。
2.转至"组件服务管理工具"。
3.浏览至"启动管理工具"。
4.选择"组件服务"。
a.展开"组件服务"树，然后展开"我的电脑"。
b.右键单击"我的电脑"，然后选择"属性"。
C.在 MSDTC 选项卡中，确保选中了下列选项：

网络 DTC 访问
网络管理
网络事务
XA 事务
e.另外，"DTC 登录帐户"一定要设置为"NT Authority\NetworkService"。
5.单击"确定"。这样将会提示您"MS DTC 将会停止并重新启动。
所有的依赖服务将被停止。请按'是'继续"。单击"是"继续。
6.单击"确定"关闭"我的电脑"属性窗口。

 

 改天将sqlserver和oracle的混合案例代码测试后贴出.