Question

I have a windows form application with .NET 4 and Entity Framework for data layer I need one method with transaction, but making simple tests I couldn t make it work

在 BLL 中 :

public int Insert(List<Estrutura> lista)
{
    using (TransactionScope scope = new TransactionScope())
    {
            id = this._dal.Insert(lista);
    }
}

在 DAL 中 :

public int Insert(List<Estrutura> lista)
{
   using (Entities ctx = new Entities (ConnectionType.Custom))
   {
     ctx.AddToEstrutura(lista);
     ctx.SaveChanges(); //<---exception is thrown here
   }
}

"幕后供应商在开放时失败了"

有人有什么想法吗?

问题解决 - 我的溶解

I solved my problem doing some changes. In one of my DAL I use a Bulk Insert and others Entity. The problem transaction was occurring by the fact that the bulk of the transaction (transaction sql) do not understand a transaction scope So I separated the Entity in DAL and used the sql transaction in its running some trivial. ExecuteScalar ();

我认为这不是最优雅的方法这样做,但解决了我的问题交易。

这是我DAL的代码

   using (SqlConnection sourceConnection = new SqlConnection(Utils.ConnectionString()))
   {
        sourceConnection.Open();
        using (SqlTransaction transaction = sourceConnection.BeginTransaction())
        {
            StringBuilder query = new StringBuilder();
            query.Append("INSERT INTO...");
            SqlCommand command = new SqlCommand(query.ToString(), sourceConnection, transaction);
            using (SqlBulkCopy bulk = new SqlBulkCopy(sourceConnection, SqlBulkCopyOptions.KeepNulls, transaction))
            {                           
                bulk.BulkCopyTimeout = int.MaxValue;
                bulk.DestinationTableName = "TABLE_NAME";
                bulk.WriteToServer(myDataTable);

                StringBuilder updateQuery = new StringBuilder();
                //another simple insert or update can be performed here
                updateQuery.Append("UPDATE... ");
                command.CommandText = updateQuery.ToString();
                command.Parameters.Clear();
                command.Parameters.AddWithValue("@SOME_PARAM", DateTime.Now);
                command.ExecuteNonQuery();
                transaction.Commit();
            }
        }
    }

感谢您的帮助

Answer 1

根据全能的谷歌,EF似乎将打开/关闭每个呼叫到数据库的连接。因为EF正在这样做, 它将把交易视为使用多个连接( 使用分布式交易 ) 。绕过它的方式是打开并手工关闭连接。

此处是"http://social.msdn.microsoft.com/forums/en-US/adodotnetentitity framework/thread/3aa3942d-1b56-4030-8ea6-8d9734da4168/" rel=“nofollow” 分配交易问题的信息。

这里如何 < a href=>"""http://msdn.microsoft.com/en-us/library/bb738698.aspx" rel="nofollow" 手动打开并关闭连接。

小型代码样本:

public int Insert(List<Estrutura> lista)
{
    using (TransactionScope scope = new TransactionScope())
    {
        using (Entities ctx = new Entities (ConnectionType.Custom))
        {
            ctx.Connection.Open()

            id = this._dal.Insert(ctx, lista);
        }
    }
}

public int Insert(Entities ctx, List<Estrutura> lista)
{
     ctx.AddToEstrutura(lista);
     ctx.SaveChanges();
}

Answer 2

Instead of employing TransactionScope, it is better to employ UnitOfWork pattern while working with entity framework. please refer to: unit of work pattern

以及;

< a href=>"http://msdn.microsoft.com/en-us/magazine/dd882510.aspx" rel=“nofollow” 工作单位和持久性无知

友情链接