版权所有:基础软件。作者邮箱:sun.j.l.studio@gmail.com。本文首发于 http://www.cnblogs.com/FoundationSoft。文章转载请保持此版权信息并注明出处。
自动生成三层结构代码的程序大部分已经写出来,但是今天没有时间把博客也同步更新,先把到目前为止的代码贴出来,有兴趣的可以先看看。代码尚未完成,更新中。这个代码会随着我修改程序而不断更新。
下面列出三组代码:1.生成代码的代码; 2.生成的实体类; 3.生成的数据访问层代码。
1.以下是用于自动生成三层结构代码的代码。(有点拗口,简单说,是用于生成代码的代码)。
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1
using
System;
2 using System.Collections.Generic;
3 using System.Text;
4 using System.Data.Common;
5 using System.Reflection;
6 using System.Data.SqlClient;
7 using System.Data;
8 using Microsoft.Practices.EnterpriseLibrary.Data;
9
10 namespace Sjl.Utility.Dll
11 {
12 /* *****************************************************************
13 * 自动生成三层结构代码。
14 * 作者:孙继磊。sun.j.l.studio@gmail.com
15 * ---------------------------------------------------------------
16 * 修改记录:
17 * 初始版本1.0
18 * 实现了生成实体类,生成数据访问层update和insert方法。
19 * 孙继磊,2010-08-11
20 * ---------------------------------------------------------------
21 * 版本1.0.1
22 * 修改实体类代码,支持可空值类型。
23 * 能够生成fromReader方法和get方法代码。
24 * 孙继磊,2010-8-13
25 *
26 **************************************************************** */
27 /// <summary>
28 /// 自动代码生成类
29 /// 孙继磊,sun.j.l.studio@gmail.com
30 /// </summary>
31
32 public sealed class AutoDatabase
33 {
34 #region 成员变量
35 string table; // 数据库表名
36 bool schemaLoad = false ; // 数据库表架构是否已经加载
37 DataColumn[] allColumns = null ; // 表中所有列的集合
38 DataColumn[] primaryKey = null ; // 表的主键
39 DataColumn[] notKeyColumns = null ; // 非主键列
40 string [] columnNames = null ; // 列名数组
41 const string DalCalssSuffix = " Dal " ; // 数据访问层类名后缀
42 #endregion
43
44
45 #region 构造函数
46 public AutoDatabase( string tableName)
47 {
48 table = tableName;
49 getTableSchema();
50 }
51 #endregion
52
53
54 #region 自动为命令添加参数
55 /// <summary>
56 /// 利用反射将实体对象属性赋值到数据库命令作为参数
57 /// </summary>
58 /// <param name="command"> 数据库命令对象 </param>
59 /// <param name="entity"> 实体对象 </param>
60 /// <remarks> 参数名称必须与属性名称相同(前缀@) </remarks>
61 public static void addDbParameter(DbCommand command, object entity)
62 {
63 Type type = entity.GetType();
64 PropertyInfo[] properties = type.GetProperties();
65 foreach (var p in properties)
66 {
67 string name = " @ " + p.Name;
68 SqlParameter pa = new SqlParameter(name, p.GetValue(entity, null ));
69 }
70 }
71 #endregion
72
73
74 #region 生成实体类代码
75 // 生成实体类代码
76 public string generateEntityClass()
77 {
78 StringBuilder sb = new StringBuilder();
79 sb.Append( " public class " + table);
80 sb.Append(Environment.NewLine);
81 sb.Append( " { " );
82 foreach (DataColumn c in allColumns)
83 {
84 string type = c.DataType.Name;
85 sb.Append( " public " + type );
86 if (c.DataType.IsValueType)
87 if (c.AllowDBNull)
88 sb.Append( " ? " );
89 sb.Append( " " + c.ColumnName + " { get; set; } " );
90 sb.Append(Environment.NewLine);
91 }
92 sb.Append( " } " );
93 return sb.ToString();
94 }
95 #endregion
96
97
98 #region 生成数据访问类代码
99
100 #region 公共方法
101 // 生成数据访问类代码
102 public string generateDalClass()
103 {
104 StringBuilder sb = new StringBuilder();
105 sb.Append( " public class " );
106 sb.Append(table + DalCalssSuffix + Environment.NewLine);
107 sb.Append( " { " + Environment.NewLine); // class开始
108 // 生成字段
109 sb.Append(generateFields());
110 sb.Append(generateFromReaderMethod());
111 sb.Append(generateGetMethod());
112 // 生成insert方法代码
113 sb.Append(generateInsertMethod());
114 // 生成update方法代码
115 sb.Append(generateUpdateMethod());
116 // 生成delete方法代码
117 sb.Append(generateDeleteMethod());
118 sb.Append( " } " ); // class结束
119 return sb.ToString();
120 }
121 // 生成数据访问类insert方法代码
122 public string generateInsertMethod()
123 {
124 StringBuilder sb = new StringBuilder();
125 string temp = null ;
126 temp = " //插入新数据 " ;
127 sb.Append(temp + Environment.NewLine);
128 temp = " public void insert( " + table + " item) " ;
129 sb.Append(temp + Environment.NewLine);
130 sb.Append( " { " + Environment.NewLine);
131 temp = " Database db=DbUtility.getDatabase(); " ;
132 sb.Append(temp + Environment.NewLine);
133 temp = " //以下为生成的insert语句 " ;
134 sb.Append(temp + Environment.NewLine);
135 temp = " string sql=@\ "" + getInsertSql() + " \ " ; " ;
136 sb.Append(temp + Environment.NewLine);
137 temp = " DbCommand command=db.GetSqlStringCommand(sql); " ;
138 sb.Append(temp + Environment.NewLine);
139 temp = " //为数据库命令添加参数 " ;
140 sb.Append(temp + Environment.NewLine);
141 temp = getAddParameterCode();
142 sb.Append(temp);
143 temp = " db.ExecuteNonQuery(command); " ;
144 sb.Append(temp + Environment.NewLine);
145 temp = " command.Dispose(); " ;
146 sb.Append(temp + Environment.NewLine);
147 sb.Append( " } " + Environment.NewLine);
148 return sb.ToString();
149 }
150 // 生成数据访问类update方法代码
151 public string generateUpdateMethod()
152 {
153 StringBuilder sb = new StringBuilder();
154 string temp = null ;
155 temp = " //更新数据 " ;
156 sb.Append(temp + Environment.NewLine);
157 temp = " public void update( " + table + " item) " ;
158 sb.Append(temp + Environment.NewLine);
159 sb.Append( " { " + Environment.NewLine);
160 temp = " Database db=DbUtility.getDatabase(); " ;
161 sb.Append(temp + Environment.NewLine);
162 temp = " //以下为生成的update语句 " ;
163 sb.Append(temp + Environment.NewLine);
164 temp = " string sql=@\ "" + getUpdateSql() + " \ " ; " ;
165 sb.Append(temp + Environment.NewLine);
166 temp = " DbCommand command=db.GetSqlStringCommand(sql); " ;
167 sb.Append(temp + Environment.NewLine);
168 temp = " //为数据库命令添加参数 " ;
169 sb.Append(temp + Environment.NewLine);
170 temp = getAddParameterCode();
171 sb.Append(temp);
172 temp = " db.ExecuteNonQuery(command); " ;
173 sb.Append(temp + Environment.NewLine);
174 temp = " command.Dispose(); " ;
175 sb.Append(temp + Environment.NewLine);
176 sb.Append( " } " + Environment.NewLine);
177 return sb.ToString();
178 }
179 // 生成数据访问类delete方法代码
180 public string generateDeleteMethod()
181 {
182 StringBuilder sb = new StringBuilder();
183 string temp = null ;
184 sb.Append(temp + Environment.NewLine);
185 temp = " public void delete( " + table + " item) " ;
186 sb.Append(temp + Environment.NewLine);
187 sb.Append( " { " + Environment.NewLine);
188 temp = " Database db=DbUtility.getDatabase(); " ;
189 sb.Append(temp + Environment.NewLine);
190 sb.Append( " //TODO:修改以下where语句 " + Environment.NewLine);
191 temp = " string sql=@\ " delete from " + table;
192 sb.Append(temp + Environment.NewLine);
193 sb.Append( " where " );
194 temp = getWhere(primaryKey);
195 sb.Append(temp);
196 sb.Append( " \ " ; " + Environment.NewLine);
197 temp = " DbCommand command=db.GetSqlStringCommand(sql); " ;
198 sb.Append(temp + Environment.NewLine);
199 temp = " //为数据库命令添加参数 " ;
200 sb.Append(temp + Environment.NewLine);
201 temp = getAddParameterCode(primaryKey);
202 sb.Append(temp);
203 temp = " db.ExecuteNonQuery(command); " ;
204 sb.Append(temp + Environment.NewLine);
205 temp = " command.Dispose(); " ;
206 sb.Append(temp + Environment.NewLine);
207 sb.Append( " } " + Environment.NewLine);
208 return sb.ToString();
209 }
210 // 生成fromReader方法(fromReader方法作用为根据IDataReader读取数据并生成实体对象
211 public string generateFromReaderMethod()
212 {
213 StringBuilder sb = new StringBuilder();
214 string temp = null ;
215 sb.Append( " private " + table + " fromReader(IDataReader reader) " );
216 sb.Append(Environment.NewLine);
217 sb.Append( " { " + Environment.NewLine);
218 temp = table + " item=new " + table + " (); " ;
219 sb.Append(temp + Environment.NewLine);
220 foreach (DataColumn c in allColumns)
221 {
222 temp = " item. " + c.ColumnName + " =DbUtility.get " + c.DataType.Name + " (reader[\ "" +c.ColumnName+ " \ " ]) " ;
223 sb.Append(temp);
224 if ( ! c.AllowDBNull)
225 sb.Append( " .Value " );
226 sb.Append( " ; " + Environment.NewLine);
227 }
228 sb.Append( " return item; " + Environment.NewLine);
229 sb.Append( " } " + Environment.NewLine);
230 return sb.ToString();
231 }
232 // 生成get方法(根据对象id得到对象)
233 /*
234 public Announcement getById(int id)
235 {
236 string sql = SelectAll + " where id=@id";
237 Database db = DbUtility.getDatabase();
238 DbCommand command = db.GetSqlStringCommand(sql);
239 db.AddInParameter(command, "@id", DbType.Int32, id);
240 IDataReader reader = db.ExecuteReader(command);
241 Announcement a = null;
242 if (reader.Read())
243 a = fromReader(reader);
244 reader.Close();
245 command.Dispose();
246 return a;
247 }
248 */
249 public string generateGetMethod()
250 {
251 StringBuilder sb = new StringBuilder();
252 string temp = null ;
253 // 方法头
254 temp = " public " + table + " get( " ;
255 sb.Append(temp);
256 foreach (DataColumn c in primaryKey)
257 {
258 temp = " " + c.DataType.Name + " " + c.ColumnName.ToLower() + " , " ;
259 sb.Append(temp);
260 }
261 sb.Remove(sb.Length - 1 , 1 ); // 去掉最后一个逗号
262 sb.Append( " ) " + Environment.NewLine); // 方法头结束
263 sb.Append( " { " + Environment.NewLine); // 方法体开始
264 // 生成select语句
265 sb.Append( " string sql= SelectAll+\ " where " );
266 sb.Append(getWhere(primaryKey));
267 sb.Append( " \ " ; " + Environment.NewLine);
268 temp = " Database db=DbUtility.getDatabase(); " ;
269 sb.Append(temp + Environment.NewLine);
270 // 创建命令
271 sb.Append( " DbCommand command = db.GetSqlStringCommand(sql); " );
272 sb.Append(Environment.NewLine);
273 // 添加命令参数
274 sb.Append(table + " item=new " + table + " (); " );
275 sb.Append(Environment.NewLine);
276 foreach (DataColumn c in primaryKey)
277 {
278 sb.Append( " item. " + c.ColumnName + " = " + c.ColumnName.ToLower() + " ; " );
279 sb.Append(Environment.NewLine);
280 }
281 temp = getAddParameterCode(primaryKey);
282 sb.Append(temp);
283 // 执行命令得到reader
284 sb.Append( " IDataReader reader = db.ExecuteReader(command); " );
285 sb.Append(Environment.NewLine);
286 sb.Append( " item =null; " );
287 sb.Append(Environment.NewLine);
288 sb.Append( " if (reader.Read()) " + Environment.NewLine + " item=fromReader(reader); " + Environment.NewLine);
289 sb.Append( " reader.Close(); " + Environment.NewLine);
290 sb.Append( " command.Dispose(); " + Environment.NewLine);
291 sb.Append( " return item; " + Environment.NewLine);
292 sb.Append( " } " + Environment.NewLine); // 方法体结束
293 return sb.ToString();
294 }
295
296 // 生成类中常量和成员变量代码
297 public string generateFields()
298 {
299 StringBuilder sb = new StringBuilder();
300 string temp = null ;
301 // 定义表名
302 temp = " const string Table = \ " " + table + " \ " ; " ;
303 sb.Append(temp + Environment.NewLine);
304 // 定义非主键字段
305 sb.Append( " //非主键字段 " );
306 sb.Append(Environment.NewLine);
307 sb.Append( " const string AllButKey=\ " " );
308 foreach (DataColumn c in notKeyColumns)
309 {
310 sb.Append(c.ColumnName + " , " );
311 }
312 sb.Remove(sb.Length - 1 , 1 );
313 sb.Append( " \ " ; " +Environment.NewLine);
314 // 定义所有字段(包括主键)
315 sb.Append( " //所有字段(包括主键 " );
316 sb.Append(Environment.NewLine);
317 sb.Append( " const string AllColumns=\ " " );
318 foreach (DataColumn c in primaryKey)
319 {
320 sb.Append(c.ColumnName + " , " );
321 }
322 sb.Append( " \ " + AllButKey; " );
323 sb.Append(Environment.NewLine);
324 // 定义select all
325 sb.Append( " const string SelectAll = \ " select \ " + AllColumns +\ " from \ " +Table; " );
326 sb.Append(Environment.NewLine);
327 return sb.ToString();
328 }
329
330 #endregion
331
332 #region 私有方法
333 // 生成update的sql语句
334 private string getUpdateSql()
335 {
336 StringBuilder sb = new StringBuilder();
337 string temp = null ;
338 temp = " update " + table
339 + Environment.NewLine + " set " ;
340 sb.Append(temp + Environment.NewLine);
341 foreach (DataColumn c in notKeyColumns)
342 {
343 sb.Append(c.ColumnName + " =@ " + c.ColumnName + " , " );
344 }
345 // 去掉最后一个逗号
346 sb.Remove(sb.Length - 1 , 1 );
347 sb.Append( Environment.NewLine);
348 sb.Append( " where " );
349 sb.Append(getWhere(primaryKey));
350 return sb.ToString();
351 }
352
353 // 生成insert的sql语句
354 private string getInsertSql()
355 {
356 StringBuilder sb = new StringBuilder();
357 string temp = null ;
358 temp = " insert into " + table
359 + Environment.NewLine + " ( " ;
360 sb.Append(temp + Environment.NewLine);
361 foreach ( string n in columnNames)
362 {
363 sb.Append(n + " , " );
364 }
365 // 去掉最后一个逗号
366 sb.Remove(sb.Length - 1 , 1 );
367 temp = " ) " + Environment.NewLine + " values ( " + Environment.NewLine;
368 sb.Append(temp);
369 foreach ( string n in columnNames)
370 {
371 sb.Append( " @ " + n + " , " );
372 }
373 sb.Remove(sb.Length - 1 , 1 );
374 sb.Append( " ) " );
375 return sb.ToString();
376 }
377
378 #region 生成添加命令参数的语句
379 private string getAddParameterCode()
380 {
381 return getAddParameterCode(allColumns);
382 }
383
384 private string getAddParameterCode(DataColumn[] columns)
385 {
386 StringBuilder sb = new StringBuilder();
387 string temp = null ;
388 foreach (DataColumn c in columns)
389 {
390 temp = " db.AddInParameter(command,\ "@" + c.ColumnName + " \ " ,DbType. " + c.DataType.Name + " ,item. " + c.ColumnName + " ); " ;
391 sb.Append(temp);
392 sb.Append(Environment.NewLine);
393 }
394 return sb.ToString();
395 }
396 #endregion
397
398 #region 得到表架构,获得所有列信息,获得的信息保存在成员变量中
399
400 private void getTableSchema()
401 {
402 if (schemaLoad ) return ; // 如果已经加载,则不重复加载
403 // 以下代码获取表架构
404 Database db = DbUtility.getDatabase();
405 DbCommand command = db.GetSqlStringCommand( " select * from " + table + " where 1=2 " );
406 // IDataReader reader = db.ExecuteReader(command);
407 // DataTable dataTable=reader.GetSchemaTable();
408 // reader.Close();
409 command.Connection = db.CreateConnection();
410 DbDataAdapter a = db.GetDataAdapter();
411 a.SelectCommand = command;
412 DataTable dataTable = new DataTable();
413 a.FillSchema(dataTable, SchemaType.Source);
414 command.Dispose();
415 // 将表架构列信息保存到成员变量中备用
416 allColumns = new DataColumn[dataTable.Columns.Count];
417 primaryKey = dataTable.PrimaryKey;
418 notKeyColumns = new DataColumn[allColumns.Length - primaryKey.Length];
419 int n = allColumns.Length;
420 columnNames = new string [n];
421 List < DataColumn > notKeys = new List < DataColumn > ();
422 for ( int i = 0 ; i < n; i ++ )
423 {
424 DataColumn column = dataTable.Columns[i];
425 allColumns[i] = column;
426 columnNames[i] = column.ColumnName;
427 if (Array.IndexOf < DataColumn > (primaryKey, column) < 0 )
428 notKeys.Add(column);
429 }
430 notKeyColumns = notKeys.ToArray();
431 }
432 #endregion
433
434 #region 得到where子句语句
435 /// <summary>
436 /// 根据表中指定字段生成where条件
437 /// </summary>
438 /// <param name="columns"> 需要包含在where条件中的字段列表 </param>
439 /// <returns> 生成的条件(不包含where关键字) </returns>
440 private string getWhere(DataColumn[] columns)
441 {
442 StringBuilder sb = new StringBuilder();
443 foreach (DataColumn c in columns)
444 {
445 string n = c.ColumnName;
446 sb.Append(n + " =@ " + n + " and " );
447 }
448 if (primaryKey.Length > 0 )
449 sb.Remove(sb.Length - 4 , 4 ); // 去掉最后的and
450 return sb.ToString();
451 }
452 #endregion
453
454 #endregion
455
456 #endregion
457 }
458 }
459
2 using System.Collections.Generic;
3 using System.Text;
4 using System.Data.Common;
5 using System.Reflection;
6 using System.Data.SqlClient;
7 using System.Data;
8 using Microsoft.Practices.EnterpriseLibrary.Data;
9
10 namespace Sjl.Utility.Dll
11 {
12 /* *****************************************************************
13 * 自动生成三层结构代码。
14 * 作者:孙继磊。sun.j.l.studio@gmail.com
15 * ---------------------------------------------------------------
16 * 修改记录:
17 * 初始版本1.0
18 * 实现了生成实体类,生成数据访问层update和insert方法。
19 * 孙继磊,2010-08-11
20 * ---------------------------------------------------------------
21 * 版本1.0.1
22 * 修改实体类代码,支持可空值类型。
23 * 能够生成fromReader方法和get方法代码。
24 * 孙继磊,2010-8-13
25 *
26 **************************************************************** */
27 /// <summary>
28 /// 自动代码生成类
29 /// 孙继磊,sun.j.l.studio@gmail.com
30 /// </summary>
31
32 public sealed class AutoDatabase
33 {
34 #region 成员变量
35 string table; // 数据库表名
36 bool schemaLoad = false ; // 数据库表架构是否已经加载
37 DataColumn[] allColumns = null ; // 表中所有列的集合
38 DataColumn[] primaryKey = null ; // 表的主键
39 DataColumn[] notKeyColumns = null ; // 非主键列
40 string [] columnNames = null ; // 列名数组
41 const string DalCalssSuffix = " Dal " ; // 数据访问层类名后缀
42 #endregion
43
44
45 #region 构造函数
46 public AutoDatabase( string tableName)
47 {
48 table = tableName;
49 getTableSchema();
50 }
51 #endregion
52
53
54 #region 自动为命令添加参数
55 /// <summary>
56 /// 利用反射将实体对象属性赋值到数据库命令作为参数
57 /// </summary>
58 /// <param name="command"> 数据库命令对象 </param>
59 /// <param name="entity"> 实体对象 </param>
60 /// <remarks> 参数名称必须与属性名称相同(前缀@) </remarks>
61 public static void addDbParameter(DbCommand command, object entity)
62 {
63 Type type = entity.GetType();
64 PropertyInfo[] properties = type.GetProperties();
65 foreach (var p in properties)
66 {
67 string name = " @ " + p.Name;
68 SqlParameter pa = new SqlParameter(name, p.GetValue(entity, null ));
69 }
70 }
71 #endregion
72
73
74 #region 生成实体类代码
75 // 生成实体类代码
76 public string generateEntityClass()
77 {
78 StringBuilder sb = new StringBuilder();
79 sb.Append( " public class " + table);
80 sb.Append(Environment.NewLine);
81 sb.Append( " { " );
82 foreach (DataColumn c in allColumns)
83 {
84 string type = c.DataType.Name;
85 sb.Append( " public " + type );
86 if (c.DataType.IsValueType)
87 if (c.AllowDBNull)
88 sb.Append( " ? " );
89 sb.Append( " " + c.ColumnName + " { get; set; } " );
90 sb.Append(Environment.NewLine);
91 }
92 sb.Append( " } " );
93 return sb.ToString();
94 }
95 #endregion
96
97
98 #region 生成数据访问类代码
99
100 #region 公共方法
101 // 生成数据访问类代码
102 public string generateDalClass()
103 {
104 StringBuilder sb = new StringBuilder();
105 sb.Append( " public class " );
106 sb.Append(table + DalCalssSuffix + Environment.NewLine);
107 sb.Append( " { " + Environment.NewLine); // class开始
108 // 生成字段
109 sb.Append(generateFields());
110 sb.Append(generateFromReaderMethod());
111 sb.Append(generateGetMethod());
112 // 生成insert方法代码
113 sb.Append(generateInsertMethod());
114 // 生成update方法代码
115 sb.Append(generateUpdateMethod());
116 // 生成delete方法代码
117 sb.Append(generateDeleteMethod());
118 sb.Append( " } " ); // class结束
119 return sb.ToString();
120 }
121 // 生成数据访问类insert方法代码
122 public string generateInsertMethod()
123 {
124 StringBuilder sb = new StringBuilder();
125 string temp = null ;
126 temp = " //插入新数据 " ;
127 sb.Append(temp + Environment.NewLine);
128 temp = " public void insert( " + table + " item) " ;
129 sb.Append(temp + Environment.NewLine);
130 sb.Append( " { " + Environment.NewLine);
131 temp = " Database db=DbUtility.getDatabase(); " ;
132 sb.Append(temp + Environment.NewLine);
133 temp = " //以下为生成的insert语句 " ;
134 sb.Append(temp + Environment.NewLine);
135 temp = " string sql=@\ "" + getInsertSql() + " \ " ; " ;
136 sb.Append(temp + Environment.NewLine);
137 temp = " DbCommand command=db.GetSqlStringCommand(sql); " ;
138 sb.Append(temp + Environment.NewLine);
139 temp = " //为数据库命令添加参数 " ;
140 sb.Append(temp + Environment.NewLine);
141 temp = getAddParameterCode();
142 sb.Append(temp);
143 temp = " db.ExecuteNonQuery(command); " ;
144 sb.Append(temp + Environment.NewLine);
145 temp = " command.Dispose(); " ;
146 sb.Append(temp + Environment.NewLine);
147 sb.Append( " } " + Environment.NewLine);
148 return sb.ToString();
149 }
150 // 生成数据访问类update方法代码
151 public string generateUpdateMethod()
152 {
153 StringBuilder sb = new StringBuilder();
154 string temp = null ;
155 temp = " //更新数据 " ;
156 sb.Append(temp + Environment.NewLine);
157 temp = " public void update( " + table + " item) " ;
158 sb.Append(temp + Environment.NewLine);
159 sb.Append( " { " + Environment.NewLine);
160 temp = " Database db=DbUtility.getDatabase(); " ;
161 sb.Append(temp + Environment.NewLine);
162 temp = " //以下为生成的update语句 " ;
163 sb.Append(temp + Environment.NewLine);
164 temp = " string sql=@\ "" + getUpdateSql() + " \ " ; " ;
165 sb.Append(temp + Environment.NewLine);
166 temp = " DbCommand command=db.GetSqlStringCommand(sql); " ;
167 sb.Append(temp + Environment.NewLine);
168 temp = " //为数据库命令添加参数 " ;
169 sb.Append(temp + Environment.NewLine);
170 temp = getAddParameterCode();
171 sb.Append(temp);
172 temp = " db.ExecuteNonQuery(command); " ;
173 sb.Append(temp + Environment.NewLine);
174 temp = " command.Dispose(); " ;
175 sb.Append(temp + Environment.NewLine);
176 sb.Append( " } " + Environment.NewLine);
177 return sb.ToString();
178 }
179 // 生成数据访问类delete方法代码
180 public string generateDeleteMethod()
181 {
182 StringBuilder sb = new StringBuilder();
183 string temp = null ;
184 sb.Append(temp + Environment.NewLine);
185 temp = " public void delete( " + table + " item) " ;
186 sb.Append(temp + Environment.NewLine);
187 sb.Append( " { " + Environment.NewLine);
188 temp = " Database db=DbUtility.getDatabase(); " ;
189 sb.Append(temp + Environment.NewLine);
190 sb.Append( " //TODO:修改以下where语句 " + Environment.NewLine);
191 temp = " string sql=@\ " delete from " + table;
192 sb.Append(temp + Environment.NewLine);
193 sb.Append( " where " );
194 temp = getWhere(primaryKey);
195 sb.Append(temp);
196 sb.Append( " \ " ; " + Environment.NewLine);
197 temp = " DbCommand command=db.GetSqlStringCommand(sql); " ;
198 sb.Append(temp + Environment.NewLine);
199 temp = " //为数据库命令添加参数 " ;
200 sb.Append(temp + Environment.NewLine);
201 temp = getAddParameterCode(primaryKey);
202 sb.Append(temp);
203 temp = " db.ExecuteNonQuery(command); " ;
204 sb.Append(temp + Environment.NewLine);
205 temp = " command.Dispose(); " ;
206 sb.Append(temp + Environment.NewLine);
207 sb.Append( " } " + Environment.NewLine);
208 return sb.ToString();
209 }
210 // 生成fromReader方法(fromReader方法作用为根据IDataReader读取数据并生成实体对象
211 public string generateFromReaderMethod()
212 {
213 StringBuilder sb = new StringBuilder();
214 string temp = null ;
215 sb.Append( " private " + table + " fromReader(IDataReader reader) " );
216 sb.Append(Environment.NewLine);
217 sb.Append( " { " + Environment.NewLine);
218 temp = table + " item=new " + table + " (); " ;
219 sb.Append(temp + Environment.NewLine);
220 foreach (DataColumn c in allColumns)
221 {
222 temp = " item. " + c.ColumnName + " =DbUtility.get " + c.DataType.Name + " (reader[\ "" +c.ColumnName+ " \ " ]) " ;
223 sb.Append(temp);
224 if ( ! c.AllowDBNull)
225 sb.Append( " .Value " );
226 sb.Append( " ; " + Environment.NewLine);
227 }
228 sb.Append( " return item; " + Environment.NewLine);
229 sb.Append( " } " + Environment.NewLine);
230 return sb.ToString();
231 }
232 // 生成get方法(根据对象id得到对象)
233 /*
234 public Announcement getById(int id)
235 {
236 string sql = SelectAll + " where id=@id";
237 Database db = DbUtility.getDatabase();
238 DbCommand command = db.GetSqlStringCommand(sql);
239 db.AddInParameter(command, "@id", DbType.Int32, id);
240 IDataReader reader = db.ExecuteReader(command);
241 Announcement a = null;
242 if (reader.Read())
243 a = fromReader(reader);
244 reader.Close();
245 command.Dispose();
246 return a;
247 }
248 */
249 public string generateGetMethod()
250 {
251 StringBuilder sb = new StringBuilder();
252 string temp = null ;
253 // 方法头
254 temp = " public " + table + " get( " ;
255 sb.Append(temp);
256 foreach (DataColumn c in primaryKey)
257 {
258 temp = " " + c.DataType.Name + " " + c.ColumnName.ToLower() + " , " ;
259 sb.Append(temp);
260 }
261 sb.Remove(sb.Length - 1 , 1 ); // 去掉最后一个逗号
262 sb.Append( " ) " + Environment.NewLine); // 方法头结束
263 sb.Append( " { " + Environment.NewLine); // 方法体开始
264 // 生成select语句
265 sb.Append( " string sql= SelectAll+\ " where " );
266 sb.Append(getWhere(primaryKey));
267 sb.Append( " \ " ; " + Environment.NewLine);
268 temp = " Database db=DbUtility.getDatabase(); " ;
269 sb.Append(temp + Environment.NewLine);
270 // 创建命令
271 sb.Append( " DbCommand command = db.GetSqlStringCommand(sql); " );
272 sb.Append(Environment.NewLine);
273 // 添加命令参数
274 sb.Append(table + " item=new " + table + " (); " );
275 sb.Append(Environment.NewLine);
276 foreach (DataColumn c in primaryKey)
277 {
278 sb.Append( " item. " + c.ColumnName + " = " + c.ColumnName.ToLower() + " ; " );
279 sb.Append(Environment.NewLine);
280 }
281 temp = getAddParameterCode(primaryKey);
282 sb.Append(temp);
283 // 执行命令得到reader
284 sb.Append( " IDataReader reader = db.ExecuteReader(command); " );
285 sb.Append(Environment.NewLine);
286 sb.Append( " item =null; " );
287 sb.Append(Environment.NewLine);
288 sb.Append( " if (reader.Read()) " + Environment.NewLine + " item=fromReader(reader); " + Environment.NewLine);
289 sb.Append( " reader.Close(); " + Environment.NewLine);
290 sb.Append( " command.Dispose(); " + Environment.NewLine);
291 sb.Append( " return item; " + Environment.NewLine);
292 sb.Append( " } " + Environment.NewLine); // 方法体结束
293 return sb.ToString();
294 }
295
296 // 生成类中常量和成员变量代码
297 public string generateFields()
298 {
299 StringBuilder sb = new StringBuilder();
300 string temp = null ;
301 // 定义表名
302 temp = " const string Table = \ " " + table + " \ " ; " ;
303 sb.Append(temp + Environment.NewLine);
304 // 定义非主键字段
305 sb.Append( " //非主键字段 " );
306 sb.Append(Environment.NewLine);
307 sb.Append( " const string AllButKey=\ " " );
308 foreach (DataColumn c in notKeyColumns)
309 {
310 sb.Append(c.ColumnName + " , " );
311 }
312 sb.Remove(sb.Length - 1 , 1 );
313 sb.Append( " \ " ; " +Environment.NewLine);
314 // 定义所有字段(包括主键)
315 sb.Append( " //所有字段(包括主键 " );
316 sb.Append(Environment.NewLine);
317 sb.Append( " const string AllColumns=\ " " );
318 foreach (DataColumn c in primaryKey)
319 {
320 sb.Append(c.ColumnName + " , " );
321 }
322 sb.Append( " \ " + AllButKey; " );
323 sb.Append(Environment.NewLine);
324 // 定义select all
325 sb.Append( " const string SelectAll = \ " select \ " + AllColumns +\ " from \ " +Table; " );
326 sb.Append(Environment.NewLine);
327 return sb.ToString();
328 }
329
330 #endregion
331
332 #region 私有方法
333 // 生成update的sql语句
334 private string getUpdateSql()
335 {
336 StringBuilder sb = new StringBuilder();
337 string temp = null ;
338 temp = " update " + table
339 + Environment.NewLine + " set " ;
340 sb.Append(temp + Environment.NewLine);
341 foreach (DataColumn c in notKeyColumns)
342 {
343 sb.Append(c.ColumnName + " =@ " + c.ColumnName + " , " );
344 }
345 // 去掉最后一个逗号
346 sb.Remove(sb.Length - 1 , 1 );
347 sb.Append( Environment.NewLine);
348 sb.Append( " where " );
349 sb.Append(getWhere(primaryKey));
350 return sb.ToString();
351 }
352
353 // 生成insert的sql语句
354 private string getInsertSql()
355 {
356 StringBuilder sb = new StringBuilder();
357 string temp = null ;
358 temp = " insert into " + table
359 + Environment.NewLine + " ( " ;
360 sb.Append(temp + Environment.NewLine);
361 foreach ( string n in columnNames)
362 {
363 sb.Append(n + " , " );
364 }
365 // 去掉最后一个逗号
366 sb.Remove(sb.Length - 1 , 1 );
367 temp = " ) " + Environment.NewLine + " values ( " + Environment.NewLine;
368 sb.Append(temp);
369 foreach ( string n in columnNames)
370 {
371 sb.Append( " @ " + n + " , " );
372 }
373 sb.Remove(sb.Length - 1 , 1 );
374 sb.Append( " ) " );
375 return sb.ToString();
376 }
377
378 #region 生成添加命令参数的语句
379 private string getAddParameterCode()
380 {
381 return getAddParameterCode(allColumns);
382 }
383
384 private string getAddParameterCode(DataColumn[] columns)
385 {
386 StringBuilder sb = new StringBuilder();
387 string temp = null ;
388 foreach (DataColumn c in columns)
389 {
390 temp = " db.AddInParameter(command,\ "@" + c.ColumnName + " \ " ,DbType. " + c.DataType.Name + " ,item. " + c.ColumnName + " ); " ;
391 sb.Append(temp);
392 sb.Append(Environment.NewLine);
393 }
394 return sb.ToString();
395 }
396 #endregion
397
398 #region 得到表架构,获得所有列信息,获得的信息保存在成员变量中
399
400 private void getTableSchema()
401 {
402 if (schemaLoad ) return ; // 如果已经加载,则不重复加载
403 // 以下代码获取表架构
404 Database db = DbUtility.getDatabase();
405 DbCommand command = db.GetSqlStringCommand( " select * from " + table + " where 1=2 " );
406 // IDataReader reader = db.ExecuteReader(command);
407 // DataTable dataTable=reader.GetSchemaTable();
408 // reader.Close();
409 command.Connection = db.CreateConnection();
410 DbDataAdapter a = db.GetDataAdapter();
411 a.SelectCommand = command;
412 DataTable dataTable = new DataTable();
413 a.FillSchema(dataTable, SchemaType.Source);
414 command.Dispose();
415 // 将表架构列信息保存到成员变量中备用
416 allColumns = new DataColumn[dataTable.Columns.Count];
417 primaryKey = dataTable.PrimaryKey;
418 notKeyColumns = new DataColumn[allColumns.Length - primaryKey.Length];
419 int n = allColumns.Length;
420 columnNames = new string [n];
421 List < DataColumn > notKeys = new List < DataColumn > ();
422 for ( int i = 0 ; i < n; i ++ )
423 {
424 DataColumn column = dataTable.Columns[i];
425 allColumns[i] = column;
426 columnNames[i] = column.ColumnName;
427 if (Array.IndexOf < DataColumn > (primaryKey, column) < 0 )
428 notKeys.Add(column);
429 }
430 notKeyColumns = notKeys.ToArray();
431 }
432 #endregion
433
434 #region 得到where子句语句
435 /// <summary>
436 /// 根据表中指定字段生成where条件
437 /// </summary>
438 /// <param name="columns"> 需要包含在where条件中的字段列表 </param>
439 /// <returns> 生成的条件(不包含where关键字) </returns>
440 private string getWhere(DataColumn[] columns)
441 {
442 StringBuilder sb = new StringBuilder();
443 foreach (DataColumn c in columns)
444 {
445 string n = c.ColumnName;
446 sb.Append(n + " =@ " + n + " and " );
447 }
448 if (primaryKey.Length > 0 )
449 sb.Remove(sb.Length - 4 , 4 ); // 去掉最后的and
450 return sb.ToString();
451 }
452 #endregion
453
454 #endregion
455
456 #endregion
457 }
458 }
459
2.以下为自动生成的实体类代码
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1
public
class
Orders
2 {
3 public Int32 OrderID { get ; set ; }
4 public String CustomerID { get ; set ; }
5 public Int32 ? EmployeeID { get ; set ; }
6 public DateTime ? OrderDate { get ; set ; }
7 public DateTime ? RequiredDate { get ; set ; }
8 public DateTime ? ShippedDate { get ; set ; }
9 public Int32 ? ShipVia { get ; set ; }
10 public Decimal ? Freight { get ; set ; }
11 public String ShipName { get ; set ; }
12 public String ShipAddress { get ; set ; }
13 public String ShipCity { get ; set ; }
14 public String ShipRegion { get ; set ; }
15 public String ShipPostalCode { get ; set ; }
16 public String ShipCountry { get ; set ; }
17 }
2 {
3 public Int32 OrderID { get ; set ; }
4 public String CustomerID { get ; set ; }
5 public Int32 ? EmployeeID { get ; set ; }
6 public DateTime ? OrderDate { get ; set ; }
7 public DateTime ? RequiredDate { get ; set ; }
8 public DateTime ? ShippedDate { get ; set ; }
9 public Int32 ? ShipVia { get ; set ; }
10 public Decimal ? Freight { get ; set ; }
11 public String ShipName { get ; set ; }
12 public String ShipAddress { get ; set ; }
13 public String ShipCity { get ; set ; }
14 public String ShipRegion { get ; set ; }
15 public String ShipPostalCode { get ; set ; }
16 public String ShipCountry { get ; set ; }
17 }
3.以下为自动生成的数据访问类代码
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1
using
System.Data.Common;
2 using Microsoft.Practices.EnterpriseLibrary.Data;
3 using System.Data;
4 using System;
5 namespace Sjl.Utility.Dll
6 {
7 public class OrdersDal
8 {
9 const string Table = " Orders " ;
10 // 非主键字段
11 const string AllButKey = " CustomerID,EmployeeID,OrderDate,RequiredDate,ShippedDate,ShipVia,Freight,ShipName,ShipAddress,ShipCity,ShipRegion,ShipPostalCode,ShipCountry " ;
12 // 所有字段(包括主键
13 const string AllColumns = " OrderID, " + AllButKey;
14 const string SelectAll = " select " + AllColumns + " from " + Table;
15 private Orders fromReader(IDataReader reader)
16 {
17 Orders item = new Orders();
18 item.OrderID = DbUtility.getInt32(reader[ " OrderID " ]).Value;
19 item.CustomerID = DbUtility.getString(reader[ " CustomerID " ]);
20 item.EmployeeID = DbUtility.getInt32(reader[ " EmployeeID " ]);
21 item.OrderDate = DbUtility.getDateTime(reader[ " OrderDate " ]);
22 item.RequiredDate = DbUtility.getDateTime(reader[ " RequiredDate " ]);
23 item.ShippedDate = DbUtility.getDateTime(reader[ " ShippedDate " ]);
24 item.ShipVia = DbUtility.getInt32(reader[ " ShipVia " ]);
25 item.Freight = DbUtility.getDecimal(reader[ " Freight " ]);
26 item.ShipName = DbUtility.getString(reader[ " ShipName " ]);
27 item.ShipAddress = DbUtility.getString(reader[ " ShipAddress " ]);
28 item.ShipCity = DbUtility.getString(reader[ " ShipCity " ]);
29 item.ShipRegion = DbUtility.getString(reader[ " ShipRegion " ]);
30 item.ShipPostalCode = DbUtility.getString(reader[ " ShipPostalCode " ]);
31 item.ShipCountry = DbUtility.getString(reader[ " ShipCountry " ]);
32 return item;
33 }
34 public Orders get (Int32 orderid)
35 {
36 string sql = SelectAll + " where OrderID=@OrderID " ;
37 Database db = DbUtility.getDatabase();
38 DbCommand command = db.GetSqlStringCommand(sql);
39 Orders item = new Orders();
40 item.OrderID = orderid;
41 db.AddInParameter(command, " @OrderID " , DbType.Int32, item.OrderID);
42 IDataReader reader = db.ExecuteReader(command);
43 item = null ;
44 if (reader.Read())
45 item = fromReader(reader);
46 reader.Close();
47 command.Dispose();
48 return item;
49 }
50 // 插入新数据
51 public void insert(Orders item)
52 {
53 Database db = DbUtility.getDatabase();
54 // 以下为生成的insert语句
55 string sql = @" insert into Orders
56 (
57 OrderID,CustomerID,EmployeeID,OrderDate,RequiredDate,ShippedDate,ShipVia,Freight,ShipName,ShipAddress,ShipCity,ShipRegion,ShipPostalCode,ShipCountry)
58 values (
59 @OrderID,@CustomerID,@EmployeeID,@OrderDate,@RequiredDate,@ShippedDate,@ShipVia,@Freight,@ShipName,@ShipAddress,@ShipCity,@ShipRegion,@ShipPostalCode,@ShipCountry) " ;
60 DbCommand command = db.GetSqlStringCommand(sql);
61 // 为数据库命令添加参数
62 db.AddInParameter(command, " @OrderID " , DbType.Int32, item.OrderID);
63 db.AddInParameter(command, " @CustomerID " , DbType.String, item.CustomerID);
64 db.AddInParameter(command, " @EmployeeID " , DbType.Int32, item.EmployeeID);
65 db.AddInParameter(command, " @OrderDate " , DbType.DateTime, item.OrderDate);
66 db.AddInParameter(command, " @RequiredDate " , DbType.DateTime, item.RequiredDate);
67 db.AddInParameter(command, " @ShippedDate " , DbType.DateTime, item.ShippedDate);
68 db.AddInParameter(command, " @ShipVia " , DbType.Int32, item.ShipVia);
69 db.AddInParameter(command, " @Freight " , DbType.Decimal, item.Freight);
70 db.AddInParameter(command, " @ShipName " , DbType.String, item.ShipName);
71 db.AddInParameter(command, " @ShipAddress " , DbType.String, item.ShipAddress);
72 db.AddInParameter(command, " @ShipCity " , DbType.String, item.ShipCity);
73 db.AddInParameter(command, " @ShipRegion " , DbType.String, item.ShipRegion);
74 db.AddInParameter(command, " @ShipPostalCode " , DbType.String, item.ShipPostalCode);
75 db.AddInParameter(command, " @ShipCountry " , DbType.String, item.ShipCountry);
76 db.ExecuteNonQuery(command);
77 command.Dispose();
78 }
79 // 更新数据
80 public void update(Orders item)
81 {
82 Database db = DbUtility.getDatabase();
83 // 以下为生成的update语句
84 string sql = @" update Orders
85 set
86 CustomerID=@CustomerID,EmployeeID=@EmployeeID,OrderDate=@OrderDate,RequiredDate=@RequiredDate,ShippedDate=@ShippedDate,ShipVia=@ShipVia,Freight=@Freight,ShipName=@ShipName,ShipAddress=@ShipAddress,ShipCity=@ShipCity,ShipRegion=@ShipRegion,ShipPostalCode=@ShipPostalCode,ShipCountry=@ShipCountry
87 where OrderID=@OrderID " ;
88 DbCommand command = db.GetSqlStringCommand(sql);
89 // 为数据库命令添加参数
90 db.AddInParameter(command, " @OrderID " , DbType.Int32, item.OrderID);
91 db.AddInParameter(command, " @CustomerID " , DbType.String, item.CustomerID);
92 db.AddInParameter(command, " @EmployeeID " , DbType.Int32, item.EmployeeID);
93 db.AddInParameter(command, " @OrderDate " , DbType.DateTime, item.OrderDate);
94 db.AddInParameter(command, " @RequiredDate " , DbType.DateTime, item.RequiredDate);
95 db.AddInParameter(command, " @ShippedDate " , DbType.DateTime, item.ShippedDate);
96 db.AddInParameter(command, " @ShipVia " , DbType.Int32, item.ShipVia);
97 db.AddInParameter(command, " @Freight " , DbType.Decimal, item.Freight);
98 db.AddInParameter(command, " @ShipName " , DbType.String, item.ShipName);
99 db.AddInParameter(command, " @ShipAddress " , DbType.String, item.ShipAddress);
100 db.AddInParameter(command, " @ShipCity " , DbType.String, item.ShipCity);
101 db.AddInParameter(command, " @ShipRegion " , DbType.String, item.ShipRegion);
102 db.AddInParameter(command, " @ShipPostalCode " , DbType.String, item.ShipPostalCode);
103 db.AddInParameter(command, " @ShipCountry " , DbType.String, item.ShipCountry);
104 db.ExecuteNonQuery(command);
105 command.Dispose();
106 }
107
108 public void delete(Orders item)
109 {
110 Database db = DbUtility.getDatabase();
111 // TODO:修改以下where语句
112 string sql = @" delete from Orders
113 where OrderID=@OrderID " ;
114 DbCommand command = db.GetSqlStringCommand(sql);
115 // 为数据库命令添加参数
116 db.AddInParameter(command, " @OrderID " , DbType.Int32, item.OrderID);
117 db.ExecuteNonQuery(command);
118 command.Dispose();
119 }
120 }
121 }
2 using Microsoft.Practices.EnterpriseLibrary.Data;
3 using System.Data;
4 using System;
5 namespace Sjl.Utility.Dll
6 {
7 public class OrdersDal
8 {
9 const string Table = " Orders " ;
10 // 非主键字段
11 const string AllButKey = " CustomerID,EmployeeID,OrderDate,RequiredDate,ShippedDate,ShipVia,Freight,ShipName,ShipAddress,ShipCity,ShipRegion,ShipPostalCode,ShipCountry " ;
12 // 所有字段(包括主键
13 const string AllColumns = " OrderID, " + AllButKey;
14 const string SelectAll = " select " + AllColumns + " from " + Table;
15 private Orders fromReader(IDataReader reader)
16 {
17 Orders item = new Orders();
18 item.OrderID = DbUtility.getInt32(reader[ " OrderID " ]).Value;
19 item.CustomerID = DbUtility.getString(reader[ " CustomerID " ]);
20 item.EmployeeID = DbUtility.getInt32(reader[ " EmployeeID " ]);
21 item.OrderDate = DbUtility.getDateTime(reader[ " OrderDate " ]);
22 item.RequiredDate = DbUtility.getDateTime(reader[ " RequiredDate " ]);
23 item.ShippedDate = DbUtility.getDateTime(reader[ " ShippedDate " ]);
24 item.ShipVia = DbUtility.getInt32(reader[ " ShipVia " ]);
25 item.Freight = DbUtility.getDecimal(reader[ " Freight " ]);
26 item.ShipName = DbUtility.getString(reader[ " ShipName " ]);
27 item.ShipAddress = DbUtility.getString(reader[ " ShipAddress " ]);
28 item.ShipCity = DbUtility.getString(reader[ " ShipCity " ]);
29 item.ShipRegion = DbUtility.getString(reader[ " ShipRegion " ]);
30 item.ShipPostalCode = DbUtility.getString(reader[ " ShipPostalCode " ]);
31 item.ShipCountry = DbUtility.getString(reader[ " ShipCountry " ]);
32 return item;
33 }
34 public Orders get (Int32 orderid)
35 {
36 string sql = SelectAll + " where OrderID=@OrderID " ;
37 Database db = DbUtility.getDatabase();
38 DbCommand command = db.GetSqlStringCommand(sql);
39 Orders item = new Orders();
40 item.OrderID = orderid;
41 db.AddInParameter(command, " @OrderID " , DbType.Int32, item.OrderID);
42 IDataReader reader = db.ExecuteReader(command);
43 item = null ;
44 if (reader.Read())
45 item = fromReader(reader);
46 reader.Close();
47 command.Dispose();
48 return item;
49 }
50 // 插入新数据
51 public void insert(Orders item)
52 {
53 Database db = DbUtility.getDatabase();
54 // 以下为生成的insert语句
55 string sql = @" insert into Orders
56 (
57 OrderID,CustomerID,EmployeeID,OrderDate,RequiredDate,ShippedDate,ShipVia,Freight,ShipName,ShipAddress,ShipCity,ShipRegion,ShipPostalCode,ShipCountry)
58 values (
59 @OrderID,@CustomerID,@EmployeeID,@OrderDate,@RequiredDate,@ShippedDate,@ShipVia,@Freight,@ShipName,@ShipAddress,@ShipCity,@ShipRegion,@ShipPostalCode,@ShipCountry) " ;
60 DbCommand command = db.GetSqlStringCommand(sql);
61 // 为数据库命令添加参数
62 db.AddInParameter(command, " @OrderID " , DbType.Int32, item.OrderID);
63 db.AddInParameter(command, " @CustomerID " , DbType.String, item.CustomerID);
64 db.AddInParameter(command, " @EmployeeID " , DbType.Int32, item.EmployeeID);
65 db.AddInParameter(command, " @OrderDate " , DbType.DateTime, item.OrderDate);
66 db.AddInParameter(command, " @RequiredDate " , DbType.DateTime, item.RequiredDate);
67 db.AddInParameter(command, " @ShippedDate " , DbType.DateTime, item.ShippedDate);
68 db.AddInParameter(command, " @ShipVia " , DbType.Int32, item.ShipVia);
69 db.AddInParameter(command, " @Freight " , DbType.Decimal, item.Freight);
70 db.AddInParameter(command, " @ShipName " , DbType.String, item.ShipName);
71 db.AddInParameter(command, " @ShipAddress " , DbType.String, item.ShipAddress);
72 db.AddInParameter(command, " @ShipCity " , DbType.String, item.ShipCity);
73 db.AddInParameter(command, " @ShipRegion " , DbType.String, item.ShipRegion);
74 db.AddInParameter(command, " @ShipPostalCode " , DbType.String, item.ShipPostalCode);
75 db.AddInParameter(command, " @ShipCountry " , DbType.String, item.ShipCountry);
76 db.ExecuteNonQuery(command);
77 command.Dispose();
78 }
79 // 更新数据
80 public void update(Orders item)
81 {
82 Database db = DbUtility.getDatabase();
83 // 以下为生成的update语句
84 string sql = @" update Orders
85 set
86 CustomerID=@CustomerID,EmployeeID=@EmployeeID,OrderDate=@OrderDate,RequiredDate=@RequiredDate,ShippedDate=@ShippedDate,ShipVia=@ShipVia,Freight=@Freight,ShipName=@ShipName,ShipAddress=@ShipAddress,ShipCity=@ShipCity,ShipRegion=@ShipRegion,ShipPostalCode=@ShipPostalCode,ShipCountry=@ShipCountry
87 where OrderID=@OrderID " ;
88 DbCommand command = db.GetSqlStringCommand(sql);
89 // 为数据库命令添加参数
90 db.AddInParameter(command, " @OrderID " , DbType.Int32, item.OrderID);
91 db.AddInParameter(command, " @CustomerID " , DbType.String, item.CustomerID);
92 db.AddInParameter(command, " @EmployeeID " , DbType.Int32, item.EmployeeID);
93 db.AddInParameter(command, " @OrderDate " , DbType.DateTime, item.OrderDate);
94 db.AddInParameter(command, " @RequiredDate " , DbType.DateTime, item.RequiredDate);
95 db.AddInParameter(command, " @ShippedDate " , DbType.DateTime, item.ShippedDate);
96 db.AddInParameter(command, " @ShipVia " , DbType.Int32, item.ShipVia);
97 db.AddInParameter(command, " @Freight " , DbType.Decimal, item.Freight);
98 db.AddInParameter(command, " @ShipName " , DbType.String, item.ShipName);
99 db.AddInParameter(command, " @ShipAddress " , DbType.String, item.ShipAddress);
100 db.AddInParameter(command, " @ShipCity " , DbType.String, item.ShipCity);
101 db.AddInParameter(command, " @ShipRegion " , DbType.String, item.ShipRegion);
102 db.AddInParameter(command, " @ShipPostalCode " , DbType.String, item.ShipPostalCode);
103 db.AddInParameter(command, " @ShipCountry " , DbType.String, item.ShipCountry);
104 db.ExecuteNonQuery(command);
105 command.Dispose();
106 }
107
108 public void delete(Orders item)
109 {
110 Database db = DbUtility.getDatabase();
111 // TODO:修改以下where语句
112 string sql = @" delete from Orders
113 where OrderID=@OrderID " ;
114 DbCommand command = db.GetSqlStringCommand(sql);
115 // 为数据库命令添加参数
116 db.AddInParameter(command, " @OrderID " , DbType.Int32, item.OrderID);
117 db.ExecuteNonQuery(command);
118 command.Dispose();
119 }
120 }
121 }