.NET数据库编程求索之路--6.使用ADO.NET实现（三层架构篇-使用List传递数据-基于存储过程）（2）

Posted on 2012-07-27 00:08 SummerRain 阅读(219) 评论(0) 编辑收藏举报

6.使用ADO.NET实现（三层架构篇-使用List传递数据-基于存储过程）（2）

【夏春涛 email: xchuntao@163.com blog: http://www.cnblogs.com/SummerRain 】

6.3 存储过程

Order相关:

1 -- =============================================
2 -- 创建人: 夏春涛
3 -- 创建时间: 2011-12-31
4 -- 功能描述: 订单新增
5 -- =============================================
6 CREATE PROCEDURE Order_Insert
7     @OrderID int output,
8     @OrderTime datetime,
9     @OrderStateCode char(1),
10     @CustomerName varchar(30),
11     @CustomerPhoneNo varchar(15),
12     @CustomerAddress varchar(60)
13 AS
14 BEGIN
15     INSERT INTO [Order](
16         [OrderTime],
17         [OrderStateCode],
18         [CustomerName],
19         [CustomerPhoneNo],
20         [CustomerAddress])
21     VALUES(
22         @OrderTime,
23         @OrderStateCode,
24         @CustomerName,
25         @CustomerPhoneNo,
26         @CustomerAddress)
27     SET @OrderID = SCOPE_IDENTITY()
28 END

1 -- =============================================
2 -- 创建人: 夏春涛
3 -- 创建时间: 2011-12-31
4 -- 功能描述: 订单删除
5 -- =============================================
6 CREATE PROCEDURE Order_Delete
7     @OrderID int
8 AS
9 BEGIN
10     begin tran MyTran --事务开始
11
12         DELETE FROM [OrderItem]
13         WHERE [OrderID] = @OrderID
14
15         DELETE FROM [Order]
16         WHERE [OrderID] = @OrderID
17
18     --事务回滚或提交
19     if (@@ERROR <> 0)
20         rollback tran MyTran
21     else
22         commit tran MyTran
23
24 END

1 -- =============================================
2 -- 创建人: 夏春涛
3 -- 创建时间: 2011-12-31
4 -- 功能描述: 订单查询
5 -- =============================================
6 CREATE PROCEDURE Order_Select
7     @predicate varchar(200) --查询条件
8 AS
9 BEGIN
10     declare @sql varchar(1000)
11     set @sql = 'SELECT [OrderID],
12                        [CustomerName],
13                        [CustomerPhoneNo],
14                        [CustomerAddress],
15                        [OrderTime],
16                        [OrderStateCode],
17                        [OrderState].[Name] AS [OrderStateName]
18                 FROM [Order]
19                 LEFT OUTER JOIN [OrderState]
20                 ON [Order].[OrderStateCode] = [OrderState].[Code]';
21     if (ltrim(rtrim(@predicate)))!=''
22         set @sql = @sql + '  WHERE  ' + @predicate;
23     set @sql = @sql + ' ORDER BY [OrderID] DESC ';
24
25     print(@sql)
26
27     exec(@sql)
28 END

1 -- =============================================
2 -- 创建人: 夏春涛
3 -- 创建时间: 2011-12-31
4 -- 功能描述: 订单更新
5 -- =============================================
6 CREATE/ALTER PROCEDURE Order_Update
7     @OrderID int,
8     @OrderTime datetime,
9     @OrderStateCode char(1),
10     @CustomerName varchar(30),
11     @CustomerPhoneNo varchar(15),
12     @CustomerAddress varchar(60)
13 AS
14 BEGIN
15     UPDATE [Order]
16     SET [OrderTime] = @OrderTime,
17         [OrderStateCode] = @OrderStateCode,
18         [CustomerName] = @CustomerName,
19         [CustomerPhoneNo] = @CustomerPhoneNo,
20         [CustomerAddress] = @CustomerAddress
21     WHERE [OrderID] = @OrderID
22 END

OrderItem相关：

1 -- =============================================
2 -- 创建人: 夏春涛
3 -- 创建时间: 2011-12-31
4 -- 功能描述: 新增订单详情
5 -- =============================================
6 ALTER PROCEDURE [dbo].[OrderItem_Insert]
7     @OrderItemID int output,
8     @OrderID int,
9     @Product varchar(30),
10     @UnitPrice decimal(18,2),
11     @Quantity int
12 AS
13 BEGIN
14     INSERT INTO [OrderItem](
15         [OrderID],
16         [Product],
17         [UnitPrice],
18         [Quantity])
19     VALUES(
20         @OrderID,
21         @Product,
22         @UnitPrice,
23         @Quantity)
24
25     SET @OrderItemID = SCOPE_IDENTITY()
26 END

1 -- =============================================
2 -- 创建人: 夏春涛
3 -- 创建时间: 2011-12-31
4 -- 功能描述: 删除订单详情
5 -- =============================================
6 CREATE PROCEDURE OrderItem_Delete
7     @OrderItemID int
8 AS
9 BEGIN
10     DELETE FROM [OrderItem]
11     WHERE [OrderItemID] = @OrderItemID
12 END

1 -- =============================================
2 -- 创建人: 夏春涛
3 -- 创建时间: 2011-12-31
4 -- 功能描述: 订单详情查询
5 -- =============================================
6 CREATE PROCEDURE OrderItem_Select
7     @OrderID int
8 AS
9 BEGIN
10     SELECT [OrderItemID],
11            [OrderID],
12            [Product],
13            [UnitPrice],
14            [Quantity]
15     FROM [OrderItem]
16     WHERE [OrderID] = @OrderID
17 END

1 -- =============================================
2 -- 创建人: 夏春涛
3 -- 创建时间: 2011-12-31
4 -- 功能描述: 更新订单详情
5 -- =============================================
6 CREATE PROCEDURE OrderItem_Update
7     @OrderItemID int,
8     @OrderID int,
9     @Product varchar(30),
10     @UnitPrice decimal(18,2),
11     @Quantity int
12 AS
13 BEGIN
14     UPDATE [OrderItem]
15     SET [OrderID] = @OrderID,
16         [Product] = @Product,
17         [UnitPrice] = @UnitPrice,
18         [Quantity] = @Quantity
19     WHERE [OrderItemID] = @OrderItemID
20 END

OrderState相关：

1 -- =============================================
2 -- 创建人: 夏春涛
3 -- 创建时间: 2011-12-31
4 -- 功能描述: 订单状态查询
5 -- =============================================
6 CREATE PROCEDURE OrderState_Select
7 AS
8 BEGIN
9 SELECT * FROM [OrderState]
10 ORDER BY [Code]
11 END

数据库文件：/Files/SummerRain/NetDbDevRoad/HomeShopDB.rar

完整源代码：/Files/SummerRain/NetDbDevRoad/6使用ADONET实现三层架构ListSP.rar