背景
由于前一阵子有个项目,需要用到将数据库里面的表生成实体类。虽然不是什么很麻烦的事情,但是也经
不起量大啊!由于没有找到一个适合的代码生成工具,后来没办法只好写了一段程序,将数据库的字段用字符串拼起来组成要用的类然后输出。办法随笨了点,但也
还实用。但是太过死板,不方便修改。后来想用XML +
XLST可以实现用模板来定制这些类,这样应该好多了。于是,这两天就简单的写了个程序,结果感觉还不错。
一, 获取数据库中的字段和数据类型
我这里是用了两个系统存储过程sp_columns和sp_pkeys来获取的,当然你也可以使用sp_help来获取这些值。
二,将获取的列和数据类型转成XML数据。
只需要写一段数据库访问程序调用系统存储过程sp_columns和sp_pkeys并将你要的生成代码的数据表的名字传给它们的参数@table_name就可以了。
部分代码:
1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
2
Dim stbld As New StringBuilder
3
Dim stbldcol As New StringBuilder
4
Dim stbldpkey As New StringBuilder
5![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
6
Dim tblo As New tableoperator
7
Dim ds As DataSet
8
Dim i As Integer
9![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
10
'
11
ds = tblo.getTableColumns()
12![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
13
'field
14
stbld.Append("<?xml version=""1.0"" encoding=""utf-8"" ?>" + vbCrLf)
15
stbld.Append("<Schema>" + vbCrLf)
16
stbld.Append(Space(4) + "<TableName value=""" + tblo.tablename + """ />" + vbCrLf)
17
stbld.Append(Space(4) + "<FIELDS>" + vbCrLf)
18
For i = 0 To ds.Tables(0).Rows.Count - 1
19
stbld.Append(Space(8) + "<FIELD Name=""" + ds.Tables(0).Rows(i).Item("COLUMN_NAME") + """ Type = ""System.String""/>" + vbCrLf)
20
Next
21
stbld.Append(Space(4) + "</FIELDS>" + vbCrLf)
22![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
23
'query
24
stbldcol.Append(Space(4) + "<Query>" + vbCrLf)
25
For i = 0 To ds.Tables(0).Rows.Count - 1
26
stbldcol.Append(Space(8) + "<Command Name=""SelectBy" + ds.Tables(0).Rows(i).Item("COLUMN_NAME") + """ ReturnMode = ""Multiple"">" + vbCrLf)
27
stbldcol.Append(Space(12) + "<FIELD Name=""" + ds.Tables(0).Rows(i).Item("COLUMN_NAME") + """ Type = ""System.String""/>" + vbCrLf)
28
stbldcol.Append(Space(8) + "</Command>" + vbCrLf)
29
Next
30
stbldcol.Append(Space(4) + "</Query>" + vbCrLf)
31![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
32
ds.Clear()
33
ds = tblo.getPrimaryKeys
34
'primary keys
35
stbldpkey.Append(Space(4) + "<PrimaryKeys>" + vbCrLf)
36
For i = 0 To ds.Tables(0).Rows.Count - 1
37
stbldpkey.Append(Space(8) + "<FIELD Name=""" + ds.Tables(0).Rows(i).Item("COLUMN_NAME") + """ Type = ""System.String""/>" + vbCrLf)
38
Next
39
stbldpkey.Append(Space(4) + "</PrimaryKeys>" + vbCrLf)
40![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
41
stbld.Append(stbldpkey)
42
stbld.Append(stbldcol)
43![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
44
stbld.Append("</Schema>" + vbCrLf)
45
TextBox1.Text = stbld.ToString
46
End Sub
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
(注:这里为了显示xml方便所以生成了一个完整的xml文件,但在后面使用的时候请将第14,15,44行注释掉)
当程序运行时,生成的的XML数据是这样就可以了,当然你也可以根据自己的需要生成自己需要的格式:
<FIELDS></FIELDS>部分会生成实体类的属性;
<PrimaryKeys></PrimaryKeys>部分会用在实体类的New函数中;
<Query></Query>部分用来配置将来要生成DO代码的增删改以及查询用的函数。
<Command><Command>里面的FIELD用作增删改查询中需要的参数和参数对应的字段。