I have an application for creating windows with .NET 4 and Entity Framework for the data layer. I need one method with a transaction, but, making simple tests, I could not get it to work.
In BLL:
public int Insert(List<Estrutura> lista)
{
using (TransactionScope scope = new TransactionScope())
{
id = this._dal.Insert(lista);
}
}
In DAL:
public int Insert(List<Estrutura> lista)
{
using (Entities ctx = new Entities (ConnectionType.Custom))
{
ctx.AddToEstrutura(lista);
ctx.SaveChanges();
}
}
"The primary provider has abandoned Open."
Does anyone have any ideas?
SOLVING THE PROBLEM - MY SOLUTION
I decided that my problem was making some changes. In one of my DALs, I use Bulk Insert and other Entity. The problematic transaction was due to the fact that the main part of the transaction (sql transaction) does not understand the scope of transactions. Therefore, I split the Entity in the DAL and used the sql transaction in its launch of some trivial one. ExecuteScalar ();
I believe that this is not the most elegant way to do this, but solved my problem.
Here is the code of my 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();
updateQuery.Append("UPDATE... ");
command.CommandText = updateQuery.ToString();
command.Parameters.Clear();
command.Parameters.AddWithValue("@SOME_PARAM", DateTime.Now);
command.ExecuteNonQuery();
transaction.Commit();
}
}
}
thanks for the help