小程序大智慧，sqlserver 注释提取工具

原文:小程序大智慧，sqlserver 注释提取工具

开篇背景

我习惯在写表的创建脚本时将注释直接写在脚本里，比如

/*账套*/
CREATE TABLE [dbo].[AccountingBook]
(
	[IDNO]			NVARCHAR (255) NOT NULL,	/*ID*/
    [BH]			NVARCHAR (255) NULL,		/*业务编号*/
	[Name]			NVARCHAR (255) NOT NULL,	/*名称*/
	[Decription]	NVARCHAR (255) NULL,		/*描述*/
	[Owner]			NVARCHAR (255) NOT NULL,	/*所属*/
	CONSTRAINT [PK_AccountingBook] PRIMARY KEY CLUSTERED ([IDNO] ASC)
)

这样写很直观，如果在vs里创建一个数据库项目，把表的创建脚本放在里面进行管理，就非常方便的。

由于习惯用自己的Orm框架，所以DTO也就是那些数据映射实体我都是用codeSmith生成，生成这些DTO对象时，我想共用我的那些注释，那么我该怎么办呢，之前，我需要把这些注释复制出来写成一些注释创建的脚本，像这样　　

exec sp_addextendedproperty N'MS_Description', N'字段描述', N'user', N'dbo', N'table', N'表名', N'column', N'字段名'

添加注释的目的是除了在使用数据库连接工具时方便查看表和字段的说明外，还可以使用CodeSmith生成代码的时候就可以通过编写模版生成带注释的映射DTO 对象，如下　　

/// <summary>
/// 业务编号
/// </summary>
[Column(ColumnName=Columns.BH,FullName=Columns.BHFullName,Index=,CType=typeof(string),Description="业务编号")]
[DataMember(Order = )]
public virtual string BH{get;set;}

但是由于表创建脚本里的注释不能直接写入到数据库的表和字段中，所以注释的创建脚本我需要再写一次，我觉得比较不爽，于是我决定写个小工具从表的创建脚本里面抽取那些本来就写好的注释，从而减小重复机械的工作，也防止错误的发生。

这样一个程序非常简单，下面说一下思路

实现

一，写好带注释的表脚本，并提取这些信息

格式按文章开始讲到的那样写好，即"/*注释*/"，“*”可以是多个，比如"/*********注释***********/"，并将这些脚本存放到相同根目录

要提取这些注释，最大的功臣非正则表达式莫属了

　　　　

a.提取表头的注释，也就是表名的解释正则表达式　　　　　　

 private readonly Regex _tableReg = new Regex(@"/[\*]+([^\*]*)[\*]+/[\r\n\s]*CREATE TABLE \[dbo\].\[(\w*)\]");

b.提取列的注释正则表达式

private readonly Regex _columnsReg = new Regex(@"\[([\w]*)\][^\/]*/[\*]+([^\*]*)[\*]+/");

二，递归查找到这些表的创建脚本，将每个生成注释脚本的字符串连接起来

Func<string, List<string>> getFolderSqlNotes = null;
    getFolderSqlNotes = path =>
    {
        var listAll = new List<string>();
        var files = Directory.GetFiles(path);
        var dirs = Directory.GetDirectories(path);
        foreach (string t in dirs)
        {
            listAll.AddRange(getFolderSqlNotes(t)); ;
        }
        var listStr = files.Where(m => m.EndsWith(".sql")).Select(GetDescriptionSql).ToList();
        listAll.AddRange(listStr);
        return listAll;
    };
    var list = getFolderSqlNotes(Path);
    return string.Join("\r\n", list);

三，执行生成好的脚本（如下图），注释创建完成

四，用codesimth 生成映射类（已带注释）

核心代码

 namespace GenrerateSqlDescription
 {
     using System;
     using System.Collections.Generic;
     using System.IO;
     using System.Linq;
     using System.Text;
     using System.Text.RegularExpressions;

     public class SqlNotesGenerator
     {
         private readonly Regex _tableReg = new Regex(@"/[\*]+([^\*]*)[\*]+/[\r\n\s]*CREATE TABLE \[dbo\].\[(\w*)\]");
         private readonly Regex _columnsReg = new Regex(@"\[([\w]*)\][^\/]*/[\*]+([^\*]*)[\*]+/");
         private const string TableDescriptionCrateSqlFormat = "EXEC sp_addextendedproperty 'MS_Description','{0}','user ',dbo,'table',[{1}];\r\n";
         private const string ColumnDescriptionCrateSqlFormat = "EXEC sp_addextendedproperty 'MS_Description','{0}','user',dbo,'table',[{1}],'column','{2}';\r\n";
         private const string TableDescriptionDropSqlFormat = "EXEC sp_dropextendedproperty 'MS_Description','user',dbo,'table',[{0}];\r\n";
         private const string ColumnescriptionDropSqlFormat = "EXEC sp_dropextendedproperty 'MS_Description','user',dbo,'table',[{0}],'column','{1}';\r\n";
         private const string CheckTableExistsSqlFormat = "IF OBJECT_ID(N'{0}',N'U') IS NOT NULL \r\nBEGIN \r\n";
         private const string EndStr = "END";
         private const string Tab = "    ";

         public bool GenDrop { get; set; }
         public string Path { get; set; }

         private string GetDescriptionSql(string path)
         {
             var sb = new StringBuilder();
             var fs = File.OpenRead(path);
             var fileRd = new StreamReader(fs);
             var str = fileRd.ReadToEnd();
             var tableMatch = _tableReg.Match(str);
             if (tableMatch.Length < ) return string.Empty;

             var tableName = tableMatch.Groups[].Value;
             var tableDes = tableMatch.Groups[].Value;
             if (string.IsNullOrEmpty(tableName) || string.IsNullOrEmpty(tableDes)) return string.Empty;

             var columnStr = str.Substring(str.IndexOf("(", StringComparison.Ordinal));
             var matches = _columnsReg.Matches(columnStr);

             sb.AppendFormat(CheckTableExistsSqlFormat, tableName);

             sb.Append(Tab);
             if (GenDrop)
                 sb.AppendFormat(TableDescriptionDropSqlFormat, tableName);
             else
                 sb.AppendFormat(TableDescriptionCrateSqlFormat, tableDes, tableName);
             foreach (Match match in matches)
             {
                 var columnName = match.Groups[].Value;
                 var description = match.Groups[].Value;
                 if (string.IsNullOrEmpty(columnName) || string.IsNullOrEmpty(description))
                     continue;
                 sb.Append(Tab);
                 if (GenDrop)
                     sb.AppendFormat(ColumnescriptionDropSqlFormat, tableName, columnName);
                 else
                     sb.AppendFormat(ColumnDescriptionCrateSqlFormat, description, tableName, columnName);
             }

             sb.AppendLine(EndStr);
             return sb.ToString();
         }

         public string GetGenerateSql()
         {
             Func<string, List<string>> getFolderSqlNotes = null;
             getFolderSqlNotes = path =>
             {
                 var listAll = new List<string>();
                 var files = Directory.GetFiles(path);
                 var dirs = Directory.GetDirectories(path);
                 foreach (string t in dirs)
                 {
                     listAll.AddRange(getFolderSqlNotes(t)); ;
                 }
                 var listStr = files.Where(m => m.EndsWith(".sql")).Select(GetDescriptionSql).ToList();
                 listAll.AddRange(listStr);
                 return listAll;
             };
             var list = getFolderSqlNotes(Path);
             return string.Join("\r\n", list);
         }

     }

 }

SqlNotesGenerator

CodeSimith模版

 <%--
 Name:
 Author:
 Description:
 --%>
 <%@ CodeTemplate Language="C#" TargetLanguage="C#" Src="" Inherits="OutputFileCodeTemplate" Debug="False" Description="Template description here." %>
 <%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema"  Category="Context"  Optional="True" Description="the table name"   %>
 <%@ Assembly Name="SchemaExplorer" %>
 <%@ Import Namespace="SchemaExplorer" %>
 <%@ Assembly Name="CodeSmith.BaseTemplates" %>
 <%@ Import Namespace="CodeSmith.BaseTemplates" %>
 <%@ Assembly Name="CodeSmith.CustomProperties" %>
 <%@ Import Namespace="CodeSmith.CustomProperties" %>
 //------------------------------------------------------------------------------
 // <auto-generated>
 //     This code generated by the tool, do not propose to amend
 //       Generation time:<%=DateTime.Now%>
 // </auto-generated>
 //------------------------------------------------------------------------------
 using System;
 using System.Data;
 using System.Runtime.Serialization;
 using XDbFramework;
 using BPM_M01.DataAccessLayer.Base;
 using System.Xml.Serialization;
 using System.Diagnostics;
 using System.CodeDom.Compiler;

 namespace <%=Namespace%>
 {
     [Table(TableName = <%=this.SourceTable.Name%>.Table.Name ,Descripton = "<%=this.SourceTable.Description%>",GenreratePK = <%=GenreratePK.ToString().ToLower()%>)]
       [GeneratedCodeAttribute("System.Xml", "2.0.50727.4927")]
     [DebuggerStepThroughAttribute()]
     public partial class <%=this.SourceTable.Name%> : DtoBase
     {
         public static class Table
         {
             public const string Name = "<%=this.SourceTable.Name%>";
         }
         public static class Columns
         {
         <%for(int i=; i<this.SourceTable.Columns.Count;i++)
         {
             string colName=this.SourceTable.Columns[i].Name;%>
             public const string <%=colName%> = "<%=colName%>";
             public const string <%=colName%>FullName =Table.Name + "." + "<%=colName%>";
         <%}%>
         }

     <%for(int i=; i<this.SourceTable.Columns.Count;i++)
     {
         int namelength=this.SourceTable.Columns[i].Name.Length;
         string colName=this.SourceTable.Columns[i].Name;
         if(colName == "ID" || colName == "IDNO") continue;
         string titleCaseColumName = colName.Substring(,).ToUpper() + colName.Substring(,colName.Length-);
         %>

         /// <summary>
         /// <%=this.SourceTable.Columns[i].Description%>
         /// </summary>
         <%if(this.SourceTable.Columns[i].IsPrimaryKeyMember){%>
         [Column(KeyType = KeyTypeEnum.PrimaryKey,FullName="<%=this.SourceTable.Name%>.<%=this.SourceTable.Columns[i].Name%>", ColumnName="<%=this.SourceTable.Columns[i].Name%>",Index=<%=i%>,Description="<%=this.SourceTable.Columns[i].Description%>")]
         <%}%>
         <%else{%>
         <%if(this.SourceTable.Columns[i].IsForeignKeyMember)%>
         <%//
         foreach(TableKeySchema tks in SourceTable.ForeignKeys)
         {
         // 

             foreach(MemberColumnSchema mcs in tks.ForeignKeyMemberColumns)
             {

                 //
                 if(mcs.Name == SourceTable.Columns[i].Name)
                 {
                     TableSchema ts= tks.PrimaryKeyTable;%>
         [Column(ColumnName=Columns.<%=colName%>,KeyType = KeyTypeEnum.ForeignKey,FullName=Columns.<%=colName%>FullName,CType=typeof(<%=GetCSharpVariableType(this.SourceTable.Columns[i])%>),ForeignKeyTableName="<%=ts.Name%>",ForeignKeyFiledName="<%=ts.PrimaryKey.MemberColumns[0].Name%>", DbType=<%=GetSqlDBType(this.SourceTable.Columns[i].NativeType)%> Index=<%=i%>,Description="<%=this.SourceTable.Columns[i].Description%>")]
                    <% break;
                 }
             }
         }
         %>
         <%else{%>
         [Column(ColumnName=Columns.<%=colName%>,FullName=Columns.<%=colName%>FullName,Index=<%=i%>,CType=typeof(<%=GetCSharpVariableType(this.SourceTable.Columns[i])%>),Description="<%=this.SourceTable.Columns[i].Description%>")]
         <%}%>
         <%}%>
         [DataMember(Order = <%=i%>)]
         public virtual <%=GetCSharpVariableType(this.SourceTable.Columns[i])%> <%=titleCaseColumName%>{get;set;}

     <%}%>
     }
 }

 <script runat="template">
 // Override the OutputFile property and assign our specific settings to it.
 [FileDialog(FileDialogType.Save, Title="Select Output File", Filter="C# Files (*.cs)|*.cs", DefaultExtension=".cs")]
 public override string OutputFile
 {
     get {return base.OutputFile;}
     set {base.OutputFile = value;}
 }
 private string _Namespace;
 public string Namespace
 {
     get{return _Namespace;}
     set{_Namespace = value;}
 }

 public bool GenreratePK{get;set;}
 </script>
 <script runat="template">
 public string GetSqlDBType(string type)
 {
     switch(type)
     {
         case "int": return "SqlDbType.Int,";
         case "bit": return "SqlDbType.Bit,";
         case "bigint": return "SqlDbType.BigInt,";
         case "tinyint": return "SqlDbType.TinyInt,";
         case "nvarchar": return "SqlDbType.NVarChar,";
         case "date": return "SqlDbType.Date,";
         case "datetime": return "SqlDbType.DateTime,";
         case "char": return "SqlDbType.Char,";
         case "decimal": return "SqlDbType.Decimal,";
         case "float": return "SqlDbType.Float,";
         case "image": return "SqlDbType.Image,";
         case "money": return "SqlDbType.Money,";
         case "nchar": return "SqlDbType.NChar,";
         case "ntext": return "SqlDbType.NText,";
         case "real": return "SqlDbType.Real,";
         case "smalldatetime": return "SqlDbType.SmallDateTime,";
         case "smallint": return "SqlDbType.SmallInt,";
         case "smallmoney": return "SqlDbType.SmallMoney,";
         case "text": return "SqlDbType.Text,";
         case "timestamp": return "SqlDbType.Timestamp,";
         case "udt": return "SqlDbType.Udt,";
         case "uniqueidentifier": return "SqlDbType.UniqueIdentifier,";
         case "varbinary": return "SqlDbType.VarBinary,";
         case "varchar": return "SqlDbType.VarChar,";
         case "variant": return "SqlDbType.Variant,";
         case "xml": return "SqlDbType.Xml,";
         default : return "";
     }

 }
 public string GetCSharpVariableType(ColumnSchema column)
 {
     switch (column.DataType)
     {
         case DbType.AnsiString: return "string";
         case DbType.AnsiStringFixedLength: return "string";
         case DbType.Binary: return "byte[]";
         case DbType.Boolean: return "bool?";
         case DbType.Byte: return "byte";
         case DbType.Currency: return "decimal?";
         case DbType.Date: return "DateTime?";
         case DbType.DateTime: return "DateTime?";
         case DbType.Decimal: return "decimal?";
         case DbType.Double: return "double?";
         case DbType.Guid: return "Guid";
         case DbType.Int16: return "short?";
         case DbType.Int32: return "int?";
         case DbType.Int64: return "long?";
         case DbType.Object: return "object";
         case DbType.SByte: return "sbyte";
         case DbType.Single: return "float?";
         case DbType.String: return "string";
         case DbType.StringFixedLength: return "string";
         case DbType.Time: return "TimeSpan";
         case DbType.UInt16: return "ushort?";
         case DbType.UInt32: return "uint?";
         case DbType.UInt64: return "ulong?";
         case DbType.VarNumeric: return "decimal?";
         default:
         {
             return "__UNKNOWN__" + column.NativeType;
         }
     }
 }
 </script>

代码

下载

GenrerateSqlDescription_8_28.zip