WCF中的程式事務和資料庫事務轉換

    服務定義如下:

    [ServiceContract(SessionMode = SessionMode.Required)]
    public interface IComplexService
    {
        [OperationContract]
        [TransactionFlow(TransactionFlowOption.Mandatory)]
        void ExecuteNoneQuery1(string sql);
        [OperationContract]
        [TransactionFlow(TransactionFlowOption.Mandatory)]
        void ExecuteNoneQuery2(string sql);
        [OperationContract]
        [TransactionFlow(TransactionFlowOption.Mandatory)]
        void ExecuteNoneQuery3(string sql);
    }

    服務實現如下:

    [ServiceBehavior(TransactionTimeout = "00:00:45",TransactionIsolationLevel = System.Transactions.IsolationLevel.Serializable)]
    public class ComplexServiceImplement : IComplexService
    {
        private static void ExecuteNoneQuery(string sql)
        {
            string sqlConnectionString = "Password=sa;User ID=sa;Initial Catalog=ipedf;Data Source=.";
            string oraConnectionString = "User ID=sample;Password=sample;Data Source=insapp";
            using (OracleConnection oraConnection = new OracleConnection(oraConnectionString))
            {
                try
                {
                    oraConnection.Open();
                    OracleCommand cmd = oraConnection.CreateCommand();
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    string Debug = e.Message;
                }
                finally
                {
                    oraConnection.Close();
                }
            }
            using (SqlConnection sqlConnection = new SqlConnection(sqlConnectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = sqlConnection.CreateCommand();
                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    string Debug = e.Message;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }
        [OperationBehavior(
        TransactionScopeRequired = true, TransactionAutoComplete = true)]
        public void ExecuteNoneQuery1(string sql)
        {
            ExecuteNoneQuery(sql);
            //OperationContext.Current.SetTransactionComplete();
        }
        [OperationBehavior(
        TransactionScopeRequired = true, TransactionAutoComplete = true)]
        public void ExecuteNoneQuery2(string sql)
        {
            ExecuteNoneQuery(sql);
            //OperationContext.Current.SetTransactionComplete();
        }
        [OperationBehavior(
        TransactionScopeRequired = true, TransactionAutoComplete = true)]
        public void ExecuteNoneQuery3(string sql)
        {
            ExecuteNoneQuery(sql);
            //OperationContext.Current.SetTransactionComplete();
        }

        服務端配置如下:

       <system.serviceModel>
       <!--提供的服務-->
       <services>
       <service name="Wf.Complex.Service.ComplexServiceImplement" behaviorConfiguration="myServiceBehavior">
       <endpoint address="" binding="wsHttpBinding" bindingConfiguration="transactionalWsatHttpBinding" contract="Wf.Complex.Service.IComplexService"/>
       <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange"/>
       </service>
       </services>
       <behaviors>
       <serviceBehaviors>
       <behavior name="myServiceBehavior">
       <serviceMetadata httpGetEnabled="True"/>
       <serviceDebug includeExceptionDetailInFaults="False"/>
       </behavior>
       </serviceBehaviors>
       </behaviors>
       <bindings>
       <wsHttpBinding>
       <binding name="transactionalWsatHttpBinding" transactionFlow="true"/>

       </wsHttpBinding>
       </bindings>
       </system.serviceModel>

        用戶端代碼如下:

        IComplexService complex = new ComplexServiceClient();
        try
        {
                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
                {
                    string sql = "delete from student";
                    complex.ExecuteNoneQuery1(sql);
                    sql = "insert into student(id,name) values(1,'yeyang_1')";
                    complex.ExecuteNoneQuery2(sql);
                    sql = "insert into student(id,name) values(2,'yeyang_2')";
                    complex.ExecuteNoneQuery3(sql);
                    scope.Complete();
                }
                Console.WriteLine("succeed");
        }
        catch (Exception expt)
        {
                Console.WriteLine(expt.Message);
        }
        finally
        {
                (complex as ComplexServiceClient).Close();
        }   

        運行結果:

        服務端的ExecuteNoneQuery方法中using (OracleConnection oraConnection = new OracleConnection(oraConnectionString))代碼塊發生異常,異常內容如下:

        "無法載入 DLL“oramts.dll”: 找不到指定的模組。 (異常來自 HRESULT:0x8007007E)。"

        結論:

        對於資料庫事務,可以通過程式事務變形的方式來實現,但必須擷取對應資料庫產品的支援,sqlserver產品預設支援,oracle資料庫產品需要考慮本風險,及時規避.