Dbml文件提取建表TSql-CodeSmith
2010-09-27 12:27 破狼 阅读(3302) 评论(2) 编辑 收藏 举报在昨天一个大学师弟，他问我能不能将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
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
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
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
18 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
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
18 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
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
作者：破 狼
出处：http://www.cnblogs.com/whitewolf/
本文版权归作者，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。该文章也同时发布在我的独立博客中-个人独立博客、博客园--破狼和51CTO--破狼。
