如何建立与实体框架5和MVC 4审计线索 [英] how to create an audit trail with Entity framework 5 and MVC 4
问题描述
我建立一个MVC 4应用程序,使用EF 5。 我需要做的审计线索,即登录最终用户进行任何更改。
我也问过这个问题了几次,但在此之前还没有真正得到一个满意的答案。所以我在希望得到的地方加入了很多的详细信息。
目前我拥有多个仓库
即
公共类AuditZoneRepository:IAuditZoneRepository
{
私人AISDbContext上下文=新AISDbContext();
公众诠释保存(AuditZone模型,ModelStateDictionary的ModelState)
{
如果(model.Id == 0)
{
context.AuditZones.Add(模型);
}
其他
{
VAR recordToUpdate = context.AuditZones.FirstOrDefault(X => x.Id == model.Id);
如果(recordToUpdate!= NULL)
{
recordToUpdate.Description = model.Description;
recordToUpdate.Valid = model.Valid;
recordToUpdate.ModifiedDate = DateTime.Now;
}
}
尝试
{
context.SaveChanges();
返回1;
}
赶上(例外前)
{
modelState.AddModelError(。,数据库发生错误,请稍后重试);
返回-1;
}
}
}
公共类岗位codesRepository:IPOST codesRepository
{
私人AISDbContext上下文=新AISDbContext();
公众诠释保存(邮codeS模式,ModelStateDictionary的ModelState)
{
如果(model.Id == 0)
{
context.Post codes.Add(模型);
}
其他
{
VAR recordToUpdate = context.Post codes.FirstOrDefault(X => x.Id == model.Id);
如果(recordToUpdate!= NULL)
{
recordToUpdate.Suburb = model.Suburb;
recordToUpdate.State = model.State;
recordToUpdate.Post code = model.Post code;
recordToUpdate.AuditZoneId = model.AuditZoneId;
recordToUpdate.ModifiedDate = DateTime.Now;
}
}
尝试
{
context.SaveChanges();
返回1;
}
赶上(例外前)
{
modelState.AddModelError(。,数据库发生错误,请稍后重试);
返回-1;
}
}
}
现在我知道我添加code进行检查,看是否有我需要添加它的保存尝试任何更改。在context.SaveChanges前()。
不过,目前我有10的回购协议。我真的不希望添加code到10个不同的地方。由于这code会做同样的事情。我想以某种方式有一个回购从继承一个基类。
任何帮助吗?任何样品code?任何指针?
将AP preciated。我相信其他人会在
这样做我mappying我的钥匙,关系和表像这样
公共类AuditZoneMap:EntityTypeConfiguration< AuditZone>
{
公共AuditZoneMap()
{
// 首要的关键
HasKey(T => t.Id);
//属性
物业(T => t.Description)
.HasMaxLength(100);
//表和放大器;列映射
ToTable(AuditZone);
物业(T => t.Id).HasColumnName(ID);
物业(T => t.Description).HasColumnName(说明);
物业(T => t.Valid).HasColumnName(有效);
物业(T => t.CreatedDate).HasColumnName(CreatedDate);
物业(T => t.CreatedBy).HasColumnName(CreatedBy);
物业(T => t.ModifiedDate).HasColumnName(ModifiedDate);
物业(T => t.ModifiedBy).HasColumnName(ModifiedBy);
//关系
HasOptional(T => t.CreatedByUser)
.WithMany(T => t.CreatedByAuditZone)
.HasForeignKey(D => d.CreatedBy);
HasOptional(T => t.ModifiedByUser)
.WithMany(T => t.ModifiedByAuditZone)
.HasForeignKey(D => d.ModifiedBy);
}
}
我建议你是使用ChangeTracker属性EF。
在你的DBContext.cs,你都会有这样的:
公共类的DbContext:的DbContext
{
公众的DbContext():基地(数据库名称)
{
}
保护覆盖无效OnModelCreating(DbModelBuilder模型构建器)
{
}
公共DbSet< YourPocoModelNameHere> YourPocoModelNameHere {获得;组; }
//这是从调用的SaveChanges不指定用户进行更改覆盖为prevent人
公众覆盖INT的SaveChanges()
{
抛出新的InvalidOperationException异常(用户ID必须提供);
}
公众诠释的SaveChanges(INT用户id)
{
//获取所有的添加/删除/修改实体(未未改性或分离)
在this.ChangeTracker.Entries的foreach(VAR ENT()式(P => p.State == System.Data.EntityState.Added || p.State == System.Data.EntityState.Deleted || p.State == System.Data.EntityState.Modified))
{
//对于每一个变化的记录,得到了审计记录的条目,并将其添加
的foreach(审计日志的X GetAuditRecordsForChange(ENT,用户id))
{
this.AuditLogs.Add(X);
}
}
//调用原有的SaveChanges(),这将节省所做的更改和审计记录
返回base.SaveChanges();
}
私人名单,其中,审计日志> GetAuditRecordsForChange(DbEntityEntry dbEntry,INT用户id)
{
名单<审计日志>结果=新名单,其中,审计日志>();
日期时间changeTime = DateTime.UtcNow;
//获取表()属性(如果存在)
// TableAttribute tableAttr = dbEntry.Entity.GetType()GetCustomAttributes(typeof运算(TableAttribute),FALSE).SingleOrDefault()作为TableAttribute;
TableAttribute tableAttr = dbEntry.Entity.GetType()GetCustomAttributes(typeof运算(TableAttribute),真).SingleOrDefault()作为TableAttribute。
//获取表名称(如果它有一个表的属性,使用,否则获得多元化的名称)
字符串tableName值= tableAttr!= NULL? 。tableAttr.Name:dbEntry.Entity.GetType()名称;
//获取主键值(如果有多个键列,这将需要进行调整)
VAR键名= dbEntry.Entity.GetType()的GetProperties,(),其中(P => p.GetCustomAttributes(typeof运算(KeyAttribute),FALSE).Count之间()> 0)。.ToList();
字符串的keyName =键名[0] .Name点; //dbEntry.Entity.GetType().GetProperties().Single(p => p.GetCustomAttributes(typeof运算(KeyAttribute),FALSE).Count之间()> 0).Name点;
如果(dbEntry.State == System.Data.EntityState.Added)
{
//对于插入,只是添加了全程记录
//如果实体实现IDescribableEntity,请使用说明()的说明,否则使用的ToString()
的foreach(在dbEntry.CurrentValues.PropertyNames串propertyName的)
{
result.Add(新的审计日志()
{
AuditLogId = Guid.NewGuid(),
用户ID =用户id,
EventDateUTC = changeTime,
事件类型=A,//添加
表名= tableName值,
的recordId = dbEntry.CurrentValues.GetValue<对象>(注册表项目)的ToString()
的ColumnName = propertyName的,
的NewValue = dbEntry.CurrentValues.GetValue<对象>(propertyName的)== NULL?空:dbEntry.CurrentValues.GetValue<对象>(propertyName的)的ToString()
}
);
}
}
否则,如果(dbEntry.State == System.Data.EntityState.Deleted)
{
//同样的,删除,执行全程记录,并使用了来自描述)的说明(或的ToString()
result.Add(新的审计日志()
{
AuditLogId = Guid.NewGuid(),
用户ID =用户id,
EventDateUTC = changeTime,
事件类型=D,//删除
表名= tableName值,
的recordId = dbEntry.OriginalValues.GetValue<对象>(注册表项目)的ToString()
的ColumnName =* ALL,
的NewValue =(dbEntry.OriginalValues.ToObject()是IDescribableEntity)? (dbEntry.OriginalValues.ToObject()作为IDescribableEntity).Describe():dbEntry.OriginalValues.ToObject()的ToString()
}
);
}
否则,如果(dbEntry.State == System.Data.EntityState.Modified)
{
的foreach(在dbEntry.OriginalValues.PropertyNames串propertyName的)
{
//对于更新,我们只希望捕获的实际更改的列
如果(的Object.Equals(dbEntry.OriginalValues.GetValue<!对象>(propertyName的),dbEntry.CurrentValues.GetValue<对象>(propertyName的)))
{
result.Add(新的审计日志()
{
AuditLogId = Guid.NewGuid(),
用户ID =用户id,
EventDateUTC = changeTime,
的EventType =M,//改性
表名= tableName值,
的recordId = dbEntry.OriginalValues.GetValue<对象>(注册表项目)的ToString()
的ColumnName = propertyName的,
OriginalValue = dbEntry.OriginalValues.GetValue<对象>(propertyName的)== NULL?空:dbEntry.OriginalValues.GetValue<对象>(propertyName的)的ToString()
的NewValue = dbEntry.CurrentValues.GetValue<对象>(propertyName的)== NULL?空:dbEntry.CurrentValues.GetValue<对象>(propertyName的)的ToString()
}
);
}
}
}
//否则,什么都不做,我们不关心不变或分离的实体
返回结果;
}
}
这将使用下表中您的数据库:
使用[数据库名称]
走
/ ******对象:表[DBO] [审核日志]脚本日期:2014年6月1日五时56分49秒第米****** /
SET ANSI_NULLS ON
走
SET QUOTED_IDENTIFIER ON
走
SET ANSI_PADDING ON
走
CREATE TABLE [DBO]。[审计日志](
[auditlogid] [唯一标识符] NOT NULL,
[用户ID] [INT] NOT NULL,
[eventdateutc] [日期时间] NOT NULL,
[事件类型] [CHAR(1)NOT NULL,
[表名] [为nvarchar(100)NOT NULL,
[的recordId] [为nvarchar(100)NOT NULL,
[的ColumnName] [为nvarchar(100)NOT NULL,
[originalvalue] [为nvarchar(最大)空,
[newvalue中] [为nvarchar(最大)空,
约束[PK_AuditLog] PRIMARY KEY NONCLUSTERED
(
[auditlogid] ASC
)WITH(PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)ON [PRIMARY]
)ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
走
SET ANSI_PADDING OFF
走
ALTER TABLE [DBO]。[审计日志] WITH CHECK约束ADD [FK_auditlog_users]外键([用户ID])
参考文献:[DBO]。[用户]([用户ID])
走
ALTER TABLE [DBO]。[审计日志]检查约束[FK_auditlog_users]
走
通过这一切设置,那么你只需要打电话给你dbContext.SaveChanges(此处的用户ID);
希望这会为你工作。我在我所有的应用程序都使用它伟大工程!
享受它。
I am building an MVC 4 application, using EF 5. I need to do an audit trail, ie log any changes that end users make.
I have asked this question a few times, but haven't really gotten a satisfying answer before. So I am adding a lot more details in hoping to get somewhere..
currently I have multiple repositories
ie
public class AuditZoneRepository : IAuditZoneRepository
{
private AISDbContext context = new AISDbContext();
public int Save(AuditZone model, ModelStateDictionary modelState)
{
if (model.Id == 0)
{
context.AuditZones.Add(model);
}
else
{
var recordToUpdate = context.AuditZones.FirstOrDefault(x => x.Id == model.Id);
if (recordToUpdate != null)
{
recordToUpdate.Description = model.Description;
recordToUpdate.Valid = model.Valid;
recordToUpdate.ModifiedDate = DateTime.Now;
}
}
try
{
context.SaveChanges();
return 1;
}
catch (Exception ex)
{
modelState.AddModelError("", "Database error has occured. Please try again later");
return -1;
}
}
}
public class PostcodesRepository : IPostcodesRepository
{
private AISDbContext context = new AISDbContext();
public int Save(Postcodes model, ModelStateDictionary modelState)
{
if (model.Id == 0)
{
context.Postcodes.Add(model);
}
else
{
var recordToUpdate = context.Postcodes.FirstOrDefault(x => x.Id == model.Id);
if (recordToUpdate != null)
{
recordToUpdate.Suburb = model.Suburb;
recordToUpdate.State = model.State;
recordToUpdate.Postcode = model.Postcode;
recordToUpdate.AuditZoneId = model.AuditZoneId;
recordToUpdate.ModifiedDate = DateTime.Now;
}
}
try
{
context.SaveChanges();
return 1;
}
catch (Exception ex)
{
modelState.AddModelError("", "Database error has occured. Please try again later");
return -1;
}
}
}
Now I know for me to add the code to check to see if there are any changes i need to add it in the try of the save. Before the context.SaveChanges().
But currently I have 10 repos. I don't really want to add code to 10 different places. As this code will do exactly the same thing. I want to somehow have a baseclass that the repos inherit from.
any help? any sample code? any pointers?
would be appreciated. I am sure other people would have done this before
I am mappying my keys, relationships and tables like so
public class AuditZoneMap : EntityTypeConfiguration<AuditZone>
{
public AuditZoneMap()
{
// Primary Key
HasKey(t => t.Id);
// Properties
Property(t => t.Description)
.HasMaxLength(100);
// Table & Column Mappings
ToTable("AuditZone");
Property(t => t.Id).HasColumnName("Id");
Property(t => t.Description).HasColumnName("Description");
Property(t => t.Valid).HasColumnName("Valid");
Property(t => t.CreatedDate).HasColumnName("CreatedDate");
Property(t => t.CreatedBy).HasColumnName("CreatedBy");
Property(t => t.ModifiedDate).HasColumnName("ModifiedDate");
Property(t => t.ModifiedBy).HasColumnName("ModifiedBy");
// Relationships
HasOptional(t => t.CreatedByUser)
.WithMany(t => t.CreatedByAuditZone)
.HasForeignKey(d => d.CreatedBy);
HasOptional(t => t.ModifiedByUser)
.WithMany(t => t.ModifiedByAuditZone)
.HasForeignKey(d => d.ModifiedBy);
}
}
What I recommend you is to use the ChangeTracker property in EF.
Inside your DBContext.cs you will have this:
public class DBContext : DbContext
{
public DBContext () : base("DatabaseName")
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
}
public DbSet<YourPocoModelNameHere > YourPocoModelNameHere { get; set; }
// This is overridden to prevent someone from calling SaveChanges without specifying the user making the change
public override int SaveChanges()
{
throw new InvalidOperationException("User ID must be provided");
}
public int SaveChanges(int userId)
{
// Get all Added/Deleted/Modified entities (not Unmodified or Detached)
foreach (var ent in this.ChangeTracker.Entries().Where(p => p.State == System.Data.EntityState.Added || p.State == System.Data.EntityState.Deleted || p.State == System.Data.EntityState.Modified))
{
// For each changed record, get the audit record entries and add them
foreach (AuditLog x in GetAuditRecordsForChange(ent, userId))
{
this.AuditLogs.Add(x);
}
}
// Call the original SaveChanges(), which will save both the changes made and the audit records
return base.SaveChanges();
}
private List<AuditLog> GetAuditRecordsForChange(DbEntityEntry dbEntry, int userId)
{
List<AuditLog> result = new List<AuditLog>();
DateTime changeTime = DateTime.UtcNow;
// Get the Table() attribute, if one exists
//TableAttribute tableAttr = dbEntry.Entity.GetType().GetCustomAttributes(typeof(TableAttribute), false).SingleOrDefault() as TableAttribute;
TableAttribute tableAttr = dbEntry.Entity.GetType().GetCustomAttributes(typeof(TableAttribute), true).SingleOrDefault() as TableAttribute;
// Get table name (if it has a Table attribute, use that, otherwise get the pluralized name)
string tableName = tableAttr != null ? tableAttr.Name : dbEntry.Entity.GetType().Name;
// Get primary key value (If you have more than one key column, this will need to be adjusted)
var keyNames = dbEntry.Entity.GetType().GetProperties().Where(p => p.GetCustomAttributes(typeof(KeyAttribute), false).Count() > 0).ToList();
string keyName = keyNames[0].Name; //dbEntry.Entity.GetType().GetProperties().Single(p => p.GetCustomAttributes(typeof(KeyAttribute), false).Count() > 0).Name;
if (dbEntry.State == System.Data.EntityState.Added)
{
// For Inserts, just add the whole record
// If the entity implements IDescribableEntity, use the description from Describe(), otherwise use ToString()
foreach (string propertyName in dbEntry.CurrentValues.PropertyNames)
{
result.Add(new AuditLog()
{
AuditLogId = Guid.NewGuid(),
UserId = userId,
EventDateUTC = changeTime,
EventType = "A", // Added
TableName = tableName,
RecordId = dbEntry.CurrentValues.GetValue<object>(keyName).ToString(),
ColumnName = propertyName,
NewValue = dbEntry.CurrentValues.GetValue<object>(propertyName) == null ? null : dbEntry.CurrentValues.GetValue<object>(propertyName).ToString()
}
);
}
}
else if (dbEntry.State == System.Data.EntityState.Deleted)
{
// Same with deletes, do the whole record, and use either the description from Describe() or ToString()
result.Add(new AuditLog()
{
AuditLogId = Guid.NewGuid(),
UserId = userId,
EventDateUTC = changeTime,
EventType = "D", // Deleted
TableName = tableName,
RecordId = dbEntry.OriginalValues.GetValue<object>(keyName).ToString(),
ColumnName = "*ALL",
NewValue = (dbEntry.OriginalValues.ToObject() is IDescribableEntity) ? (dbEntry.OriginalValues.ToObject() as IDescribableEntity).Describe() : dbEntry.OriginalValues.ToObject().ToString()
}
);
}
else if (dbEntry.State == System.Data.EntityState.Modified)
{
foreach (string propertyName in dbEntry.OriginalValues.PropertyNames)
{
// For updates, we only want to capture the columns that actually changed
if (!object.Equals(dbEntry.OriginalValues.GetValue<object>(propertyName), dbEntry.CurrentValues.GetValue<object>(propertyName)))
{
result.Add(new AuditLog()
{
AuditLogId = Guid.NewGuid(),
UserId = userId,
EventDateUTC = changeTime,
EventType = "M", // Modified
TableName = tableName,
RecordId = dbEntry.OriginalValues.GetValue<object>(keyName).ToString(),
ColumnName = propertyName,
OriginalValue = dbEntry.OriginalValues.GetValue<object>(propertyName) == null ? null : dbEntry.OriginalValues.GetValue<object>(propertyName).ToString(),
NewValue = dbEntry.CurrentValues.GetValue<object>(propertyName) == null ? null : dbEntry.CurrentValues.GetValue<object>(propertyName).ToString()
}
);
}
}
}
// Otherwise, don't do anything, we don't care about Unchanged or Detached entities
return result;
}
}
This will use the following table in your DB:
USE [databasename]
GO
/****** Object: Table [dbo].[auditlog] Script Date: 06/01/2014 05:56:49 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[auditlog](
[auditlogid] [uniqueidentifier] NOT NULL,
[userid] [int] NOT NULL,
[eventdateutc] [datetime] NOT NULL,
[eventtype] [char](1) NOT NULL,
[tablename] [nvarchar](100) NOT NULL,
[recordid] [nvarchar](100) NOT NULL,
[columnname] [nvarchar](100) NOT NULL,
[originalvalue] [nvarchar](max) NULL,
[newvalue] [nvarchar](max) NULL,
CONSTRAINT [PK_AuditLog] PRIMARY KEY NONCLUSTERED
(
[auditlogid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[auditlog] WITH CHECK ADD CONSTRAINT [FK_auditlog_users] FOREIGN KEY([userid])
REFERENCES [dbo].[users] ([userid])
GO
ALTER TABLE [dbo].[auditlog] CHECK CONSTRAINT [FK_auditlog_users]
GO
With this all set then you will just need to call your dbContext.SaveChanges(here the userId);
Hope this will work for you... I use it in all my applications and works great!
Enjoy it.
这篇关于如何建立与实体框架5和MVC 4审计线索的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!