在昨天一個大學師弟，他問我能不能將LinqToSql文件轉化為創建表的TSql語 句，他是剛開始學習.NET，所以在網上下些示例看，但苦於沒有數據庫。所以就 有了這一篇博客，作為我的Code生成技術的CodeSimth的最後一篇示例。在下一 步Code 生成技術將轉到Microsoft的T4模板，Code生成技術目前完成的有 CodeDom,CodeSmith模板，高手請不要拍磚，請直接跳過。
在Linq2Sql的Dbml文件其實就是一個Xml文件，記錄著數據庫與生成 Linq2SqlCode的數據信息，所以轉化為TSql沒有什麼說的。我們需要提取其中的 數據庫信息，在轉化為我們的Tsql，在這裡建立了DBTable、DBColumn、 DBAssociation三個實體類：
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5
6 namespace DbmlToTable
7 {
8 public class DBTable
9 {
10
11 public DBTable()
12 {
13 Columns = new List<DBColumn>();
14 this.Associations = new List<DBAssociation>();
15 }
16
17 public string TableName
18 {
19 get;
20 set;
21 }
22
23 public List<DBColumn> Columns
24 {
25 get;
26 set;
27 }
28
29 public List<DBAssociation> Associations
30 {
31 get;
32 set;
33 }
34
35 }
36
37 public class DBColumn
38 {
39 public string Name
40 {
41 get;
42 set;
43 }
44
45 public string DBType
46 {
47 get;
48 set;
49 }
50
51 public bool IsPrimaryKey
52 {
53 get;
54 set;
55 }
56
57 public bool IsDbGenerated
58 {
59 get;
60 set;
61 }
62
63 public bool CanBeNull
64 {
65 get;
66 set;
67 }
68 }
69
70 public class DBAssociation
71 {
72 public string Name
73 {
74 get;
75 set;
76 }
77
78 public string ThisKey
79 {
80 get;
81 set;
82 }
83
84 public string OtherKey
85 {
86 get;
87 set;
88 }
89
90 public bool IsForeignKey
91 {
92 get;
93 set;
94 }
95 }
96
97 public class DBTableHlper
98 {
99 public static DBTable GetAssociationTable(List<DBTable> collection,string assName)
100 {
101
102 return collection.Find(t => t.Associations.Find(a => ! a.IsForeignKey && a.Name == assName) != null);
103 }
104 }
105 }
106
107
其中DBTableHlper是由於我的Codesimth是2.0版本的，不能用lamdam表達式 ，所以我將它編譯在程序集裡面。
建立了一個 將我們的dbml文件xml Document轉化為實體類輔助類：
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using System.Xml;
6 using System.Xml.Linq;
7
8 namespace DbmlToTable
9 {
10
11 public interface IDbTableCollectionHelper
12 {
13 List<DBTable> Transport(XElement element);
14 }
15
16 public class DbTableCollectionHelper : IDbTableCollectionHelper
17 {
18 #region IDbTableCollectionHelper 成員
19
20 public List<DBTable> Transport (XElement element)
21 {
22 List<DBTable> collection = new List<DBTable> ();
23 var tables = element.Elements (XName.Get("Table", "http://schemas.microsoft.com/linqtosql/dbml/2007%22));
24 foreach (var tab in tables)
25 {
26 DBTable t = new DBTable() { TableName = tab.Attribute("Name").Value };
27 var cols = tab.Element(XName.Get ("Type", "http://schemas.microsoft.com/linqtosql/dbml/2007% 22)).Elements(XName.Get(%22Column%22, "http://schemas.microsoft.com/linqtosql/dbml/2007%22));
28 foreach (var col in cols)
29 {
30 DBColumn c = new DBColumn()
31 {
32 CanBeNull = col.Attribute("CanBeNull") != null ? col.Attribute("CanBeNull").Value.ToLower() == "true" : false,
33 DBType = col.Attribute("DbType") != null ? col.Attribute("DbType").Value : "",
34 IsDbGenerated = col.Attribute("IsDbGenerated") != null ? col.Attribute("IsDbGenerated").Value.ToLower() == "true" : false,
35 IsPrimaryKey = col.Attribute("IsPrimaryKey") != null ? col.Attribute ("IsPrimaryKey").Value.ToLower() == "true" : false,
36 Name = col.Attribute ("Name") != null ? col.Attribute("Name").Value : ""
37 };
38 t.Columns.Add(c);
39 }
40
41 var ass = tab.Element(XName.Get("Type", "http://schemas.microsoft.com/linqtosql/dbml/2007%22)).Elements (XName.Get(%22Association%22, "http://schemas.microsoft.com/linqtosql/dbml/2007%22));
42 foreach (var item in ass)
43 {
44 DBAssociation a = new DBAssociation()
45 {
46 Name = item.Attribute("Name") != null ? item.Attribute("Name").Value : "",
47 OtherKey = item.Attribute("OtherKey") != null ? item.Attribute("OtherKey").Value : "",
48 ThisKey = item.Attribute ("ThisKey") != null ? item.Attribute("ThisKey").Value : "",
49 IsForeignKey = item.Attribute("IsForeignKey") != null ? item.Attribute ("IsForeignKey").Value.ToLower() == "true" : false
50 };
51 t.Associations.Add(a);
52 }
53 collection.Add(t);
54 }
55 return collection;
56 }
57
58 #endregion
59 }
60 }
61
62
在轉化為我們的實體類，我們剩下的就是編寫我們的CodeSmith模板了（更多 知識可以參考CodeSmith模板）：
1 <%@ CodeTemplate Language="C#" TargetLanguage="Text" Src="" Inherits="" Debug="False" Description="Template description here." %>
2
3 <%@ Import NameSpace="System" %>
4 <%@ Import NameSpace="System.Xml" %>
5 <%@ Import NameSpace="System.Text" %>
6 <%@ Import NameSpace="System.Collections.Generic" %>
7 <%@ Assembly Name="DbmlToTable" %>
8
9 --Code By Wolf
10 <script runat="template">
11 private List<DbmlToTable.DBTable> _DbTableCollection;
12 public List<DbmlToTable.DBTable> DbTableCollection
13 {
14 get
15 {
16 return _DbTableCollection;
17 }
18 set
19 {
20 _DbTableCollection=value;
21 }
22 }
23
24 public string GeneratorTableSql (List<DbmlToTable.DBTable> collection)
25 {
26 StringBuilder sb = new StringBuilder();
27 StringBuilder sbAssocation = new StringBuilder();
28 foreach (DbmlToTable.DBTable item in collection)
29 {
30 List<string> cols = new List<string>();
31 foreach (DbmlToTable.DBColumn col in item.Columns)
32 {
33 cols.Add(string.Format("{0} {1} {2} ", col.Name, col.DBType, col.IsPrimaryKey ? "PRIMARY KEY " : ""));
34 }
35 sb.AppendFormat("\r\nCREATE TABLE {0} \r\n(\r\n{1}\r\n)", item.TableName, string.Join(",\r\n", cols.ToArray()));
36
37 foreach (DbmlToTable.DBAssociation ass in item.Associations)
38 {
39 if (ass.IsForeignKey)
40 {
41 DbmlToTable.DBTable tab = DbmlToTable.DBTableHlper.GetAssociationTable (collection,ass.Name);
42 if (tab != null)
43 {
44 sbAssocation.AppendLine();
45 sbAssocation.AppendFormat(@"ALTER TABLE {0} WITH NOCHECK ADD CONSTRAINT {1} FOREIGN KEY({2}) REFERENCES {3} ({4})",
46 item.TableName, "FK_" + ass.Name, ass.ThisKey, tab.TableName, ass.OtherKey);
47 }
48 }
49 }
50 }
51
52 return sb.ToString() + "\r\n" + sbAssocation.ToString();
53 }
54 </script>
55 <%= this.GeneratorTableSql(_DbTableCollection) % >
56
57
在codeSimth中我們建立了一個集合屬性傳遞實體類DBTable和一個轉化TSql 輔助方法.
在控制台調用編譯模板以及輸出：
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5
6 namespace DbmlToTable
7 {
8 class Program
9 {
10 static void Main(string[] args)
11 {
12 IDbTableCollectionHelper helper = new DbTableCollectionHelper();
13 List<DBTable> collection = helper.
14 Transport(System.Xml.Linq.XElement.
15
16 Load (@"xxpath\MultipleDocument.Data\MultipleDocumentDB.dbml"));
17
1 8 CodeSmith.Engine.CodeTemplate template = CodeSimthTemplateHelper.
19 CompileTemplate(@"DBMLToTable.cst", w => Console.WriteLine (w));
20 if (template != null)
21 {
22 CodeSimthTemplateHelper.AddPropertyParams(template, new { DbTableCollection = collection });
23 string str = template.RenderToString();
24 Console.WriteLine(str);
25 //System.IO.File.AppendAllText(@"D:\1.sql", str);
26 }
27 Console.Read();
28 }
29
30 }
31
32
33
34
在CodeSimth中就是這麼簡單，生成相應的模板代碼（個人理解CodeSmith就 是把代碼作為字符串輸出）。
在上面到我的CodeSmith模板編譯輔助類，在上一篇通過代碼生成機制實現強 類型編程-CodeSmith版也有，在這裡也附帶上：需要引用 CodeSmith.Engine.dll.
1 using System;
2
3 using System.Collections.Generic;
4
5 using System.Linq;
6
7 using System.Text;
8
9 using CodeSmith.Engine;
10
11 using Wolf.NameValueDictionary;
12
13 namespace DbmlToTable
14
15 {
16
17 public class CodeSimthTemplateHelper
18
19 {
20
21 public static CodeTemplate CompileTemplate(string templateName, Action errorWriter)
22
23 {
24
25 CodeTemplateCompiler compiler = new CodeTemplateCompiler (templateName); compiler.Compile();
26
27 if (compiler.Errors.Count == 0)
28
29 {
30
31 return compiler.CreateInstance ();
32
33 }
34
35 else
36
37 {
38
39 for (int i = 0; i < compiler.Errors.Count; i++)
40
41 {
42
43 errorWriter(compiler.Errors[i].ToString());
44
45 }
46
47 return null;
48
49 }
50
51 }
52
53
54
55 public static void AddPropertyParams(CodeTemplate template,object param)
56
57 {
58
59 NameValueDictionary dict = new NameValueDictionary<object>(param);
60
61 AddPropertyParams(template, dict);
62
63 }
64
65
66
67 public static void AddPropertyParams (CodeTemplate template, NameValueDictionary<object> param)
68
69 {
70
71 NameValueDictionary<object> dict = new NameValueDictionary<object>(param);
72
73 foreach (var item in dict.Keys)
74
75 {
76
77 template.SetProperty(item, dict[item]);
78
79 }
80
81 }
82
83 }
84
85 }
86
