EF6代码首先使用数据注释填充SQL表和列的描述

本文关键字：SQL 描述 填充 注释 代码 数据 EF6 | 更新日期: 2023-09-27 18:05:34

我的工作有一个标准，要求每个表和列(以及其他数据库对象)都有一个描述。我会通过SQL Management Studio或TSQL Script中的属性窗口来实现这一点。

是否可以使用数据注释(例如Description)来生成关于迁移的描述? 

    [Description("List of Valid System Parameter Groups")]
    public class SystemParameterGroup
    {
        public SystemParameterGroup()
        {
            this.SystemParameters = new ObservableCollection<SystemParameter>();
        }
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]   
        [Description("PK: Unique Record Identifer")]
        public int SystemParameterGroupId { get; set; }
        [Required(ErrorMessage="Group Name is Required")]
        [MinLength(5, ErrorMessage="Must be at least 5 Characters")]
        [MaxLength(50, ErrorMessage="Must be Less than 50 Characters")]
        [Description("Name of System Parameter Group")]
        public string SystemParameterGroupName { get; set; }
        #region Child Records
        public virtual ObservableCollection<SystemParameter> SystemParameters { set; get; }
        #endregion
    }

如何先使用代码获取要使用的描述?

EF6代码首先使用数据注释填充SQL表和列的描述

我建议创建一个自定义迁移操作，参见这篇文章:http://dolinkamark.wordpress.com/2014/05/03/creating-a-custom-migration-operation-in-entity-framework/

这样你可以封装添加和删除表的描述，但是这只够手动添加这些操作，代码首先不会自动在模型中找到它。

要实现自动添加，你必须使用MigrationScaffolder类。具体来说，您必须创建一个继承MigrationScaffolder类并覆盖其Scaffold函数的新类。目前我正在写一篇关于如何做到这一点的文章，如果我完成了，我会用链接扩展这篇文章。

我听取了Mark的建议，创建了自己的Comment Code

我创建了这两个函数CreateTableDescription和CreateColumnDescription Code:

    /// <summary>
    /// Add Descriptions to New Tables and/or Columns
    /// </summary>
    /// <param name="tableName">Name of Table</param>
    /// <param name="tableDescription">Description of Table</param>
    /// <returns></returns>
    public static string CreateTableDescription(string tableName, string tableDescription )
    {
            return string.Format("IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('{0}') AND [name] = N'MS_Description' AND [minor_id] = 0) EXECUTE sp_addextendedproperty N'MS_Description', '{1}', N'SCHEMA', N'dbo', N'TABLE', N'{0}', NULL, NULL ELSE EXECUTE sp_updateextendedproperty N'MS_Description', '{1}', N'SCHEMA', N'dbo', N'TABLE', N'{0}', NULL, NULL", tableName, tableDescription);
    }
    /// <summary>
    /// Updates Descriptions on Tables and Columns
    /// </summary>
    /// <param name="tableName">Name of Table for Column</param>
    /// <param name="columnName">Name of Column</param>
    /// <param name="columDescription">Description for Column</param>
    /// <returns></returns>
    public static string CreateColumnDescription(string tableName, string columnName , string columDescription )
    {
        return string.Format("IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('{0}') AND [name] = N'MS_Description' AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = '{1}' AND [object_id] = OBJECT_ID('{0}'))) EXECUTE sp_addextendedproperty N'MS_Description', '{2}', N'SCHEMA', N'dbo', N'TABLE', N'{0}', N'COLUMN', N'{1}' ELSE EXECUTE sp_updateextendedproperty N'MS_Description', '{2}', N'SCHEMA', N'dbo', N'TABLE', N'{0}', N'COLUMN', N'{1}'", tableName, columnName, columDescription);
    }

然后在迁移的Up()中运行以下示例

RunSqlTransaction(CommonCode.CreateTableDescription("__MigrationHistory", "Database Version History Table - Administration User Only"));
    RunSqlTransaction(CommonCode.CreateColumnDescription("__MigrationHistory", "ContextKey", "Defining the Migration Context"));
            RunSqlTransaction(CommonCode.CreateColumnDescription("__MigrationHistory", "MigrationId", "Unique Migration Identifier"));
            RunSqlTransaction(CommonCode.CreateColumnDescription("__MigrationHistory", "Model", "Binary of the SQL Change Model"));
            RunSqlTransaction(CommonCode.CreateColumnDescription("__MigrationHistory", "ProductVersion", "Version of The Database Change"));
RunSqlTransaction的代码为 
private void RunSqlTransaction(string sqlStatement)
        {
            //split the script on "GO" commands
            string[] splitter = new string[] { "'r'nGO'r'n" };
            string[] commandTexts = sqlStatement.Split(splitter,
              StringSplitOptions.RemoveEmptyEntries);
            foreach (string commandText in commandTexts)
            {
            Sql(commandText.Replace("GO", ""));
            }
        }
相关文章：