获取EF提交操作的SQL语句
Entity Framework code-fist 的SaveChanges()方法一次性提交对实体的更改到数据库。类似于ADO.net 的
DataAdapter 对象提交 DataSet 的更新。 只不过，EF作为ORM ,实体是强类型的，这样linq也能做很好的支持。
最近，在做工作流的项目中，使用EF作为数据库访问层，需要在 EF提交实体的更新的时候，同时执行一段sql语句或者存储过程，而且要保证在同一个事务当中。
关于EF事务提交的问题，网上已经有方案，主要是通过 TransactionScope：
using (TransactionScope scope = new TransactionScope())
{
dbContext.Database.ExecuteSqlCommand(sql, param);
dbContext.SaveChanges();
scope.Complete();
}
甚至在TransactionScope包含的代码中可以 提交多个 DBContext,当然如果多个DBContext对于的是不同的数据库，这个事务肯定要上升到分布式事务 msdtc.
而且我们同时执行的sql语句，只能通过ExecuteSqlCommand 这条通道，如果不是的话，还是会上升到msdtc，可以参考 http://www.cnblogs.com/lovvver/archive/2012/06/10/2543762.html
由于，EF每次查询或者更新操作， 无论是ExecuteSqlCommand 还是SaveChanges，都是打开Connection，然后及时就关闭。所以，上面的事务是在多个连接 的事务。要命的是，在不支持多连接事务的数据库中，比如sql server 2005, 还是会上升为 msdtc。关于这个问题，请参考 http://www.digitallycreated.net/Blog/48/entity-framework-transactionscope-and-msdtc。
因为，在实际并不是分布式的场景中，要用到以上的方式提交事务，就不得不配置msdtc，msdtc服务浪费系统资源 和 不易配置 不讲，我们并没有实实在在做分布式操作，而且
可以的话，我们提交的各个操作都可以在一个连接内完成的。EF 没有提供方法让我们实现我们的功能，因此开始怀念ADO.net。
但是又不舍得，仅仅是因为操作提交的通道问题 ，就要舍弃 EF提供的强大的映射和查询功能吗 ？ 要知道这方面的确比 SQLinq ， L2S ， 强大很多。
如果我们能获取SaveChanges要提交的sql语句，然后我们就能为所欲为。查询的时候走的是传统的ef通道，提交的时候走的是ado.net 爱怎么样就怎么样，而EF参与提交的方式，只是提供sql语句. 查询和操作分离这种架构也符合CQRS。
前段时间，EF不是开源了吗http://www.cnblogs.com/shanyou/archive/2012/07/20/2600465.html ，嘿嘿 ，还有什么是我们做不到的呢？
首先从http://entityframework.codeplex.com/下载源代码，下载的版本需要用vs2012 打开，而且framework版本是4.5的。
不过庆幸的是，4.0的程序可以调用4.5编译的dll。
下载vs2012 rc ,打开解决方案。实际只需要 EntityFramework 和 EntityFramework.SqlServer 两个项目，其他的强制签名和nuget和其他不相关的项目可以移除。并且编译成功。
找到 ，DbContext 的类 ， 的SaveChanges方法，顺藤摸瓜，
DbContext -》 InternalContext -》ObjectContext-》EntityAdapter-》UpdateTranslator -》 DynamicUpdateCommand
终于找到 DynamicUpdateCommand 的方法 Execute ，里面看到了 DbCommand 如何被创建出来。
internal override long Execute(
Dictionary<int, object> identifierValues,
List<KeyValuePair<PropagatorResult, object>> generatedValues)
{
// Compile command
using (var command = CreateCommand(identifierValues))
{
//更新的command
var connection = Translator.Connection;
// configure command to use the connection and transaction for this session
command.Transaction = ((null == connection.CurrentTransaction)
? null
: connection.CurrentTransaction.StoreTransaction);
command.Connection = connection.StoreConnection;
if (Translator.CommandTimeout.HasValue)
{
command.CommandTimeout = Translator.CommandTimeout.Value;
}
// Execute the query
int rowsAffected;
if (_modificationCommandTree.HasReader)
{
Debug.WriteLine("查询的sql :" + command.CommandText);
// retrieve server gen results
rowsAffected = 0;
using (var reader = command.ExecuteReader(CommandBehavior.SequentialAccess))
{
if (reader.Read())
{
rowsAffected++;
var members = TypeHelpers.GetAllStructuralMembers(CurrentValues.StructuralType);
for (var ordinal = 0; ordinal < reader.FieldCount; ordinal++)
{
// column name of result corresponds to column name of table
var columnName = reader.GetName(ordinal);
var member = members[columnName];
object value;
if (Helper.IsSpatialType(member.TypeUsage)
&& !reader.IsDBNull(ordinal))
{
value = SpatialHelpers.GetSpatialValue(Translator.MetadataWorkspace, reader, member.TypeUsage, ordinal);
}
else
{
value = reader.GetValue(ordinal);
}
// retrieve result which includes the context for back-propagation
var columnOrdinal = members.IndexOf(member);
var result = CurrentValues.GetMemberValue(columnOrdinal);
// register for back-propagation
generatedValues.Add(new KeyValuePair<PropagatorResult, object>(result, value));
// register identifier if it exists
var identifier = result.Identifier;
if (PropagatorResult.NullIdentifier != identifier)
{
identifierValues.Add(identifier, value);
}
}
}
// Consume the current reader (and subsequent result sets) so that any errors
// executing the command can be intercepted
CommandHelper.ConsumeReader(reader);
}
}
else
{
Debug.WriteLine("更新的sql :" + command.CommandText);
// return 1;
rowsAffected = command.ExecuteNonQuery();
}
return rowsAffected;
}
}
rowsAffected = command.ExecuteNonQuery(); 这个代码正是真正执行的操作。
思路是我们对 DbCommand 阻止提交 ，并且记录到一个集合里面。因此我们新建一个类：
public class CommandItem
{
public string CommandText { get; set; }
public CommandType CommandType { get; set; }
public DbParameterCollection Parameters { get; set; }
}
并且组合到 ObjectContext 之中
public List<CommandItem> CommandItems { get; set; }
在DbContext就能访问到：
public List<CommandItem> CommandItems { get { return this.InternalContext.ObjectContext.CommandItems; } }
拷贝 DynamicUpdateCommand类里面的方法Excute方法，传入List<CommandItem> items 作为容器
，注释掉执行的代码，修改成一个新的方法：
internal override long AtawExecute(List<CommandItem> items, Dictionary<int, object> identifierValues, List<KeyValuePair<PropagatorResult, object>> generatedValues) { // Compile command using (var command = CreateCommand(identifierValues)) { //更新的command //var connection = Translator.Connection; //// configure command to use the connection and transaction for this session //command.Transaction = ((null == connection.CurrentTransaction) // ? null // : connection.CurrentTransaction.StoreTransaction); //command.Connection = connection.StoreConnection; //if (Translator.CommandTimeout.HasValue) //{ // command.CommandTimeout = Translator.CommandTimeout.Value; //} // Execute the query // int rowsAffected; if (_modificationCommandTree.HasReader) { Debug.WriteLine("查询的sql :" + command.CommandText); // retrieve server gen results //rowsAffected = 0; //using (var reader = command.ExecuteReader(CommandBehavior.SequentialAccess)) //{ // if (reader.Read()) // { // rowsAffected++; // var members = TypeHelpers.GetAllStructuralMembers(CurrentValues.StructuralType); // for (var ordinal = 0; ordinal < reader.FieldCount; ordinal++) // { // // column name of result corresponds to column name of table // var columnName = reader.GetName(ordinal); // var member = members[columnName]; // object value; // if (Helper.IsSpatialType(member.TypeUsage) // && !reader.IsDBNull(ordinal)) // { // value = SpatialHelpers.GetSpatialValue(Translator.MetadataWorkspace, reader, member.TypeUsage, ordinal); // } // else // { // value = reader.GetValue(ordinal); // } // // retrieve result which includes the context for back-propagation // var columnOrdinal = members.IndexOf(member); // var result = CurrentValues.GetMemberValue(columnOrdinal); // // register for back-propagation // generatedValues.Add(new KeyValuePair<PropagatorResult, object>(result, value)); // // register identifier if it exists // var identifier = result.Identifier; // if (PropagatorResult.NullIdentifier != identifier) // { // identifierValues.Add(identifier, value); // } // } // } // // Consume the current reader (and subsequent result sets) so that any errors // // executing the command can be intercepted // CommandHelper.ConsumeReader(reader); //} } else { Debug.WriteLine("更新的sql :" + command.CommandText); items.Add(new CommandItem() { CommandText = command.CommandText , CommandType = command.CommandType , Parameters = command.Parameters }); //return 1; // rowsAffected = command.ExecuteNonQuery(); } return 1; } //throw new NotImplementedException(); }
然后，同样的，一路修改应该调用的方法。
internal virtual List<CommandItem> AtawUpdate(List<CommandItem> items)
public int AtawUpdate(IEntityStateManager entityCache, List<CommandItem> items) { //if (!IsStateManagerDirty(entityCache)) //{ // return 0; //} //// Check that we have a connection before we proceed //if (_connection == null) //{ // throw Error.EntityClient_NoConnectionForAdapter(); //} //// Check that the store connection is available //if (_connection.StoreProviderFactory == null // || _connection.StoreConnection == null) //{ // throw Error.EntityClient_NoStoreConnectionForUpdate(); //} //// Check that the connection is open before we proceed //if (ConnectionState.Open // != _connection.State) //{ // throw Error.EntityClient_ClosedConnectionForUpdate(); //} var updateTranslator = _updateTranslatorFactory(entityCache, this); updateTranslator.AtawUpdate(items); return 0; }
private void AtawSaveChangesToStore(SaveOptions options) { int entriesAffected; // var mustReleaseConnection = false; var connection = (EntityConnection)Connection; // get data adapter if (_adapter == null) { _adapter = (IEntityAdapter)((IServiceProvider)EntityProviderFactory.Instance).GetService(typeof(IEntityAdapter)); } // only accept changes after the local transaction commits _adapter.AcceptChangesDuringUpdate = false; _adapter.Connection = connection; _adapter.CommandTimeout = CommandTimeout; //try //{ //EnsureConnection(); // mustReleaseConnection = true; // determine what transaction to enlist in // var needLocalTransaction = false; //if (null == connection.CurrentTransaction // && !connection.EnlistedInUserTransaction) //{ // // If there isn't a local transaction started by the user, we'll attempt to enlist // // on the current SysTx transaction so we don't need to construct a local // // transaction. // needLocalTransaction = (null == _lastTransaction); //} //// else the user already has his own local transaction going; user will do the abort or commit. //DbTransaction localTransaction = null; //try //{ // // EntityConnection tracks the CurrentTransaction we don't need to pass it around // if (needLocalTransaction) // { // localTransaction = connection.BeginTransaction(); // } entriesAffected = _adapter.AtawUpdate(ObjectStateManager, CommandItems); // if (null != localTransaction) // { // // we started the local transaction; so we also commit it // localTransaction.Commit(); // } // // else on success with no exception is thrown, user generally commits the transaction // } // finally // { // if (null != localTransaction) // { // // we started the local transaction; so it requires disposal (rollback if not previously committed // localTransaction.Dispose(); // } // // else on failure with an exception being thrown, user generally aborts (default action with transaction without an explict commit) // } //} //finally //{ // if (mustReleaseConnection) // { // // Release the connection when we are done with the save // ReleaseConnection(); // } //} //if ((SaveOptions.AcceptAllChangesAfterSave & options) != 0) //{ // // only accept changes after the local transaction commits // try // { // AcceptAllChanges(); // } // catch (Exception e) // { // // If AcceptAllChanges throw - let's inform user that changes in database were committed // // and that Context and Database can be in inconsistent state. // throw new InvalidOperationException(Strings.ObjectContext_AcceptAllChangesFailure(e.Message)); // } //} // return items; }
public int AtawSaveChanges(SaveOptions options) { PrepareToSaveChanges(options); var entriesAffected = ObjectStateManager.GetObjectStateEntriesCount(EntityState.Added | EntityState.Deleted | EntityState.Modified); // if there are no changes to save, perform fast exit to avoid interacting with or starting of new transactions if (0 < entriesAffected) { entriesAffected = 0; AtawSaveChangesToStore(options); } ObjectStateManager.AssertAllForeignKeyIndexEntriesAreValid(); //return items; return 0; }
public virtual int AtawSaveChanges()
{
try
{
if (ValidateOnSaveEnabled)
{
var validationResults = Owner.GetValidationErrors();
if (validationResults.Any())
{
throw new DbEntityValidationException(
Strings.DbEntityValidationException_ValidationFailed, validationResults);
}
}
var shouldDetectChanges = AutoDetectChangesEnabled && !ValidateOnSaveEnabled;
var saveOptions = SaveOptions.AcceptAllChangesAfterSave |
(shouldDetectChanges ? SaveOptions.DetectChangesBeforeSave : 0);
return ObjectContext.AtawSaveChanges(saveOptions);
}
catch (UpdateException ex)
{
throw WrapUpdateException(ex);
}
}
最外面的DbContext 调用：
public virtual void SaveAtawChanges() { HasSaveChanges = true; // List<CommandItem> items = new List<CommandItem>(); // return items; InternalContext.AtawSaveChanges(); }
编译，然后测试一下：
WorkflowDbContext context = new WorkflowDbContext(); var list = context.WF_WORKFLOW_INST.ToList(); list.ForEach(a => a.WI_NAME = "ff123"); context.SaveAtawChanges(); var gg = context.CommandItems.Select(a => { string ff = ""; for (int i = 0; i < a.Parameters.Count; i++) { var par = a.Parameters[i]; ff = ff + par.ParameterName + "=" + par.Value; } return new { sql语句 = a.CommandText, 语句类型 = a.CommandType, 参数 = ff }; } ); //执行事务操作 int ggg = 0; var con = context.Database.Connection as SqlConnection; using (con) { con.Open(); var trans = con.BeginTransaction(); try { foreach (var com in context.CommandItems) { List<SqlParameter> sqls = new List<SqlParameter>(); for (int i = 0; i < com.Parameters.Count; i++) { sqls.Add((((ICloneable)com.Parameters[i]).Clone() as SqlParameter)); } // SqlHelper.ExecuteNonQuery( ggg = ggg + SqlHelper.ExecuteNonQuery(trans, com.CommandType, com.CommandText, sqls.ToArray()); } } catch (Exception ex) { //发生异常，事务回滚 Response.Write("数据更新错误：" + ggg); trans.Rollback(); } } Response.Write("数据更新："+ggg); this.GridView1.DataSource = gg; this.GridView1.DataBind();
sqls.Add((((ICloneable)com.Parameters[i]).Clone() asSqlParameter));
这个代码的目的是因为SqlParameter 不允许被两个DbCommond引用，所以要克隆出来。
最终 我们用
sqlHelper执行sql语句
返回影响行数
GridView上 显示生产的sql 语句
成功。
