我正在使用EF6查询后端数据库。用户可以自定义临时表,并从临时表中查询数据。我正在使用
DataTable result = context.Query(queryStatement);
得到结果,它一直工作正常。
现在,在大量的其他sqlcommand中需要查询,并且需要事务。所以我有
public static DataTable GetData()
{
using (MyDbContext context = new MyDbContext())
using (DbContextTransaction tran = context.Database.BeginTransaction())
{
try
{
int rowAffected = context.Database.ExecuteSqlCommand(
"UPDATE [MyDb].dbo.[TableLocks] SET RefCount = RefCount + 1 WHERE TableName = 'TESTTABLE1'");
if (rowAffected != 1)
throw new Exception("Cannot find 'TestTable1'");
//The following line will raise an exception
DataTable result = context.Query("SELECT TOP 100 * FROM [MyDb].dbo.[TestTable1]");
//This line will work if I change it to
//context.Database.ExecuteSqlCommand("SELECT TOP 100 * FROM [MyDb].dbo.[TestTable1]");
//but I don't know how to get the result out of it.
context.Database.ExecuteSqlCommand(
"UPDATE [MyDb].dbo.[TableLocks] SET RefCount = RefCount - 1 WHERE TableName = 'TestTable1'");
tran.Commit();
return result;
}
catch (Exception ex)
{
tran.Rollback();
throw (ex);
}
}
}
但这会在执行上下文时引发异常。查询
ExecuteReader requires the command to have a transaction when the connection
assigned to the command is in a pending local transaction. The Transaction
property of the command has not been initialized.
当我读到这篇文章时:https://learn.microsoft.com/en-us/ef/ef6/saving/transactions它说:
实体框架不会将查询包装在事务中。
是导致此问题的原因吗?
如何在交易中使用context.Query()
?
我还可以使用什么?
我尝试了所有其他方法,但没有一种有效 - 因为无法事先预测返回数据类型。
我刚刚意识到,查询方法是在MyDbContext中定义的!
public DataTable Query(string sqlQuery)
{
DbProviderFactory dbFactory = DbProviderFactories.GetFactory(Database.Connection);
using (var cmd = dbFactory.CreateCommand())
{
cmd.Connection = Database.Connection;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlQuery;
using (DbDataAdapter adapter = dbFactory.CreateDataAdapter())
{
adapter.SelectCommand = cmd;
DataTable dt = new DataTable();
adapter.Fill(dt);
return dt;
}
}
}
可能是你错过了这一部分 -
您可以自由地直接在 SqlConnection 本身,或在 DbContext 上。所有这些操作都是 在一个事务中执行。您负责 提交或回滚事务以及调用 Dispose(( 在其上,以及用于关闭和处置数据库连接
然后这个代码库——
using (var conn = new SqlConnection("..."))
{
conn.Open();
using (var sqlTxn =
conn.BeginTransaction(System.Data.IsolationLevel.Snapshot))
{
try
{
var sqlCommand = new SqlCommand();
sqlCommand.Connection = conn;
sqlCommand.Transaction = sqlTxn;
sqlCommand.CommandText =
@"UPDATE Blogs SET Rating = 5" +
" WHERE Name LIKE '%Entity Framework%'";
sqlCommand.ExecuteNonQuery();
using (var context =
new BloggingContext(conn, contextOwnsConnection: false))
{
context.Database.UseTransaction(sqlTxn);
var query = context.Posts.Where(p => p.Blog.Rating >= 5);
foreach (var post in query)
{
post.Title += "[Cool Blog]";
}
context.SaveChanges();
}
sqlTxn.Commit();
}
catch (Exception)
{
sqlTxn.Rollback();
}
}
}
尤其是这个——
context.Database.UseTransaction(sqlTxn);
对不起,如上所述,我以为 Query 方法来自 EF,但我检查了代码,发现它实际上是由另一个开发人员编码的,在类 MyDbContext 中定义。由于此类是由 EF 生成的,因此我认为有人从未添加过方法。
是的
public DataTable Query(string sqlQuery)
{
DbProviderFactory dbFactory = DbProviderFactories.GetFactory(Database.Connection);
using (var cmd = dbFactory.CreateCommand())
{
cmd.Connection = Database.Connection;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlQuery;
//And I added this line, then problem solved.
if (Database.CurrentTransaction != null)
cmd.Transaction = Database.CurrentTransaction.UnderlyingTransaction;
using (DbDataAdapter adapter = dbFactory.CreateDataAdapter())
{
adapter.SelectCommand = cmd;
DataTable dt = new DataTable();
adapter.Fill(dt);
return dt;
}
}
}