开篇背景
我习惯在写表的创建脚本时将注释直接写在脚本里，比如
/*账套*/
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(@"/[*]+([^*]*)[*]+/[ 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(" ", 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(@"/[*]+([^*]*)[*]+/[ 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}]; "; 16 private const string ColumnDescriptionCrateSqlFormat = "EXEC sp_addextendedproperty 'MS_Description','{0}','user',dbo,'table',[{1}],'column','{2}'; "; 17 private const string TableDescriptionDropSqlFormat = "EXEC sp_dropextendedproperty 'MS_Description','user',dbo,'table',[{0}]; "; 18 private const string ColumnescriptionDropSqlFormat = "EXEC sp_dropextendedproperty 'MS_Description','user',dbo,'table',[{0}],'column','{1}'; "; 19 private const string CheckTableExistsSqlFormat = "IF OBJECT_ID(N'{0}',N'U') IS NOT NULL BEGIN "; 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(" ", list); 85 } 86 87 } 88 89 }
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: return "string"; 152 case DbType.AnsiStringFixedLength: return "string"; 153 case DbType.Binary: return "byte[]"; 154 case DbType.Boolean: return "bool?"; 155 case DbType.Byte: return "byte"; 156 case DbType.Currency: return "decimal?"; 157 case DbType.Date: return "DateTime?"; 158 case DbType.DateTime: return "DateTime?"; 159 case DbType.Decimal: return "decimal?"; 160 case DbType.Double: return "double?"; 161 case DbType.Guid: return "Guid"; 162 case DbType.Int16: return "short?"; 163 case DbType.Int32: return "int?"; 164 case DbType.Int64: return "long?"; 165 case DbType.Object: return "object"; 166 case DbType.SByte: return "sbyte"; 167 case DbType.Single: return "float?"; 168 case DbType.String: return "string"; 169 case DbType.StringFixedLength: return "string"; 170 case DbType.Time: return "TimeSpan"; 171 case DbType.UInt16: return "ushort?"; 172 case DbType.UInt32: return "uint?"; 173 case DbType.UInt64: return "ulong?"; 174 case DbType.VarNumeric: return "decimal?"; 175 default: 176 { 177 return "__UNKNOWN__" + column.NativeType; 178 } 179 } 180 } 181 </script>