OGeek|极客世界-中国程序员成长平台 › 门户 › 编程› 移动开发›小程序云开发高级教程

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

原作者: [db:作者] 来自: [db:来源] 收藏邀请

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

/*账套*/
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=1,CType=typeof(string),Description="业务编号")]
[DataMember(Order = 1)]
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 生成映射类（已带注释）

核心代码

 1 namespace GenrerateSqlDescription
 2 {
 3     using System;
 4     using System.Collections.Generic;
 5     using System.IO;
 6     using System.Linq;
 7     using System.Text;
 8     using System.Text.RegularExpressions;
 9 
10 
11     public class SqlNotesGenerator
12     {
13         private readonly Regex _tableReg = new Regex(@"/[\*]+([^\*]*)[\*]+/[\r\n\s]*CREATE TABLE \[dbo\].\[(\w*)\]");
14         private readonly Regex _columnsReg = new Regex(@"\[([\w]*)\][^\/]*/[\*]+([^\*]*)[\*]+/");
15         private const string TableDescriptionCrateSqlFormat = "EXEC sp_addextendedproperty 'MS_Description','{0}','user ',dbo,'table',[{1}];\r\n";
16         private const string ColumnDescriptionCrateSqlFormat = "EXEC sp_addextendedproperty 'MS_Description','{0}','user',dbo,'table',[{1}],'column','{2}';\r\n";
17         private const string TableDescriptionDropSqlFormat = "EXEC sp_dropextendedproperty 'MS_Description','user',dbo,'table',[{0}];\r\n";
18         private const string ColumnescriptionDropSqlFormat = "EXEC sp_dropextendedproperty 'MS_Description','user',dbo,'table',[{0}],'column','{1}';\r\n";
19         private const string CheckTableExistsSqlFormat = "IF OBJECT_ID(N'{0}',N'U') IS NOT NULL \r\nBEGIN \r\n";
20         private const string EndStr = "END";
21         private const string Tab = "    ";
22 
23         public bool GenDrop { get; set; }
24         public string Path { get; set; }
25 
26         private string GetDescriptionSql(string path)
27         {
28             var sb = new StringBuilder();
29             var fs = File.OpenRead(path);
30             var fileRd = new StreamReader(fs);
31             var str = fileRd.ReadToEnd();
32             var tableMatch = _tableReg.Match(str);
33             if (tableMatch.Length < 2) return string.Empty;
34 
35             var tableName = tableMatch.Groups[2].Value;
36             var tableDes = tableMatch.Groups[1].Value;
37             if (string.IsNullOrEmpty(tableName) || string.IsNullOrEmpty(tableDes)) return string.Empty;
38 
39             var columnStr = str.Substring(str.IndexOf("(", StringComparison.Ordinal));
40             var matches = _columnsReg.Matches(columnStr);
41 
42             sb.AppendFormat(CheckTableExistsSqlFormat, tableName);
43 
44             sb.Append(Tab);
45             if (GenDrop)
46                 sb.AppendFormat(TableDescriptionDropSqlFormat, tableName);
47             else
48                 sb.AppendFormat(TableDescriptionCrateSqlFormat, tableDes, tableName);
49             foreach (Match match in matches)
50             {
51                 var columnName = match.Groups[1].Value;
52                 var description = match.Groups[2].Value;
53                 if (string.IsNullOrEmpty(columnName) || string.IsNullOrEmpty(description))
54                     continue;
55                 sb.Append(Tab);
56                 if (GenDrop)
57                     sb.AppendFormat(ColumnescriptionDropSqlFormat, tableName, columnName);
58                 else
59                     sb.AppendFormat(ColumnDescriptionCrateSqlFormat, description, tableName, columnName);
60             }
61 
62             sb.AppendLine(EndStr);
63             return sb.ToString();
64         }
65 
66 
67         public string GetGenerateSql()
68         {
69             Func<string, List<string>> getFolderSqlNotes = null;
70             getFolderSqlNotes = path =>
71             {
72                 var listAll = new List<string>();
73                 var files = Directory.GetFiles(path);
74                 var dirs = Directory.GetDirectories(path);
75                 foreach (string t in dirs)
76                 {
77                     listAll.AddRange(getFolderSqlNotes(t)); ;
78                 }
79                 var listStr = files.Where(m => m.EndsWith(".sql")).Select(GetDescriptionSql).ToList();
80                 listAll.AddRange(listStr);
81                 return listAll;
82             };
83             var list = getFolderSqlNotes(Path);
84             return string.Join("\r\n", list);
85         }
86 
87     }
88 
89 }

SqlNotesGenerator

CodeSimith模版

  1 <%-- 
  2 Name:
  3 Author: 
  4 Description: 
  5 --%>
  6 <%@ CodeTemplate Language="C#" TargetLanguage="C#" Src="" Inherits="OutputFileCodeTemplate" Debug="False" Description="Template description here." %>
  7 <%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema"  Category="Context"  Optional="True" Description="the table name"   %>
  8 <%@ Assembly Name="SchemaExplorer" %>
  9 <%@ Import Namespace="SchemaExplorer" %>
 10 <%@ Assembly Name="CodeSmith.BaseTemplates" %>
 11 <%@ Import Namespace="CodeSmith.BaseTemplates" %>
 12 <%@ Assembly Name="CodeSmith.CustomProperties" %>
 13 <%@ Import Namespace="CodeSmith.CustomProperties" %>
 14 //------------------------------------------------------------------------------
 15 // <auto-generated>
 16 //     This code generated by the tool, do not propose to amend
 17 //       Generation time:<%=DateTime.Now%>
 18 // </auto-generated>
 19 //------------------------------------------------------------------------------
 20 using System;
 21 using System.Data;
 22 using System.Runtime.Serialization;
 23 using XDbFramework;
 24 using BPM_M01.DataAccessLayer.Base;
 25 using System.Xml.Serialization;
 26 using System.Diagnostics;
 27 using System.CodeDom.Compiler;
 28 
 29 namespace <%=Namespace%>
 30 {
 31     [Table(TableName = <%=this.SourceTable.Name%>.Table.Name ,Descripton = "<%=this.SourceTable.Description%>",GenreratePK = <%=GenreratePK.ToString().ToLower()%>)]
 32       [GeneratedCodeAttribute("System.Xml", "2.0.50727.4927")]
 33     [DebuggerStepThroughAttribute()]
 34     public partial class <%=this.SourceTable.Name%> : DtoBase
 35     {
 36         public static class Table
 37         {
 38             public const string Name = "<%=this.SourceTable.Name%>";
 39         }
 40         public static class Columns
 41         {
 42         <%for(int i=0; i<this.SourceTable.Columns.Count;i++)
 43         {
 44             string colName=this.SourceTable.Columns[i].Name;%>
 45             public const string <%=colName%> = "<%=colName%>";
 46             public const string <%=colName%>FullName =Table.Name + "." + "<%=colName%>";            
 47         <%}%>
 48         }
 49         
 50     <%for(int i=0; i<this.SourceTable.Columns.Count;i++)
 51     {                   
 52         int namelength=this.SourceTable.Columns[i].Name.Length;
 53         string colName=this.SourceTable.Columns[i].Name;
 54         if(colName == "ID" || colName == "IDNO") continue;
 55         string titleCaseColumName = colName.Substring(0,1).ToUpper() + colName.Substring(1,colName.Length-1);
 56         %>
 57         
 58         /// <summary>
 59         /// <%=this.SourceTable.Columns[i].Description%>
 60         /// </summary>
 61         <%if(this.SourceTable.Columns[i].IsPrimaryKeyMember){%>
 62         [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%>")]
 63         <%}%>
 64         <%else{%>
 65         <%if(this.SourceTable.Columns[i].IsForeignKeyMember)%>
 66         <%// 
 67         foreach(TableKeySchema tks in SourceTable.ForeignKeys)
 68         {
 69         // 
 70             
 71             foreach(MemberColumnSchema mcs in tks.ForeignKeyMemberColumns)
 72             {
 73                 
 74                 // 
 75                 if(mcs.Name == SourceTable.Columns[i].Name)
 76                 {
 77                     TableSchema ts= tks.PrimaryKeyTable;%>
 78         [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%>")]
 79                    <% break;
 80                 }
 81             }
 82         }
 83         %>
 84         <%else{%>
 85         [Column(ColumnName=Columns.<%=colName%>,FullName=Columns.<%=colName%>FullName,Index=<%=i%>,CType=typeof(<%=GetCSharpVariableType(this.SourceTable.Columns[i])%>),Description="<%=this.SourceTable.Columns[i].Description%>")]
 86         <%}%>
 87         <%}%>
 88         [DataMember(Order = <%=i%>)]
 89         public virtual <%=GetCSharpVariableType(this.SourceTable.Columns[i])%> <%=titleCaseColumName%>{get;set;}
 90     
 91     <%}%>        
 92     }    
 93 }
 94 
 95 <script runat="template">
 96 // Override the OutputFile property and assign our specific settings to it.
 97 [FileDialog(FileDialogType.Save, Title="Select Output File", Filter="C# Files (*.cs)|*.cs", DefaultExtension=".cs")]
 98 public override string OutputFile
 99 {
100     get {return base.OutputFile;}
101     set {base.OutputFile = value;}
102 }
103 private string _Namespace;
104 public string Namespace
105 {
106     get{return _Namespace;}    
107     set{_Namespace = value;}
108 }
109 
110 public bool GenreratePK{get;set;}
111 </script>
112 <script runat="template">
113 public string GetSqlDBType(string type)
114 {
115     switch(type)
116     {
117         case "int": return "SqlDbType.Int,";
118         case "bit": return "SqlDbType.Bit,";
119         case "bigint": return "SqlDbType.BigInt,";
120         case "tinyint": return "SqlDbType.TinyInt,";
121         case "nvarchar": return "SqlDbType.NVarChar,";
122         case "date": return "SqlDbType.Date,";
123         case "datetime": return "SqlDbType.DateTime,";
124         case "char": return "SqlDbType.Char,";
125         case "decimal": return "SqlDbType.Decimal,";
126         case "float": return "SqlDbType.Float,";
127         case "image": return "SqlDbType.Image,";
128         case "money": return "SqlDbType.Money,";
129         case "nchar": return "SqlDbType.NChar,";
130         case "ntext": return "SqlDbType.NText,";
131         case "real": return "SqlDbType.Real,";
132         case "smalldatetime": return "SqlDbType.SmallDateTime,";
133         case "smallint": return "SqlDbType.SmallInt,";
134         case "smallmoney": return "SqlDbType.SmallMoney,";
135         case "text": return "SqlDbType.Text,";
136         case "timestamp": return "SqlDbType.Timestamp,";
137         case "udt": return "SqlDbType.Udt,";
138         case "uniqueidentifier": return "SqlDbType.UniqueIdentifier,";
139         case "varbinary": return "SqlDbType.VarBinary,";
140         case "varchar": return "SqlDbType.VarChar,";
141         case "variant": return "SqlDbType.Variant,";
142         case "xml": return "SqlDbType.Xml,";
143         default : return "";
144     }
145     
146 }
147 public string GetCSharpVariableType(ColumnSchema column)
148 {
149     switch (column.DataType)
150     {
151         case DbType.AnsiString: re