自动将表生成实体类...(以后将陆续推出自动生成数据访问层,业务逻辑层,WCF等)
最近做项目,发现很多事情都是重复劳动,突然自己想写一个适合自己的工具,自动生成代码....
做起来也比较简单,以下是一些关键性的代码,现在暂时推出直接将表生成实体类,同时自动添加注释...
1
using System;
2using System.Collections.Generic;
3using System.Windows.Forms;
4using Microsoft.Data.ConnectionUI;
5
6namespace CodeBuilder
7
{
8
static class Program
9
{
10 /// <summary>
11 /// 应用程序的主入口点。
12
/// </summary>
13 [STAThread]
14 static void Main()
15 {
16 Application.EnableVisualStyles();
17 Application.SetCompatibleTextRenderingDefault(false);
18
19 DataConnectionDialog dialog = new DataConnectionDialog();
20 dialog.DataSources.Add(DataSource.SqlDataSource);
21 dialog.DataSources.Add(DataSource.OdbcDataSource);
22 dialog.DataSources.Add(DataSource.OracleDataSource);
23 dialog.DataSources.Add(DataSource.AccessDataSource);
24
25 dialog.SelectedDataSource = DataSource.SqlDataSource;
26 dialog.SelectedDataProvider = DataProvider.SqlDataProvider;
27 //
28 //如果确认，返回连接字符串，否则返回空
29 //
30 DataConnectionDialog.Show(dialog);
31 if (dialog.DialogResult == DialogResult.OK)
32 {
33 MainForm.ConnectionString = dialog.ConnectionString;
34 Application.Run(new MainForm());
35 }
36 else
37 {
38 Application.Exit();
39 }
40
41 }
42
43
using System;2
using System.Collections.Generic;3
using System.Windows.Forms;4
using Microsoft.Data.ConnectionUI;5
6
namespace CodeBuilder7
{
8
static class Program9
{
10
/// <summary>11
/// 应用程序的主入口点。12
/// </summary>13
[STAThread]14
static void Main()15
{16
Application.EnableVisualStyles();17
Application.SetCompatibleTextRenderingDefault(false);18
19
DataConnectionDialog dialog = new DataConnectionDialog();20
dialog.DataSources.Add(DataSource.SqlDataSource);21
dialog.DataSources.Add(DataSource.OdbcDataSource);22
dialog.DataSources.Add(DataSource.OracleDataSource);23
dialog.DataSources.Add(DataSource.AccessDataSource);24
25
dialog.SelectedDataSource = DataSource.SqlDataSource;26
dialog.SelectedDataProvider = DataProvider.SqlDataProvider;27
//28
//如果确认，返回连接字符串，否则返回空29
//30
DataConnectionDialog.Show(dialog);31
if (dialog.DialogResult == DialogResult.OK)32
{33
MainForm.ConnectionString = dialog.ConnectionString;34
Application.Run(new MainForm());35
}36
else37
{38
Application.Exit();39
}40
41
}42
43
1using System;
2using System.Collections.Generic;
3using System.ComponentModel;
4using System.Data;
5using System.Data.Common;
6using System.Data.SqlClient;
7using System.Drawing;
8using System.Text;
9using System.Windows.Forms;
10
11
12namespace CodeBuilder
13{
14 public partial class MainForm : Form
15 {
16 public static string ConnectionString { get; set; }
17 SqlConnection Conn = new SqlConnection(ConnectionString);
18 private string RootNodeTag = "root";
19 private string RootNodeText = "数据库";
20
21
22 public MainForm()
23 {
24 InitializeComponent();
25 }
26
27 private void MainForm_Load(object sender, EventArgs e)
28 {
29 Conn.Open();
30 List<Table> Tables = GetTables();
31 TreeNode ParentNode = new TreeNode();
32 ParentNode.Text = RootNodeText;
33 ParentNode.Tag = RootNodeTag;
34 foreach (Table T in Tables)
35 {
36 TreeNode TN = new TreeNode();
37 TN.Text = T.TableName;
38 TN.Tag = T;
39 TN.ForeColor = Color.Blue;
40 foreach (TableSchema TS in T.TableSchemas)
41 {
42 TreeNode TN2 = new TreeNode();
43 TN2.Text = string.Format("{0}[{1}]", TS.ColumnName, TS.SqlTypeName);
44 TN2.Tag = TS;
45 TN.Nodes.Add(TN2);
46 }
47 ParentNode.Nodes.Add(TN);
48 }
49
50
51 this.TableTreeView.Nodes.Add(ParentNode);
52 }
53
54
55 /// <summary>
56 /// 返回该表的主键
57 /// </summary>
58 /// <param name="TableName">需要获取主健的表名</param>
59 /// <returns></returns>
60 private List<string> GetTabkeKeys(string TableName)
61 {
62 SqlCommand command = new SqlCommand("sp_pkeys", Conn);
63 command.CommandType = CommandType.StoredProcedure;
64 SqlParameter para = new SqlParameter("table_name", TableName);
65 command.Parameters.Add(para);
66 List<string> TabkeKeys = new List<string>();
67 using (SqlDataReader dr = command.ExecuteReader())
68 {
69 while (dr.Read())
70 {
71 TabkeKeys.Add((string)dr["COLUMN_NAME"]);
72 }
73 }
74 command.Dispose();
75 return TabkeKeys;
76 }
77
78
79 private List<Table> GetTables()
80 {
81 SqlCommand command = new SqlCommand("SELECT Name FROM sys.tables", Conn);
82 List<Table> Tables = new List<Table>();
83 using (SqlDataReader dr = command.ExecuteReader())
84 {
85 while (dr.Read())
86 {
87 Table T = new Table();
88 T.TableName = (string)dr["Name"];
89 Tables.Add(T);
90 }
91 }
92 foreach (Table T in Tables)
93 {
94 T.TableKeys = GetTabkeKeys(T.TableName);
95 T.TableSchemas = GetTableSchemas(T.TableName);
96 }
97 command.Dispose();
98 return Tables;
99 }
100
101
102 private List<TableSchema> GetTableSchemas(string TableName)
103 {
104 string sql = @"SELECT sys.columns.name AS ColumnName,
105 type_name(sys.columns.system_type_id) AS SqlTypeName,
106 sys.columns.max_length AS MaxLength,
107 sys.columns.is_nullable AS IsNullable,
108 (select count(*) from sys.identity_columns where sys.identity_columns.object_id = sys.columns.object_id and sys.columns.column_id = sys.identity_columns.column_id) as IsIdentity,
109 (select value from sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id and sys.extended_properties.minor_id = sys.columns.column_id) as Description
110 FROM sys.columns
111 WHERE sys.columns.object_id = object_id(@TableName)
112 ORDER BY sys.columns.column_id";
113 SqlCommand command = new SqlCommand(sql, Conn);
114 command.Parameters.Add(new SqlParameter("TableName",TableName));
115 List<TableSchema> TableSchemas = new List<TableSchema>();
116 using (SqlDataReader dr = command.ExecuteReader())
117 {
118 while (dr.Read())
119 {
120 TableSchema TS = new TableSchema();
121 TS.ColumnName = (string)dr["ColumnName"];
122 TS.Description = dr["Description"].ToString();
123 TS.IsIdentity = Convert.ToBoolean(dr["IsIdentity"]);
124 TS.MaxLength = (short)dr["MaxLength"];
125 TS.SqlTypeName = (string)dr["SqlTypeName"];
126 TableSchemas.Add(TS);
127 }
128 }
129 command.Dispose();
130 return TableSchemas;
131
132 }
133
134 private void MainForm_FormClosing(object sender, FormClosingEventArgs e)
135 {
136 if (Conn.State != ConnectionState.Closed)
137 {
138 Conn.Close();
139 }
140 }
141
142
143 /// <summary>
144 /// 生成实体类
145 /// </summary>
146 /// <param name="sender"></param>
147 /// <param name="e"></param>
148 private void BuildModelButton_Click(object sender, EventArgs e)
149 {
150 Table CurrentTable = this.TableTreeView.SelectedNode.Tag as Table;
151
152 while (CurrentTable == null)
153 {
154 if (TableTreeView.SelectedNode.Tag.Equals(RootNodeTag))
155 {
156 MessageBox.Show("请选择当前需要生成的表
", "提示");
157 return;
158 }
159 CurrentTable = TableTreeView.SelectedNode.Parent.Tag as Table;
160 }
161 StringBuilder ModelText = new StringBuilder();
162 //
163 //引用
164 //
165 ModelText.AppendLine("using System;");
166 ModelText.AppendLine("using System.Collections.Generic;");
167 ModelText.AppendLine();
168 //
169 //命名空间
170 //
171 if (string.IsNullOrEmpty(NamespaceTextEdit.Text))
172 {
173 ModelText.AppendLine("namespace Fengyun");
174 }
175 else
176 {
177 ModelText.AppendFormat(string.Format("namespace {0}",NamespaceTextEdit.Text));
178 }
179 ModelText.AppendLine();
180 ModelText.AppendLine("{");
181 ModelText.AppendLine();
182 //
183 //类名
184 //
185 if (string.IsNullOrEmpty(this.ClassTextEdit.Text))
186 {
187 ModelText.AppendFormat(string.Format(" public class {0}",CurrentTable.TableName));
188 }
189 else
190 {
191 ModelText.AppendFormat(string.Format(" public class {0}", ClassTextEdit.Text));
192 }
193 ModelText.AppendLine("{");
194 ModelText.AppendLine();
195 //
196 //属性
197 //
198 foreach(TableSchema TS in CurrentTable.TableSchemas)
199 {
200 ModelText.AppendLine();
201 ModelText.AppendLine(" /// <summary>");
202 ModelText.AppendLine(" /// " + TS.Description);
203 ModelText.AppendLine(" /// </summary>");
204 ModelText.AppendLine(" public " + TS.TypeName +" " + TS.ColumnName + "{ get; set; }");
205 ModelText.AppendLine();
206 ModelText.AppendLine();
207 }
208
209 ModelText.AppendLine(" }//闭合类");
210 ModelText.AppendLine("}//闭合命名空间");
211 this.ModelEdit.Text = ModelText.ToString();
212
213 }
214
215
216
217 }
218
219
220 /// <summary>
221 /// 表结构
222 /// </summary>
223 public class Table
224 {
225 public string TableName { get; set; }
226
227 public List<string> TableKeys { get; set; }
228
229 public List<TableSchema> TableSchemas { get; set; }
230
231 }
232
233
234 /// <summary>
235 /// 表架构
236 /// </summary>
237 public class TableSchema
238 {
239 /// <summary>
240 /// 列名
241 /// </summary>
242 public string ColumnName { get; set; }
243
244 /// <summary>
245 /// sql数据类型
246 /// </summary>
247 public string SqlTypeName { get; set; }
248
249 /// <summary>
250 /// dotnet数据类型
251 /// </summary>
252 public string TypeName {
253 get
254 {
255 switch(SqlTypeName.ToLower())
256 {
257 case "image":
258 return "byte[]";
259 case "text":
260 return "string";
261 case "uniqueidentifier":
262 return "Guid";
263 case "tinyint":
264 return "byte";
265 case "int":
266 return "int";
267 case "smalldatetime":
268 return "DateTime";
269 case "real":
270 return "double";
271 case "money":
272 return "decimal";
273 case "datetime":
274 return "DateTime";
275 case "float":
276 return "float";
277
278 //sql_variant
279 case "ntext":
280 return "string";
281 case "bit":
282 return "bool";
283 case "decimal":
284 return "decimal";
285 //numeric
286 //smallmoney
287 //bigint
288 //varbinary
289 //varchar
290 //binary
291 //char
292 //timestamp
293 //nvarchar
294 //nchar
295 //xml
296 //sysname
297 }
298 }
299 }
300
301
302 /// <summary>
303 /// 该字段最大长度
304 /// </summary>
305 public short MaxLength { get; set; }
306
307 /// <summary>
308 /// 摘要
309 /// </summary>
310 public string Description { get; set; }
311
312 /// <summary>
313 /// 是否为自动增量
314 /// </summary>
315 public bool IsIdentity { get; set; }
316 }
317
318}
319
320
321
322
323
324 }
325}
326
using System;2
using System.Collections.Generic;3
using System.ComponentModel;4
using System.Data;5
using System.Data.Common;6
using System.Data.SqlClient;7
using System.Drawing;8
using System.Text;9
using System.Windows.Forms;10
11
12
namespace CodeBuilder13
{14
public partial class MainForm : Form15
{16
public static string ConnectionString { get; set; }17
SqlConnection Conn = new SqlConnection(ConnectionString);18
private string RootNodeTag = "root";19
private string RootNodeText = "数据库";20
21
22
public MainForm()23
{24
InitializeComponent();25
}26
27
private void MainForm_Load(object sender, EventArgs e)28
{29
Conn.Open();30
List<Table> Tables = GetTables();31
TreeNode ParentNode = new TreeNode();32
ParentNode.Text = RootNodeText;33
ParentNode.Tag = RootNodeTag;34
foreach (Table T in Tables)35
{36
TreeNode TN = new TreeNode();37
TN.Text = T.TableName;38
TN.Tag = T;39
TN.ForeColor = Color.Blue;40
foreach (TableSchema TS in T.TableSchemas)41
{42
TreeNode TN2 = new TreeNode();43
TN2.Text = string.Format("{0}[{1}]", TS.ColumnName, TS.SqlTypeName);44
TN2.Tag = TS;45
TN.Nodes.Add(TN2);46
}47
ParentNode.Nodes.Add(TN);48
}49
50
51
this.TableTreeView.Nodes.Add(ParentNode);52
}53
54
55
/// <summary>56
/// 返回该表的主键57
/// </summary>58
/// <param name="TableName">需要获取主健的表名</param>59
/// <returns></returns>60
private List<string> GetTabkeKeys(string TableName)61
{62
SqlCommand command = new SqlCommand("sp_pkeys", Conn);63
command.CommandType = CommandType.StoredProcedure;64
SqlParameter para = new SqlParameter("table_name", TableName);65
command.Parameters.Add(para);66
List<string> TabkeKeys = new List<string>();67
using (SqlDataReader dr = command.ExecuteReader())68
{69
while (dr.Read())70
{71
TabkeKeys.Add((string)dr["COLUMN_NAME"]);72
}73
}74
command.Dispose(); 75
return TabkeKeys;76
}77
78
79
private List<Table> GetTables()80
{81
SqlCommand command = new SqlCommand("SELECT Name FROM sys.tables", Conn);82
List<Table> Tables = new List<Table>();83
using (SqlDataReader dr = command.ExecuteReader())84
{85
while (dr.Read())86
{87
Table T = new Table();88
T.TableName = (string)dr["Name"]; 89
Tables.Add(T);90
}91
}92
foreach (Table T in Tables)93
{94
T.TableKeys = GetTabkeKeys(T.TableName);95
T.TableSchemas = GetTableSchemas(T.TableName);96
}97
command.Dispose();98
return Tables;99
}100
101
102
private List<TableSchema> GetTableSchemas(string TableName)103
{104
string sql = @"SELECT sys.columns.name AS ColumnName, 105
type_name(sys.columns.system_type_id) AS SqlTypeName,106
sys.columns.max_length AS MaxLength,107
sys.columns.is_nullable AS IsNullable,108
(select count(*) from sys.identity_columns where sys.identity_columns.object_id = sys.columns.object_id and sys.columns.column_id = sys.identity_columns.column_id) as IsIdentity, 109
(select value from sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id and sys.extended_properties.minor_id = sys.columns.column_id) as Description110
FROM sys.columns 111
WHERE sys.columns.object_id = object_id(@TableName)112
ORDER BY sys.columns.column_id";113
SqlCommand command = new SqlCommand(sql, Conn);114
command.Parameters.Add(new SqlParameter("TableName",TableName));115
List<TableSchema> TableSchemas = new List<TableSchema>();116
using (SqlDataReader dr = command.ExecuteReader())117
{118
while (dr.Read())119
{120
TableSchema TS = new TableSchema();121
TS.ColumnName = (string)dr["ColumnName"];122
TS.Description = dr["Description"].ToString();123
TS.IsIdentity = Convert.ToBoolean(dr["IsIdentity"]);124
TS.MaxLength = (short)dr["MaxLength"];125
TS.SqlTypeName = (string)dr["SqlTypeName"];126
TableSchemas.Add(TS);127
}128
}129
command.Dispose();130
return TableSchemas;131
132
}133
134
private void MainForm_FormClosing(object sender, FormClosingEventArgs e)135
{136
if (Conn.State != ConnectionState.Closed)137
{138
Conn.Close();139
}140
}141
142
143
/// <summary>144
/// 生成实体类145
/// </summary>146
/// <param name="sender"></param>147
/// <param name="e"></param>148
private void BuildModelButton_Click(object sender, EventArgs e)149
{150
Table CurrentTable = this.TableTreeView.SelectedNode.Tag as Table;151
152
while (CurrentTable == null)153
{154
if (TableTreeView.SelectedNode.Tag.Equals(RootNodeTag))155
{156
MessageBox.Show("请选择当前需要生成的表", "提示");157
return;158
}159
CurrentTable = TableTreeView.SelectedNode.Parent.Tag as Table; 160
}161
StringBuilder ModelText = new StringBuilder();162
//163
//引用164
//165
ModelText.AppendLine("using System;");166
ModelText.AppendLine("using System.Collections.Generic;");167
ModelText.AppendLine();168
//169
//命名空间170
//171
if (string.IsNullOrEmpty(NamespaceTextEdit.Text))172
{173
ModelText.AppendLine("namespace Fengyun");174
}175
else176
{177
ModelText.AppendFormat(string.Format("namespace {0}",NamespaceTextEdit.Text));178
}179
ModelText.AppendLine();180
ModelText.AppendLine("{");181
ModelText.AppendLine();182
//183
//类名184
//185
if (string.IsNullOrEmpty(this.ClassTextEdit.Text))186
{187
ModelText.AppendFormat(string.Format(" public class {0}",CurrentTable.TableName));188
}189
else190
{191
ModelText.AppendFormat(string.Format(" public class {0}", ClassTextEdit.Text));192
}193
ModelText.AppendLine("{");194
ModelText.AppendLine();195
//196
//属性197
//198
foreach(TableSchema TS in CurrentTable.TableSchemas)199
{200
ModelText.AppendLine();201
ModelText.AppendLine(" /// <summary>");202
ModelText.AppendLine(" /// " + TS.Description);203
ModelText.AppendLine(" /// </summary>");204
ModelText.AppendLine(" public " + TS.TypeName +" " + TS.ColumnName + "{ get; set; }");205
ModelText.AppendLine();206
ModelText.AppendLine();207
}208
209
ModelText.AppendLine(" }//闭合类");210
ModelText.AppendLine("}//闭合命名空间");211
this.ModelEdit.Text = ModelText.ToString();212
213
}214
215
216
217
}218
219
220
/// <summary>221
/// 表结构222
/// </summary>223
public class Table224
{225
public string TableName { get; set; }226
227
public List<string> TableKeys { get; set; }228
229
public List<TableSchema> TableSchemas { get; set; }230
231
}232
233
234
/// <summary>235
/// 表架构236
/// </summary>237
public class TableSchema238
{239
/// <summary>240
/// 列名241
/// </summary>242
public string ColumnName { get; set; }243
244
/// <summary>245
/// sql数据类型246
/// </summary>247
public string SqlTypeName { get; set; }248
249
/// <summary>250
/// dotnet数据类型251
/// </summary>252
public string TypeName {253
get254
{255
switch(SqlTypeName.ToLower())256
{257
case "image":258
return "byte[]";259
case "text":260
return "string";261
case "uniqueidentifier":262
return "Guid";263
case "tinyint":264
return "byte";265
case "int":266
return "int";267
case "smalldatetime":268
return "DateTime";269
case "real":270
return "double";271
case "money":272
return "decimal";273
case "datetime":274
return "DateTime";275
case "float":276
return "float";277
278
//sql_variant279
case "ntext":280
return "string";281
case "bit":282
return "bool";283
case "decimal":284
return "decimal";285
//numeric286
//smallmoney287
//bigint288
//varbinary289
//varchar290
//binary291
//char292
//timestamp293
//nvarchar294
//nchar295
//xml296
//sysname297
}298
}299
}300
301
302
/// <summary>303
/// 该字段最大长度304
/// </summary>305
public short MaxLength { get; set; }306
307
/// <summary>308
/// 摘要309
/// </summary>310
public string Description { get; set; }311
312
/// <summary>313
/// 是否为自动增量314
/// </summary>315
public bool IsIdentity { get; set; }316
}317
318
}319
320
321
322
323
324
}325
}326
浙公网安备 33010602011771号