我如何使用C＃在数据库优点方法中具有审核列

Question

我需要为我的MVC项目(Web-Application(中的所有表添加审核列(由，创建，修改和修改(。我想到拥有这4列的基类并继承了所有其他类。但是我正在使用数据库优先的方法，因此EF生成了所有类。我不确定如何添加审核列和基类继承。

Answer 1

数据库首先意味着您必须手动将所有字段添加到数据库，而AFAIK则没有继承。您可以编写一个添加这些列的SQL脚本。正如Mahyar所说。但是您仍然缺乏继承。

，或者您首先切换到代码。然后，您可以定义接口并处理代码中的审核。如下所示。我例如使用此接口：

    /// <summary>
    /// Adds auditing properties to an entity.
    /// </summary>
    public interface IAuditedEntity
    {
        /// <summary>
        /// Date and time of the entity's creation. Usually in UTC.
        /// </summary>
        /// <remarks>Best set via a <see cref="ITimeProvider"/>.</remarks>
        DateTime DateCreated { get; set; }
        /// <summary>
        /// Identification who created this instance.
        /// </summary>
        /// <remarks>Best set via a <see cref="ICurrentUserIdProvider"/>.</remarks>
        string CreatedBy { get; set; }
        /// <summary>
        /// Date and time of last modification. Usually in UTC.
        /// </summary>
        /// <remarks>Best set via a <see cref="ITimeProvider"/>.</remarks>
        DateTime? DateModified { get; set; }
        /// <summary>
        /// Last one modifiying this instance.
        /// </summary>
        /// <remarks>Best set via a <see cref="ICurrentUserIdProvider"/>.</remarks>
       string ModifiedBy { get; set; }
}

用户提供商接口的定义是这样的：

    /// <summary>
    /// Interface for providing the current user's id.
    /// </summary>
    public interface ICurrentUserIdProvider
    {
        /// <summary>
        /// Get the id of the curent user.
        /// </summary>
        /// <returns></returns>
        string GetCurrentUserId();
    }

用于测试目的，您现在可以使用环境上下文/服务来交付当前用户或您需要测试逻辑的任何用户。

Answer 2

我认为首先无法通过EF数据库执行所需的操作。但是，为了将特定字段添加到数据库的所有表中，您可以使用SQL Server中每个字段的以下代码，然后更新您的EF模型。

DECLARE @TableName VARCHAR(100)
DECLARE @TableSchema VARCHAR(100)
DECLARE @COLUMN_NAME VARCHAR(50)
SET @COLUMN_NAME='CreatedOn' 
DECLARE @COLUMN_DATATYPE VARCHAR(50)
SET @COLUMN_DATATYPE='DateTime'
DECLARE CUR CURSOR FOR
  SELECT TABLE_SCHEMA,
         TABLE_NAME
  FROM   INFORMATION_SCHEMA.TABLES
  WHERE  TABLE_TYPE = 'BASE TABLE'
OPEN CUR
FETCH NEXT FROM CUR INTO @TableSchema,@TableName
WHILE @@FETCH_STATUS = 0
  BEGIN
  DECLARE @SQL NVARCHAR(MAX)
  SET @SQL=NULL
  IF NOT EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
       WHERE TABLE_NAME=@TableName AND COLUMN_NAME = @COLUMN_NAME  and Table_Schema=@TableSchema)
  BEGIN
  SET @SQL='ALTER TABLE '+ @TableSchema+'.'+ @TableName +' ADD '+ @COLUMN_NAME + ' '+ @COLUMN_DATATYPE
    PRINT @SQL
    EXEC(@SQL)
  END
  IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
              WHERE TABLE_NAME=@TableName AND COLUMN_NAME=@COLUMN_NAME and Table_Schema=@TableSchema)
  BEGIN
    PRINT 'Column Already exists in Table'
  END
      FETCH NEXT FROM CUR INTO @TableSchema,@TableName
  END
CLOSE CUR
DEALLOCATE CUR