大家好,首先我要声明的是:本人不曾对CodeSmith有过任何偏见,相反,在以后我做的几个项目中,
曾大量使用它来生成一些代码。但是由于本人最近在研究IOC类型框架Castle时,为了生成符合我要求的
数据表类型CS文件,发现去定义自己的CodeSmith模版太麻烦了,因此动了使用网页ASPX生成CS文件的念
头[可能有点BT],但是在完成这个程序后发现,这个东西非常好用。虽然目前只能进行SQLSERVER2000数
据库文件生成,但相信扩展到其它数据库类型并不难,但由于本人精力有限,目前只能做到此步。相信
有使用ORACLE,MYSQL等数据库的朋友完善它。
闲话少叙,马上开说。
如何找出当前数据库中的所有表字段和类型,有许多的方法,本人只用最常用的SQL语句来完成,如
下:
SELECT 表名=case when a.colorder=1 then d.name else '' end,
表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in ( SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid ))) then '√' else '' end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case when a.isnullable=1 then '√'else '' end,
默认值=isnull(e.text,''),
字段说明=isnull(g.[value],'')FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sysproperties g on a.id=g.id and a.colid=g.smallid left join sysproperties f on d.id=f.id and f.smallid=0 order by a.id,a.colorder
得到了相应的信息之后,开始生成CS文件,相关函数代码如下:
2 public string InterFaceFileContent(string TableName,string TableField)
3 {
4 string talbefield=null;
5 foreach(string m_tablefield in TableField.Split(';'))
6 {
7 if (m_tablefield.Trim()!="") talbefield+= m_tablefield+"{get;set;}\r\n ";
8 }
9
10 StringBuilder content=new StringBuilder();
11 content.Append("using System;\r\n");
12 content.Append("using System.Data;\r\n");
13 content.Append("\r\n");
14 content.Append("namespace Entity\r\n");
15 content.Append("{\r\n ");
16 content.Append("/// <summary>\r\n ");
17 content.Append("/// Entity 的摘要说明。\r\n ");
18 content.Append("/// </summary>\r\n ");
19 content.Append("public interface I"+TableName+"Entity\r\n ");
20 content.Append("{\r\n ");
21 content.Append(talbefield+"\r\n ");
22 content.Append("}\r\n\r\n ");
23
24 content.Append("public interface IDao_"+TableName+"Entity\r\n ");
25 content.Append("{\r\n ");
26 content.Append(string.Format("I{0}Entity Insert(I{0}Entity dse);\r\n ",TableName));
27 content.Append(string.Format("I{0}Entity Update(I{0}Entity dse);\r\n ",TableName));
28 content.Append("void Delete(string Condition);\r\n ");
29 content.Append("DataTable Find(string SqlString);\r\n ");
30 content.Append(string.Format("I{0}Entity Find(int id);\r\n ",TableName));
31 content.Append("}\r\n");
32 content.Append("}\r\n");
33 return content.ToString();
34 }
35
36
37 //写入实体文件内容
38 public string EntityFileContent(string TableName,string TableField)
39 {
40 string tablefield=null;
41 string tablefieldname=null; //字段名,例:ID,NAME
42 string insertvaluetablefield=null; //例如:ie.ID,ie.Name
43 string insertvaluetableindex=null; //例如:'{1}','{2}';
44 string updatevaluetablefield=null; //例如:ID='{0}',Name='{1}'
45 string createfunction=null; //重载构造函数体
46 int i=0;
47
48 //拼接出符合上面变量格式的字符串
49 foreach(string m_tablefield in TableField.Split(';'))
50 {
51 if (m_tablefield.Trim()!="")
52 {
53 StringBuilder field=new StringBuilder();
54 field.Append("private "+m_tablefield.Replace(" "," m_")+";\r\n ");
55 field.Append(string.Format("public {0}\r\n ",m_tablefield));
56 field.Append("{\r\n ");
57 field.Append("get { return m_"+m_tablefield.Split(' ')[1]+"; }\r\n ");
58 field.Append("set { m_"+m_tablefield.Split(' ')[1]+" = value; }\r\n ");
59 field.Append("}\r\n ");
60
61 tablefield+= field.ToString()+"\r\n ";
62
63 tablefieldname+=m_tablefield.Split(' ')[1]+",";
64 insertvaluetablefield+="ie."+m_tablefield.Split(' ')[1]+",";
65 insertvaluetableindex+="'{"+i.ToString()+"}',";
66 updatevaluetablefield+=m_tablefield.Split(' ')[1]+"='{"+i.ToString()+"}',";
67
68 createfunction+="m_"+m_tablefield.Split(' ')[1]+"="+m_tablefield.Split(' ')[1]+";\r\n ";
69
70 i++;
71 }
72 }
73 //去掉尾部的‘,’号
74 tablefieldname=tablefieldname.Substring(0,tablefieldname.Length-1);
75 insertvaluetablefield=insertvaluetablefield.Substring(0,insertvaluetablefield.Length-1);
76 insertvaluetableindex=insertvaluetableindex.Substring(0,insertvaluetableindex.Length-1);
77 updatevaluetablefield=updatevaluetablefield.Substring(0,updatevaluetablefield.Length-1);
78
79 StringBuilder content=new StringBuilder();
80 //实体类写入
81 #region
82 content.Append("using System;\r\n");
83 content.Append("using System.Data;\r\n");
84 content.Append("using DbService;\r\n");
85 content.Append("\r\n");
86 content.Append("namespace Entity\r\n");
87 content.Append("{\r\n ");
88 content.Append("/// <summary>\r\n ");
89 content.Append(string.Format("/// {0}_Entity 的摘要说明。\r\n ",TableName));
90 content.Append("/// </summary>\r\n ");
91 content.Append(string.Format("public class {0}:I{0}Entity\r\n ",TableName));
92 content.Append("{\r\n ");
93 //构造函数
94 content.Append(string.Format("public {0}()\r\n ",TableName));
95 content.Append("{\r\n ");
96 content.Append("// <summary>\r\n ");
97 content.Append("// TODO: 在此处添加构造函数逻辑\r\n ");
98 content.Append("// <summary>\r\n ");
99 content.Append("Console.WriteLine(\"create "+TableName+"Entity \");\r\n ");
100 content.Append("}\r\n ");
101 content.Append("\r\n ");
102 //重载构造函数
103 content.Append(string.Format("public {0}({1})\r\n ",TableName,TableField.Substring(0,TableField.Length-1).Replace(';',',')));
104 content.Append("{\r\n ");
105 content.Append("// <summary>\r\n ");
106 content.Append("// TODO: 在此处添加构造函数逻辑\r\n ");
107 content.Append("// <summary>\r\n ");
108 content.Append(createfunction+"\r\n ");
109 content.Append("}\r\n ");
110 content.Append("\r\n ");
111 //写入实体属性字段
112 content.Append(tablefield+"\r\n ");
113 content.Append("}\r\n ");
114 content.Append("\r\n ");
115 content.Append("\r\n ");
116
117 #endregion
118
119 //实体操作类写入
120 #region
121 content.Append(string.Format("public class {0}DAO:IDao_{0}Entity\r\n ",TableName));
122 content.Append("{\r\n ");
123
124 content.Append(string.Format("public I{0}Entity Insert(I{0}Entity ie)\r\n ",TableName));
125 content.Append("{\r\n ");
126 content.Append(string.Format("string SqlString=String.Format(\"Insert Into {0} ({1}) Values ({2})\",{3});\r\n ",TableName,tablefieldname,insertvaluetableindex,insertvaluetablefield));
127 content.Append("DbAccess.InsertSqlString(SqlString);\r\n ");
128 content.Append("return ie;\r\n ");
129 content.Append("}\r\n ");
130 content.Append("\r\n ");
131
132 //更新函数
133 content.Append(string.Format("public I{0}Entity Update(I{0}Entity ie)\r\n ",TableName));
134 content.Append("{\r\n ");
135 content.Append(string.Format("string SqlString=String.Format(\"Update {0} Set {1} \",{2});\r\n ",TableName,updatevaluetablefield,insertvaluetablefield));
136 content.Append("DbAccess.UpdateSqlString(SqlString);\r\n ");
137 content.Append("return ie;\r\n ");
138 content.Append("}\r\n ");
139 content.Append("\r\n ");
140 //删除函数