昨天觉得添加的存储过程模板写的比较简单,今天准备详细介绍一下这个删除的模板。
首先介绍我们使用到的一个教本函数GetSqlParameterStatement(ColumnSchema column),其函数代码如下:
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
3
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
4
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
5
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
6
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
8
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
9
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
10
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
11
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
12
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
13
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
14
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
15
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
16
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
17
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
18
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
19
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
20
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
21
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
大家可以看到,这个函数需要传入一个ColumnSchema类型的参数,它代表一个数据表中的列,并且是一个列,然后根据ColumnSchema这个类具有的属性,对传入的列进行一些操作然后返回我们生成存储过程时需要的代码。
首先介绍一下ColumnSchema的一些常用属性,如下表:
属性Property |
描述Description |
AllowDBNull |
是否允许空值NULL |
Database |
通过DatabaseSchema对象得到当前列所属的数据库 |
DataType |
此数据对象的数据类型 |
Description |
当前对象的描述 |
ExtendedProperties |
用来存储SchemaObject的其他附加信息 |
IsForeignKeyMember |
当前列是否为外键 |
IsPrimaryKeyMember |
当前列是否为主键 |
IsUnique |
当前列是否唯一 |
Name |
列的名称 |
NativeType |
列定义的数据类型 |
Precision |
数据对象的精度 |
Scale |
数据对象的范围(个人理解为需要保留小数的范围) |
Size |
数据对象的大小(例如:字符串长度为10) |
SystemType |
数据对象的系统类型 |
Table |
当前列所属的数据表 |
下面为我们首先要生成存储过程,要自动生成的代码分成了红、绿、蓝三部分。
CREATE PROCEDURE dbo.Customer
/*
==================================================
Author:Bear-Study-Hard
CreatedTime:
==================================================
*/
@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属性。
剩下的绿色部分和蓝色部分生成时比较简单,请各位自行学习。模板代码为:
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
45
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)
46
![](https://bear-study-hard.cnblogs.com/Images/OutliningIndicators/None.gif)