使用实体框架记录每个数据更改 [英] Logging every data change with Entity Framework

问题描述

客户需要将进行修改的实际用户将每个数据更改记录到日志记录表。应用程序正在使用一个SQL用户访问数据库，但是我们需要记录真实用户标识。

我们可以通过写入触发器在t-sql中执行此操作为每个表插入和更新，并使用context_info存储用户标识。我们将用户ID传递给存储过程，将用户ID存储在contextinfo中，触发器可以使用该信息将日志行写入日志表。

I找不到在哪里或怎么做类似的使用EF的地方或方式。所以主要目标是：如果我通过EF更改数据，我想以半自动的方式将精确的数据更改记录到表中（所以我不想检查每个字段以进行更改保存对象）。我们正在使用EntitySQL。

不幸的是，我们必须坚持SQL 2000，所以在SQL2008中引入的数据更改捕获不是一个选项（但也许这不是正确的方法

任何想法，链接或起点？

一些注释：通过使用ObjectContext.SavingChanges事件处理程序，我可以得到我可以注入SQL语句初始化contextinfo的点。但是我不能混合EF和标准SQL。所以我可以得到EntityConnection，但是我不能执行使用它的T-SQL语句。或者我可以获取EntityConnection的连接字符串，并根据它创建一个SqlConnection，但它将是一个不同的连接，因此contextinfo不会影响EF所做的保存。

我在SavingChanges处理程序中尝试过以下操作：

  testEntities te =（testEntities）sender; 
 DbConnection dc = te.Connection; 
 DbCommand dcc = dc.CreateCommand（）; 
 dcc.CommandType = CommandType.StoredProcedure; 
 DbParameter dp = new EntityParameter（）; 
 dp.ParameterName =userid; 
 dp.Value = textBox1.Text; 
 dcc.CommandText =userinit; 
 dcc.Parameters.Add（dp）; 
 dcc.ExecuteNonQuery（）; 
  

错误：EntityCommand.CommandText的值对于StoredProcedure命令无效。
与SqlParameter代替EntityParameter相同：SqlParameter不能使用。

  StringBuilder cStr = new StringBuilder（declare @ tx char（50）; set @ tx ='）; 
 cStr.Append（textBox1.Text）; 
 cStr.Append（'; declare @m binary（128）; set @m = cast（@tx as binary（128））; set context_info @m;）; 
 
 testEntities te =（testEntities）sender; 
 DbConnection dc = te.Connection; 
 DbCommand dcc = dc.CreateCommand（）; 
 dcc.CommandType = CommandType.Text; 
 dcc.CommandText = cStr.ToString（）; 
 dcc.ExecuteNonQuery（）; 
  

错误：查询语法无效。

所以在这里，我坚持在Entity Framework和ADO.NET之间创建一个桥梁。
如果我可以让它工作，我会发布一个概念证明。

解决方案

a href =http://msdn.microsoft.com/en-us/library/cc716714.aspx =noreferrer> SavingChanges ？

There is a need from a customer to log every data change to a logging table with the actual user who made the modification. The application is using one SQL user to access the database, but we need to log the "real" user id.

We can do this in t-sql by writing triggers for every table insert and update, and using context_info to store the user id. We passed the user id to a stored procedure, stored the user id in the contextinfo, and the trigger could use this info to write log rows to the log table.

I can not find the place or way where or how can I do something similar using EF. So the main goal is: if I make a change in the data via EF, I would like to log the exact data change to a table in a semi-automatic way (so I don't want to check for every field for change before saving the object). We are using EntitySQL.

Unfortunately we have to stick on SQL 2000 so the data change capture introduced in SQL2008 is not an option (but maybe that's also not the right way for us).

Any ideas, links or starting points?

[Edit] Some notes: by using ObjectContext.SavingChanges eventhandler, I can get the point where I can inject the SQL statement to initialize the contextinfo. However I cannot mix the EF and the standard SQL. So I can get the EntityConnection but I cannot execute a T-SQL statement using it. Or I can get the connection string of the EntityConnection and create an SqlConnection based on it, but it will be a different connection, so the contextinfo will not affect the save made by the EF.

I tried the following in the SavingChanges handler:

testEntities te = (testEntities)sender;
DbConnection dc = te.Connection;
DbCommand dcc = dc.CreateCommand();
dcc.CommandType = CommandType.StoredProcedure;
DbParameter dp = new EntityParameter();
dp.ParameterName = "userid";
dp.Value = textBox1.Text;
dcc.CommandText = "userinit";
dcc.Parameters.Add(dp);
dcc.ExecuteNonQuery();

Error: The value of EntityCommand.CommandText is not valid for a StoredProcedure command. The same with SqlParameter instead of EntityParameter: SqlParameter cannot be used.

StringBuilder cStr = new StringBuilder("declare @tx char(50); set @tx='");
cStr.Append(textBox1.Text);
cStr.Append("'; declare @m binary(128); set @m = cast(@tx as binary(128)); set context_info @m;");

testEntities te = (testEntities)sender;
DbConnection dc = te.Connection;
DbCommand dcc = dc.CreateCommand();
dcc.CommandType = CommandType.Text;
dcc.CommandText = cStr.ToString();
dcc.ExecuteNonQuery();

Error: The query syntax is not valid.

So here I am, stuck to create a bridge between Entity Framework and ADO.NET. If I can get it working, I will post a proof of concept.

解决方案

How about handling Context.SavingChanges?

这篇关于使用实体框架记录每个数据更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！