CodeSmith应用（二）

缺乏、安全感 2021-12-20 12:07 160阅读 0赞

今天又根据CodeSmith的几个基本组件写出了基于表生成删除功能的存储过程代码生成模板。  
昨天觉得添加的存储过程模板写的比较简单，今天准备详细介绍一下这个删除的模板。  
首先介绍我们使用到的一个教本函数GetSqlParameterStatement(ColumnSchema column)，其函数代码如下：

1 ![None.gif][] public  string  GetSqlParameterStatement(ColumnSchema column)  
  2 ![ExpandedBlockStart.gif][]![ContractedBlock.gif][] ![dot.gif][] \{  
 3![InBlock.gif][]string param ="@"\+ column.Name \+""\+ column.NativeType;  
 4![InBlock.gif][]switch (column.DataType)  
 5![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]![dot.gif][]\{  
 6![InBlock.gif][]case DbType.Decimal:  
 7![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]![dot.gif][]\{  
 8![InBlock.gif][] param \+="("\+ column.Precision \+", "\+ column.Scale \+")";  
 9![InBlock.gif][]break;  
10![ExpandedSubBlockEnd.gif][] \}  
11![InBlock.gif][]default:  
12![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]![dot.gif][]\{  
13![InBlock.gif][]if (column.Size >0)  
14![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]![dot.gif][]\{  
15![InBlock.gif][] param \+="("\+ column.Size \+")";  
16![ExpandedSubBlockEnd.gif][] \}  
17![InBlock.gif][]break;  
18![ExpandedSubBlockEnd.gif][] \}  
19![ExpandedSubBlockEnd.gif][] \}  
20![InBlock.gif][]return param;  
21![ExpandedBlockEnd.gif][]\}

大家可以看到，这个函数需要传入一个ColumnSchema类型的参数，它代表一个数据表中的列，并且是一个列，然后根据ColumnSchema这个类具有的属性，对传入的列进行一些操作然后返回我们生成存储过程时需要的代码。  
首先介绍一下ColumnSchema的一些常用属性，如下表：

<table> 
 <tbody> 
 <tr> 
 <td> 属性Property</td> 
 <td> 描述Description</td> 
 </tr> 
 <tr> 
 <td> AllowDBNull</td> 
 <td> 是否允许空值NULL</td> 
 </tr> 
 <tr> 
 <td> Database</td> 
 <td> 通过DatabaseSchema对象得到当前列所属的数据库</td> 
 </tr> 
 <tr> 
 <td> DataType</td> 
 <td> 此数据对象的数据类型</td> 
 </tr> 
 <tr> 
 <td> Description</td> 
 <td> 当前对象的描述</td> 
 </tr> 
 <tr> 
 <td> ExtendedProperties</td> 
 <td> 用来存储SchemaObject的其他附加信息</td> 
 </tr> 
 <tr> 
 <td> IsForeignKeyMember</td> 
 <td> 当前列是否为外键</td> 
 </tr> 
 <tr> 
 <td> IsPrimaryKeyMember</td> 
 <td> 当前列是否为主键</td> 
 </tr> 
 <tr> 
 <td> IsUnique</td> 
 <td> 当前列是否唯一</td> 
 </tr> 
 <tr> 
 <td> Name</td> 
 <td> 列的名称</td> 
 </tr> 
 <tr> 
 <td> NativeType</td> 
 <td> 列定义的数据类型</td> 
 </tr> 
 <tr> 
 <td> Precision</td> 
 <td> 数据对象的精度</td> 
 </tr> 
 <tr> 
 <td> Scale</td> 
 <td> 数据对象的范围（个人理解为需要保留小数的范围）</td> 
 </tr> 
 <tr> 
 <td> Size</td> 
 <td> 数据对象的大小（例如：字符串长度为10）</td> 
 </tr> 
 <tr> 
 <td> SystemType</td> 
 <td> 数据对象的系统类型</td> 
 </tr> 
 <tr> 
 <td> Table</td> 
 <td> 当前列所属的数据表</td> 
 </tr> 
 </tbody> 
</table>

下面为我们首先要生成存储过程，要自动生成的代码分成了红、绿、蓝三部分。 
CREATE PROCEDURE dbo.Customer<?xml:namespace prefix = st2 />sDelete 
/\* 
================================================== 
Author:Bear-Study-Hard 
CreatedTime:<?xml:namespace prefix = st1 />2005-12-28 
Description:Delete a record from table Customers 
================================================== 
\*/ 
@CustomerID nchar(5) --客户ID 
AS 
Delete From \[Customers\] 
Where 
\[CustomerID\] = @CustomerID 
 
 我们的这个脚本函数就是要实现拼出红色的部分，GetSqlParameterStatement函数接收到ColumnSchema类型的参数后，从其Name属性和NativeType属性拼出@CustomerID nchar部分，然后由于不同的数据类型尤其是数值类型和字符串类型的区别，会导致数据类型的大小会有所不同，这里仅对Decimal的数据类型进行了判断（Numeric和float等均需要这种处理），然后根据Precision属性得到精度并通过Scale属性得到了需要保留小数的范围。如果传出的为非Decimal类型字段则直接通过Size属性取出其大小即可。得到了(5)部分。最后的注释是为了生成的存储过程解读性好加上的，使用的是Description属性。 
剩下的绿色部分和蓝色部分生成时比较简单，请各位自行学习。模板代码为：

1 ![None.gif][] < %@ CodeTemplate Language ="C\#" TargetLanguage ="T-SQL" Description ="Create a procedure which have delete function base on a table.Must use PrimaryKey to delete a record." % > 
 2 ![None.gif][] < %@ Assembly Name ="SchemaExplorer" % > 
 3 ![None.gif][] < %@ Import Namespace ="SchemaExplorer" % > 
 4 ![None.gif][] < %@ Property Name ="SourceTable" Type ="SchemaExplorer.TableSchema" Category ="DataTable" Description ="Table that the stored procedures should be based on." % > 
 5 ![None.gif][] < %@ Property Name ="Author" Type ="String" Category ="Context" Description ="The author for this procedure." Optional ="true" % > 
 6 ![None.gif][] < %@ Property Name ="Description" Type ="String" Category ="Context" Description ="The description for this procedure." Optional ="true" % > 
 7 ![None.gif][] < script runat ="template" > 
 8 ![None.gif][]public string GetSqlParameterStatement(ColumnSchema column) 
 9 ![None.gif][]\{ 
 10 ![None.gif][] string param = "@" + column.Name + " " + column.NativeType; 
 11 ![None.gif][] switch (column.DataType) 
 12 ![None.gif][] \{ 
 13 ![None.gif][] case DbType.Decimal: 
 14 ![None.gif][] \{ 
 15 ![None.gif][] param += "(" + column.Precision + ", " + column.Scale + ")"; 
 16 ![None.gif][] break; 
 17 ![None.gif][] \} 
 18 ![None.gif][] default: 
 19 ![None.gif][] \{ 
 20 ![None.gif][] if (column.Size > 0) 
 21 ![None.gif][] \{ 
 22 ![None.gif][] param += "(" + column.Size + ")"; 
 23 ![None.gif][] \} 
 24 ![None.gif][] break; 
 25 ![None.gif][] \} 
 26 ![None.gif][] \} 
 27 ![None.gif][] return param; 
 28 ![None.gif][]\} 
 29 ![None.gif][] </ script > 
 30 ![None.gif][]CREATE PROCEDURE dbo. < %=SourceTable .Name % > Delete 
 31 ![None.gif][]/\* 
 32 ![None.gif][]================================================== 
 33 ![None.gif][]Author: < %= Author % > 
 34 ![None.gif][]CreatedTime: < %= System .DateTime.Now.ToShortDateString() % > 
 35 ![None.gif][]Description: < %= Description % > 
 36 ![None.gif][]================================================== 
 37 ![None.gif][]\*/ 
 38 ![None.gif][] < % for (int i = 0; i < SourceTable.PrimaryKey.MemberColumns.Count; i++) \{ % > 
 39 ![None.gif][] < %= GetSqlParameterStatement (SourceTable.PrimaryKey.MemberColumns\[i\]) % >< % if (i < SourceTable.PrimaryKey.MemberColumns.Count - 1) \{ % > , < % \} % > < % if (SourceTable.Columns\[i\].Description ! = "" ) \{ % > \-- < %= SourceTable .Columns\[i\].Description % >< % \} % > 
 40 ![None.gif][] < % \} % > 
 41 ![None.gif][]AS 
 42 ![None.gif][]Delete From \[ < %= SourceTable .Name % > \] 
 43 ![None.gif][]Where 
 44 ![None.gif][] < % for (int i = 0; i < SourceTable.PrimaryKey.MemberColumns.Count; i++) \{ % > 
 45 ![None.gif][] < % if (i > 0) \{ %>AND < % \} % > \[ < %= SourceTable .PrimaryKey.MemberColumns\[i\].Name % > \] = @ < %= SourceTable .PrimaryKey.MemberColumns\[i\].Name % > 
 46 ![None.gif][] < % \} % >

转载于:https://www.cnblogs.com/jirong/archive/2006/04/13/374454.html

[None.gif]: /images/20211220/055e498114a747a095ac4923d6b237a8.png
[ExpandedBlockStart.gif]: https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif
[ContractedBlock.gif]: https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif
[dot.gif]: /images/20211220/3f7072f95c3d49b68e0e19bbd3280677.png
[InBlock.gif]: /images/20211220/34925152f7f64de29b483009d7c8eca2.png
[ExpandedSubBlockStart.gif]: https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif
[ContractedSubBlock.gif]: https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif
[ExpandedSubBlockEnd.gif]: /images/20211220/c45ba3086f5c4a9b99482fce170501b2.png
[ExpandedBlockEnd.gif]: /images/20211220/faf84f963b1f4b8a8f9da23d85cc4c36.png