2. 示范代码，以事务方式调用多个存储过程
C#实体类型，成员与数据库表对应，这里就不给出表结构截图了。
- public sealed class OrderItem
- {
- public int OrderID { get; set; }
- public int? CustomerID { get; set; }
- public DateTime OrderDate { get; set; }
- public decimal SumMoney { get; set; }
- [ItemField(OnlyLoadAll = true)]
- public string Comment { get; set; }
- public bool Finished { get; set; }
- public string CustomerName { get; set; }
-
- [ItemField(IgnoreLoad=true)]
- public List<OrderDetail> Detail;
- }
-
- public sealed class OrderDetail
- {
- public int OrderID { get; set; }
- public int ProductID { get; set; }
- public decimal UnitPrice { get; set; }
- public int Quantity { get; set; }
- }
|
三个存储过程，用于插入主表，子表，刷新总金额
- create procedure [dbo].[InsertOrder](
- @CustomerID int = null,
- @SumMoney money,
- @Comment nvarchar(300),
- @OrderID int output
- )
- as
- begin
-
- insert into Orders( CustomerID, OrderDate, SumMoney, Comment)
- values( @CustomerID, getdate(), @SumMoney, @Comment);
-
- set @OrderID = scope_identity();
-
- end;
- create procedure [dbo].[InsertOrderDetail](
- @OrderID int,
- @ProductID int,
- @Quantity int
- )
- as
- declare @Price money;
- select @Price = (select UnitPrice from Products where ProductID = @ProductID);
-
- insert into [Order Details] (OrderID, ProductID, UnitPrice, Quantity)
- values (@OrderID, @ProductID, @Price, @Quantity);
-
-
- create procedure [dbo].[RefreshOrderSumMoney](
- @OrderID int
- )
- as
- declare @SumMoney money;
- select @SumMoney = (select sum(UnitPrice * Quantity) from [Order Details] where OrderID = @OrderID);
-
- update Orders set SumMoney = @SumMoney where OrderID = @OrderID;
-
|
