版权所有:基础软件。作者邮箱:sun.j.l.studio@gmail.com。本文首发于 http://www.cnblogs.com/FoundationSoft。文章转载请保持此版权信息并注明出处。
数据访问层包括CRUD功能。自动生成代码时,可以自动生成这4个方法。本例以Northwind数据库中的Orders表为例,说明如何自动生成数据访问层的insert方法,以实现插入数据的功能。当然,生成代码的代码是与数据库表无关的,可以生成任意表的代码。这里以Orders表为例,只是为了便于讨论最终生成的代码结构。
先来看下要生成的insert方法代码。如果不用自动生成,那么这些代码就应该是手写的。看这些代码的目的是为了明确最终我们要得到的产品是什么样子的,从而能够知道中间过程所做工作的目的。
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1
//
插入新数据
2 public void insert(Orders item)
3 {
4 Database db = DbUtility.getDatabase();
5 // 以下为生成的insert语句
6 string sql = @" insert into Orders (
7 OrderID,CustomerID,EmployeeID,OrderDate,RequiredDate,ShippedDate,ShipVia,Freight,ShipName,ShipAddress,ShipCity,ShipRegion,ShipPostalCode,ShipCountry)
8 values (
9 @OrderID,@CustomerID,@EmployeeID,@OrderDate,@RequiredDate,@ShippedDate,@ShipVia,@Freight,@ShipName,@ShipAddress,@ShipCity,@ShipRegion,@ShipPostalCode,@ShipCountry) " ;
10 DbCommand command = db.GetSqlStringCommand(sql);
11 // 为数据库命令添加参数
12 db.AddInParameter(command, " @OrderID " ,DbType.Int32,item.OrderID);
13 db.AddInParameter(command, " @CustomerID " ,DbType.String,item.CustomerID);
14 db.AddInParameter(command, " @EmployeeID " ,DbType.Int32,item.EmployeeID);
15 db.AddInParameter(command, " @OrderDate " ,DbType.DateTime,item.OrderDate);
16 db.AddInParameter(command, " @RequiredDate " ,DbType.DateTime,item.RequiredDate);
17 db.AddInParameter(command, " @ShippedDate " ,DbType.DateTime,item.ShippedDate);
18 db.AddInParameter(command, " @ShipVia " ,DbType.Int32,item.ShipVia);
19 db.AddInParameter(command, " @Freight " ,DbType.Decimal,item.Freight);
20 db.AddInParameter(command, " @ShipName " ,DbType.String,item.ShipName);
21 db.AddInParameter(command, " @ShipAddress " ,DbType.String,item.ShipAddress);
22 db.AddInParameter(command, " @ShipCity " ,DbType.String,item.ShipCity);
23 db.AddInParameter(command, " @ShipRegion " ,DbType.String,item.ShipRegion);
24 db.AddInParameter(command, " @ShipPostalCode " ,DbType.String,item.ShipPostalCode);
25 db.AddInParameter(command, " @ShipCountry " ,DbType.String,item.ShipCountry);
26 db.ExecuteNonQuery(command);
27 command.Dispose();
28 }
2 public void insert(Orders item)
3 {
4 Database db = DbUtility.getDatabase();
5 // 以下为生成的insert语句
6 string sql = @" insert into Orders (
7 OrderID,CustomerID,EmployeeID,OrderDate,RequiredDate,ShippedDate,ShipVia,Freight,ShipName,ShipAddress,ShipCity,ShipRegion,ShipPostalCode,ShipCountry)
8 values (
9 @OrderID,@CustomerID,@EmployeeID,@OrderDate,@RequiredDate,@ShippedDate,@ShipVia,@Freight,@ShipName,@ShipAddress,@ShipCity,@ShipRegion,@ShipPostalCode,@ShipCountry) " ;
10 DbCommand command = db.GetSqlStringCommand(sql);
11 // 为数据库命令添加参数
12 db.AddInParameter(command, " @OrderID " ,DbType.Int32,item.OrderID);
13 db.AddInParameter(command, " @CustomerID " ,DbType.String,item.CustomerID);
14 db.AddInParameter(command, " @EmployeeID " ,DbType.Int32,item.EmployeeID);
15 db.AddInParameter(command, " @OrderDate " ,DbType.DateTime,item.OrderDate);
16 db.AddInParameter(command, " @RequiredDate " ,DbType.DateTime,item.RequiredDate);
17 db.AddInParameter(command, " @ShippedDate " ,DbType.DateTime,item.ShippedDate);
18 db.AddInParameter(command, " @ShipVia " ,DbType.Int32,item.ShipVia);
19 db.AddInParameter(command, " @Freight " ,DbType.Decimal,item.Freight);
20 db.AddInParameter(command, " @ShipName " ,DbType.String,item.ShipName);
21 db.AddInParameter(command, " @ShipAddress " ,DbType.String,item.ShipAddress);
22 db.AddInParameter(command, " @ShipCity " ,DbType.String,item.ShipCity);
23 db.AddInParameter(command, " @ShipRegion " ,DbType.String,item.ShipRegion);
24 db.AddInParameter(command, " @ShipPostalCode " ,DbType.String,item.ShipPostalCode);
25 db.AddInParameter(command, " @ShipCountry " ,DbType.String,item.ShipCountry);
26 db.ExecuteNonQuery(command);
27 command.Dispose();
28 }
分析以上代码,其思路为,(1)先定义一条insert的sql语句,(2)根据sql语句生成一个DbCommand,(3)为DbCommand的各个字段添加参数,(4)然后执行DbCommand。
自动生成代码也是按照上述步骤来进行。
第一步,生成insert的sql语句。这个语句字符串需要根据表结构来生成。代码如下。
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1
2 // 生成insert的sql语句
3 private string getInsertSql()
4 {
5 StringBuilder sb = new StringBuilder();
6 string temp = null ;
7 temp = " insert into " + table + " ( " ;
8 sb.Append(temp + Environment.NewLine);
9 // 添加各列
10 foreach ( string n in columnNames)
11 {
12 sb.Append(n + " , " );
13 }
14 // 去掉最后一个逗号
15 sb.Remove(sb.Length - 1 , 1 );
16 temp = " ) " + Environment.NewLine + " values ( " + Environment.NewLine;
17 sb.Append(temp);
18 // 添加各个参数
19 foreach ( string n in columnNames)
20 {
21 sb.Append( " @ " + n + " , " );
22 }
23 // 去掉最后一个逗号
24 sb.Remove(sb.Length - 1 , 1 );
25 sb.Append( " ) " );
26 return sb.ToString();
27 }
2 // 生成insert的sql语句
3 private string getInsertSql()
4 {
5 StringBuilder sb = new StringBuilder();
6 string temp = null ;
7 temp = " insert into " + table + " ( " ;
8 sb.Append(temp + Environment.NewLine);
9 // 添加各列
10 foreach ( string n in columnNames)
11 {
12 sb.Append(n + " , " );
13 }
14 // 去掉最后一个逗号
15 sb.Remove(sb.Length - 1 , 1 );
16 temp = " ) " + Environment.NewLine + " values ( " + Environment.NewLine;
17 sb.Append(temp);
18 // 添加各个参数
19 foreach ( string n in columnNames)
20 {
21 sb.Append( " @ " + n + " , " );
22 }
23 // 去掉最后一个逗号
24 sb.Remove(sb.Length - 1 , 1 );
25 sb.Append( " ) " );
26 return sb.ToString();
27 }
第二步,生成insert方法。代码如下。
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1
//
生成数据访问类insert方法代码
2 public string generateInsertMethod()
3 {
4 StringBuilder sb = new StringBuilder();
5 string temp = null ;
6 temp = " //插入新数据 " ;
7 sb.Append(temp + Environment.NewLine);
8 temp = " public void insert( " + table + " item) " ;
9 sb.Append(temp + Environment.NewLine);
10 sb.Append( " { " + Environment.NewLine);
11 temp = " Database db=DbUtility.getDatabase(); " ;
12 sb.Append(temp + Environment.NewLine);
13 temp = " //以下为生成的insert语句 " ;
14 sb.Append(temp + Environment.NewLine);
15 temp = " string sql=@\ "" + getInsertSql() + " \ " ; " ;
16 sb.Append(temp + Environment.NewLine);
17 temp = " DbCommand command=db.GetSqlStringCommand(sql); " ;
18 sb.Append(temp + Environment.NewLine);
19 temp = " //为数据库命令添加参数 " ;
20 sb.Append(temp + Environment.NewLine);
21 temp = getAddParameterCode();
22 sb.Append(temp);
23 temp = " db.ExecuteNonQuery(command); " ;
24 sb.Append(temp + Environment.NewLine);
25 temp = " command.Dispose(); " ;
26 sb.Append(temp + Environment.NewLine);
27 sb.Append( " } " + Environment.NewLine);
28 return sb.ToString();
29 }
2 public string generateInsertMethod()
3 {
4 StringBuilder sb = new StringBuilder();
5 string temp = null ;
6 temp = " //插入新数据 " ;
7 sb.Append(temp + Environment.NewLine);
8 temp = " public void insert( " + table + " item) " ;
9 sb.Append(temp + Environment.NewLine);
10 sb.Append( " { " + Environment.NewLine);
11 temp = " Database db=DbUtility.getDatabase(); " ;
12 sb.Append(temp + Environment.NewLine);
13 temp = " //以下为生成的insert语句 " ;
14 sb.Append(temp + Environment.NewLine);
15 temp = " string sql=@\ "" + getInsertSql() + " \ " ; " ;
16 sb.Append(temp + Environment.NewLine);
17 temp = " DbCommand command=db.GetSqlStringCommand(sql); " ;
18 sb.Append(temp + Environment.NewLine);
19 temp = " //为数据库命令添加参数 " ;
20 sb.Append(temp + Environment.NewLine);
21 temp = getAddParameterCode();
22 sb.Append(temp);
23 temp = " db.ExecuteNonQuery(command); " ;
24 sb.Append(temp + Environment.NewLine);
25 temp = " command.Dispose(); " ;
26 sb.Append(temp + Environment.NewLine);
27 sb.Append( " } " + Environment.NewLine);
28 return sb.ToString();
29 }
上面的代码用到以下一个方法。
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1
private
string
getAddParameterCode()
2 {
3 return getAddParameterCode(allColumns);
4 }
5 /// <summary>
6 /// 生成向DbCommand添加参数的语句
7 /// </summary>
8 /// <param name="columns"> 要添加参数的列(每列添加一个参数) </param>
9 /// <returns> 生成的代码 </returns>
10 private string getAddParameterCode(DataColumn[] columns)
11 {
12 StringBuilder sb = new StringBuilder();
13 string temp = null ;
14 foreach (DataColumn c in columns)
15 {
16 temp = " db.AddInParameter(command,\ "@" + c.ColumnName + " \ " ,DbType. " + c.DataType.Name + " ,item. " + c.ColumnName + " ); " ;
17 sb.Append(temp);
18 sb.Append(Environment.NewLine);
19 }
20 return sb.ToString();
21 }
2 {
3 return getAddParameterCode(allColumns);
4 }
5 /// <summary>
6 /// 生成向DbCommand添加参数的语句
7 /// </summary>
8 /// <param name="columns"> 要添加参数的列(每列添加一个参数) </param>
9 /// <returns> 生成的代码 </returns>
10 private string getAddParameterCode(DataColumn[] columns)
11 {
12 StringBuilder sb = new StringBuilder();
13 string temp = null ;
14 foreach (DataColumn c in columns)
15 {
16 temp = " db.AddInParameter(command,\ "@" + c.ColumnName + " \ " ,DbType. " + c.DataType.Name + " ,item. " + c.ColumnName + " ); " ;
17 sb.Append(temp);
18 sb.Append(Environment.NewLine);
19 }
20 return sb.ToString();
21 }