在昨天一个大学师弟,他问我能不能将LinqToSql文件转化为创建表的TSql语句,他是刚开始学习.NET,所以在网上下些示例看,但苦于没有数据库。所以就有了这一篇博客,作为我的Code生成技术的CodeSimth的最后一篇示例。在下一步Code 生成技术将转到Microsoft的T4模板,Code生成技术目前完成的有CodeDom,CodeSmith模板,高手请不要拍砖,请直接跳过。
在Linq2Sql的Dbml文件其实就是一个Xml文件,记录着数据库与生成Linq2SqlCode的数据信息,所以转化为TSql没有什么说的。我们需要提取其中的数据库信息,在转化为我们的Tsql,在这里建立了DBTable、DBColumn、DBAssociation三个实体类:
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转化为实体类辅助类:
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模板):
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辅助方法.
在控制台调用编译模板以及输出:
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.
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--破狼。http://www.cnblogs.com/whitewolf/archive/2010/09/27/1836731.html