|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351 |
using
System;using
System.Collections.Generic;using
System.ComponentModel;using
System.Data;using
System.Data.SqlClient;using
System.Drawing;using
System.IO;using
System.Linq;using
System.Text;using
System.Threading.Tasks;using
System.Windows.Forms;namespace
代码生成器{ public
partial class Form1 : Form { public
Form1() { InitializeComponent(); } //for(int i=0;i<4;i++){ //} string
kongge1 = "\t"; string
kongge2 = "\t\t"; string
kongge3 = "\t\t\t"; string
kongge4 = "\t\t\t\t"; StringBuilder SB = new
StringBuilder(); #region 把数据库类型转化为.net类型 private
static string ToNetType(string
dataType) { switch
(dataType) { case
"int": return
"int"; break; case
"nvarchar": case
"varchar": case
"char": case
"nchar": return
"string"; case
"bit": return
"bool"; case
"datetime": return
"DateTime"; default: return
"object"; } } #endregion #region 数据库连接操作 public
DataTable ExecuteDataTable(string
cmdText, params
SqlParameter[] parameters) //不能写成static { using
(SqlConnection conn = new
SqlConnection(txtConnStr.Text)) { //WhetherCon(txtConnSr.Text);//待优化 /************此处写等待用户输入的代码********************/ conn.Open(); using
(SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = cmdText; cmd.Parameters.AddRange(parameters); DataTable dt = new
DataTable(); SqlDataAdapter adapter = new
SqlDataAdapter(cmd); adapter.Fill(dt); return
dt; } } } #endregion #region "连接"按钮事件 private
void btnConnect_Click(object
sender, EventArgs e) { DataTable dt = ExecuteDataTable("select * from information_schema.tables"); foreach
(DataRow row in
dt.Rows) { string
tablename = (string)row["TABLE_NAME"]; clbFile.Items.Add(tablename); } } #endregion #region "生成"按钮事件；生成文件选项 private
void btnGo_Click(object
sender, EventArgs e) { foreach
(string
tablename in
clbFile.CheckedItems) //遍历，获得用户所勾选的表的名字 { if
(checkBox1.Checked == true)/*★★*/ { CreateModel(tablename);//生成model SB.AppendLine("模型层生成完成\r\n"); } if
(checkBox2.Checked == true) { CreateDAL(tablename);//生成DAL SB.AppendLine("数据访问层生成完成\r\n"); } if
(checkBox3.Checked == true) { CreateBLL(tablename);//生成BLL SB.AppendLine("业务逻辑层生成完成\r\n"); } txtShow.Text=SB.ToString(); } } #endregion #region 生成Model /// <summary> /// 生成Model /// </summary> /// <param name="tablename"></param> private
void CreateModel(string
tablename) { DataTable dtCols = ExecuteDataTable("select * from information_schema.columns where table_name=@tablename", new
SqlParameter("tablename", tablename));//得到选中的表 StringBuilder sb = new
StringBuilder();//用来拼接字符串的对象 sb.AppendLine("using System;"); sb.AppendLine("using System.Collections.Generic;"); sb.AppendLine("using System.Linq;"); sb.AppendLine("using System.Text;"); sb.AppendLine("using System.Threading.Tasks;\r\n"); sb.AppendLine("namespace "
+ textBox1.Text.Trim()+ ".Model{"); sb.AppendLine(kongge1+"class "+tablename); sb.AppendLine(kongge1+"{"); foreach(DataRow row in
dtCols.Rows) /*★参数中数据库类型和.net的数据类型之间的转换*/ //遍历每行，得到要用的参数，并赋给其它变量 { string
colName = (string)row["Column_Name"]; string
dataType = (string)row["Data_Type"]; string
netType = ToNetType(dataType); sb.AppendLine(kongge2 + "public"
+ " "
+ netType + " "
+ colName + "{get;set;}"); //MessageBox.Show((string)row["Column_Name"] + (string)row["Data_Type"]); } sb.AppendLine(kongge1+"}"); sb.AppendLine( "}"); File.WriteAllText(textBox2.Text + @"\"
+ tablename + "Model.cs", sb.ToString()); } #endregion /*得到数据库表列名的第二种方式：*************************************/ //private static string[] GetColumnNames(DataTable table) //{ // string[] colnames = new string[table.Columns.Count]; // for (int i = 0; i < table.Columns.Count; i++) // { // DataColumn dataCol = table.Columns[i]; // colnames[i] = dataCol.ColumnName; // } // return colnames; //} //private static string[] GetParamColumnNames(DataTable table){ // string[] colnames=new string[table.Columns.Count]; // for(int i=0;i<table.Columns.Count;i++){ // DataColumn dataCol=table.Columns[i]; // colnames[i]="@"+dataCol.ColumnName; // } // return colnames; //} /********************************************************************/ #region 提取方法:getTableFromDataBase()、 GetParametersFromTable() private
string[] getTableFromDataBase(string
tablename) { //做复杂功能点的时候先对一些条件做一些假设，把问题逐步分解 //假设：主键名就是Id,并且是int类型、自动增长 DataTable dtCols = ExecuteDataTable("select * from information_schema.columns where Column_Name<>‘Id‘ and table_name=@tablename", new
SqlParameter("tablename", tablename)); string[] colnames = new
string[dtCols.Rows.Count]; for
(int
i = 0; i < dtCols.Rows.Count; i++)//遍历数据表中的各行 { DataRow row = dtCols.Rows[i];//得到一行 string
colname = (string)row["Column_Name"]; //得到该行列为“Column_Name”信息并将其存于colname变量中 colnames[i] = colname;//▲colnames数组中存放的是数据表列名信息 } return
colnames;/*▲返回数组，可以直接返回数组名字*/ } private
string[] GetParametersFromTable(string
tablename)//将列名数组变成DAL的sql语句的参数信息 { DataTable dtCols = ExecuteDataTable("select * from information_schema.columns where Column_Name<>‘Id‘ and table_name=@tablename", new
SqlParameter("tablename", tablename)); string[] colnames = new
string[dtCols.Rows.Count]; for
(int
i = 0; i < dtCols.Rows.Count; i++) { DataRow row = dtCols.Rows[i]; string
colname = (string)row["Column_Name"]; colnames[i] = "@"
+ colname; } return
colnames; } #endregion #region 生成DAL：CreateDAL /// <summary> /// 生成DAL：CreateDAL /// </summary> /// <param name="tablename"></param> private
void CreateDAL(string
tablename) { DataTable dtCols = ExecuteDataTable("select * from information_schema.columns where Column_Name<>‘Id‘ and table_name=@tablename", new
SqlParameter("tablename", tablename)); string[] parameters = GetParametersFromTable(tablename); string[] colnames = getTableFromDataBase(tablename); StringBuilder sb = new
StringBuilder(); /************命名空间***************/ sb.AppendLine("using System;"); sb.AppendLine("using System.Collections.Generic;"); sb.AppendLine("using System.Data;"); sb.AppendLine("using System.Data.SqlClient;"); sb.AppendLine("using System.Linq;"); sb.AppendLine("using System.Text;"); sb.AppendLine("using System.Threading.Tasks;"); sb.AppendLine("using System.Windows.Forms;"); sb.AppendLine("using "
+ textBox1.Text.Trim() + ".Model;\r\n"); sb.AppendLine("namespace "
+ textBox1.Text.Trim() + ".DAL{"); sb.AppendLine(kongge1+"class "+tablename+"DAL{"); /************AddNew()方法***************/ sb.AppendLine(kongge2+"public int AddNew("
+ tablename + " model){"); sb.Append(kongge3+"object obj = SqlHelper.ExecuteScalar("); sb.Append("\"intsert into "
+ tablename + "("
+ string.Join(",", colnames) + ") values ("
+ string.Join(",", parameters) + ");select @@identity\""); /*@只能转义“\”,这里必须用“\”来转义*/ /*▲string.Join方法：第一个参数是分隔符；第二个参数参数字符串*/ /*▲拼：“(Age,Name) values(@Age,@Name)”形式的参数*/ //拼参数 foreach
(string
colname in
colnames) { sb.Append(",new SqlParameter(\""
+ colname + "\",model."
+ colname + ")"); } sb.AppendLine(");"); sb.AppendLine(kongge3+"return Convert.ToInt32(obj);"); sb.AppendLine(kongge2+"}"); /************拼Delete()方法***************/ sb.AppendLine(kongge2+"public int Delete(int id){"); sb.Append(kongge3+"return SqlHelper.ExecuteNonQuery("); sb.Append("\"delete from "
+ tablename + " where id=@id\",new SqlParameter("); sb.AppendLine("\"id\",id));");//★字符串的拼接，这里有点麻烦要注意 sb.AppendLine(kongge2+"}"); /************拼Update()方法***************/ /*★拼：“Name=@Name,Age=@Age”形式的参数列表******/ sb.AppendLine(kongge2+"public int Update("
+ tablename + " model){"); sb.Append(kongge3+"return SqlHelper.ExecuteNonQuery").Append("(\"update "
+ tablename + " set "); foreach
(string
colname in
colnames) { sb.Append(colname + "=@"
+ colname + ","); } sb.Append("where id=@id\""); foreach
(string
colname in
colnames) { sb.Append(",new SqlParameter(\""
+ colname + "\",model."
+ colname + ")"); } sb.AppendLine(");"); sb.AppendLine(kongge2+"}"); sb.AppendLine("\n"); /************拼Get()方法***************/ //▲where后面有bug，有个逗号不好处理 sb.AppendLine(kongge2+"public "
+ tablename + " Get(int id){"); sb.AppendLine(kongge3+"DataTable dt = SqlHelper.ExecuteDataTable(\"select * from "
+ tablename + " where id=@id\",new SqlParameter(\"id\",id));"); sb.AppendLine(kongge3+"if (dt.Rows.Count <= 0) {return null;}"); sb.AppendLine(kongge3+"else if(dt.Rows.Count==1){"); sb.AppendLine(kongge4+tablename + " model=new "
+ tablename + "();"); sb.AppendLine(kongge4+"DataRow row = dt.Rows[0];"); foreach
(DataRow row in
dtCols.Rows) { string
colName = (string)row["Column_Name"]; string
dataType = (string)row["Data_Type"]; string
netType = ToNetType(dataType); sb.AppendLine(kongge4+"model."
+ colName + "=("
+ netType + ")row[\""
+ colName + "\"];"); } sb.AppendLine(kongge4+"return model;"); sb.AppendLine(kongge3+"}else{"); sb.AppendLine(kongge4+"throw new Exception(\"出现多条id值相同的数据\");"); sb.AppendLine(kongge3+"}"); sb.AppendLine(kongge2+"}"); sb.AppendLine(kongge1+"}"); sb.AppendLine("}"); //MessageBox.Show(sb.ToString()); File.WriteAllText(textBox2.Text + @"\"
+ tablename + "DAL.cs", sb.ToString()); } #endregion #region Default生成的BLL private
void CreateBLL(string
tablename) { StringBuilder sb = new
StringBuilder(); sb.AppendLine("using System;"); sb.AppendLine("using System.Collections.Generic;"); sb.AppendLine("using System.Data;"); sb.AppendLine("using System.Data.SqlClient;"); sb.AppendLine("using System.Linq;"); sb.AppendLine("using System.Text;"); sb.AppendLine("using System.Threading.Tasks;"); sb.AppendLine("using "
+ tablename + ".DAL;"); sb.AppendLine("using "
+ tablename + ".Model;\r\n"); sb.AppendLine("namespace "
+ textBox1.Text.Trim()+ ".BLL;{"); sb.AppendLine(kongge1+"class "
+ tablename + "BLL{"); sb.AppendLine(kongge2+"pulic int AddNew("
+ tablename + " model){"); sb.AppendLine(kongge3+"return new "
+ tablename + "DAL().AddNew(model);"); sb.AppendLine(kongge2+"}"); sb.AppendLine(kongge2+"pulic int Delete(int id){");//假设Id是主键且必须有的 sb.AppendLine(kongge3+"return new "
+ tablename + "DAL().Delete(id);"); sb.AppendLine(kongge2+"}"); sb.AppendLine(kongge2+"pulic int Update("
+ tablename + " model){"); sb.AppendLine(kongge3+"return new "
+ tablename + "DAL().Update(model);"); sb.AppendLine(kongge2+"}"); sb.AppendLine(kongge2+"pulic int Get(int id){"); sb.AppendLine(kongge3+"return new "
+ tablename + "DAL().Get(model)"); sb.AppendLine(kongge2+"}"); sb.AppendLine(kongge1+"}"); sb.AppendLine("}"); File.WriteAllText(textBox2.Text + @"\"
+ tablename + "BLL.cs", sb.ToString()); } #endregion private
void textBox1_TextChanged(object
sender, EventArgs e) { } /*点击按钮弹出“保存对话框”***************************************/ private
void btnRoutine_Click(object
sender, EventArgs e) { //SaveFileDialog saveFileDialog = new SaveFileDialog(); //if (saveFileDialog.ShowDialog() == DialogResult.OK) //{ // string localFilePath = saveFileDialog.FileName.ToString();//获得文件路径 // textBox2.Text = localFilePath; // saveFileDialog.RestoreDirectory = true;//保存对话框是否记忆上次打开的目录 // // Directory.CreateDirectory(textBox2.Text); //} /*以上是保存对话框，下面是浏览对话框*/ FolderBrowserDialog folderBrowserDialog1 = new
FolderBrowserDialog(); //使用文件对话框查找文件夹创建的路径 if
(folderBrowserDialog1.ShowDialog() == DialogResult.OK) { string
path = folderBrowserDialog1.SelectedPath; //获取用户选中路径 textBox2.Text = path; //显示路径 /*▲Bug:路径不能手写。因为先点击按钮执行该函数，后面改变TextBox与该函数无关*/ } } }} |
App.config
<?xml version="1.0" encoding="utf-8" ?> <configuration> <startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" /> </startup> <connectionStrings> <add name="connstr" connectionString="Data Source=.\;Initial Catalog=CallCenter;Integrated Security=True"/> </connectionStrings> </configuration
简单三层代码生成器（C#）（传智播客整理）,布布扣,bubuko.com
原文：http://www.cnblogs.com/heyuchi-2013/p/3624217.html