Binding a Windows DataGrid to Master-Detail Data

[ Team LiB ] Recipe 7.12 Binding a Windows DataGrid to Master-Detail Data Problem You need to bind both a parent table and child table within a DataSet to a DataGrid so that the child data is displayed when the parent is expanded, and update the database with edits made to data in both tables.
Nội dung trích xuất từ tài liệu:
Binding a Windows DataGrid to Master-Detail Data[ Team LiB ]Recipe 7.12 Binding a Windows DataGrid to Master-Detail DataProblemYou need to bind both a parent table and child table within a DataSet to a DataGrid sothat the child data is displayed when the parent is expanded, and update the database withedits made to data in both tables.SolutionUse the approach demonstrated in the sample code.The sample uses eight stored procedures, which are shown in Example 7-20 throughExample 7-27:GetOrders Used to retrieve a single record from the Orders table if the optional @OrderId parameter is specified or all Orders records if it is notDeleteOrders Used to delete the record specified by the @OrderId parameter from the Orders tableInsertOrders Used to insert a record into the Orders table and return the OrderID identity value for the new recordUpdateOrders Used to update all field values for the record in the Orders table specified by the @OrderId input parameterGetOrderDetails Used to retrieve a single record from the Order Details table if the optional @OrderId and @ProductID parameters are specified, or all Order Details records if it is notDeleteOrderDetails Used to delete the record specified by the @OrderId and @ProductID parameters from the Order Details tableInsertOrderDetails Used to insert a record into the Order Details tableUpdateOrderDetails Used to update all field values for the record in the Order Details table specified by the @OrderId and @ProductID input parametersExample 7-20. Stored procedure: GetOrdersCREATE PROCEDURE GetOrders @OrderID int=nullAS SET NOCOUNT ON if @OrderID is not null begin select OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry from Orders where OrderID=@OrderID return 0 end select OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry from Orders return 0Example 7-21. Stored procedure: DeleteOrdersCREATE PROCEDURE DeleteOrders @OrderID intAS SET NOCOUNT ON delete from Orders where OrderID=@OrderID return 0Example 7-22. Stored procedure: InsertOrdersCREATE PROCEDURE InsertOrders @OrderID int output, @CustomerID nchar(5), @EmployeeID int, @OrderDate datetime, @RequiredDate datetime, @ShippedDate datetime, @ShipVia int, @Freight money, @ShipName nvarchar(40), @ShipAddress nvarchar(60), @ShipCity nvarchar(15), @ShipRegion nvarchar(15), @ShipPostalCode nvarchar(10), @ShipCountry nvarchar(15)AS SET NOCOUNT ON insert Orders( CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry) values ( @CustomerID, @EmployeeID, @OrderDate, @RequiredDate, @ShippedDate, @ShipVia, @Freight, @ShipName, @ShipAddress, @ShipCity, @ShipRegion, @ShipPostalCode, @ShipCountry) if @@rowcount=0 return 1 set @OrderID=Scope_Identity( ) select @OrderId OrderId return 0Example 7-23. Stored procedure: UpdateOrdersCREATE PROCEDURE UpdateOrders @OrderID int, @CustomerID nchar(5), @EmployeeID int, @OrderDate datetime, @RequiredDate datetime, @ShippedDate datetime, @ShipVia int, @Freight money, @ShipName nvarchar(40), @ShipAddress nvarchar(60), @ShipCity nvarchar(15), @ShipRegion nvarchar(15), @ShipPostalCode nvarchar(10), @ShipCountry nvarchar(15)AS SET NOCOUNT ON update Orders set CustomerID=@CustomerID, EmployeeID=@EmployeeID, OrderDate=@OrderDate, RequiredDate=@RequiredDate, ShippedDate=@ShippedDate, ShipVia=@ShipVia, Freight=@Freight, ShipName=@ShipName, ShipAddress=@ShipAddress, ShipCity=@ShipCity, ShipRegion=@ShipRegion, ShipPostalCode=@ShipPostalCode, ShipCountry=@ShipCountry where OrderID=@OrderID if @@rowcount=0 return 1 return 0Example 7-24. Stored procedure: GetOrderDetailsCREATE PROCEDURE GetOrderDetails @OrderID int=null, @ProductID int=nullAS SET NOCOUNT ON if @OrderID is not null and @ProductID is not null begin select OrderID, ProductID, UnitPrice, Quantity, Discount from [Order Details] where OrderID=@OrderID and ProductID=@ProductID return 0 end select OrderID, ProductID, UnitPrice, Quantity, Discount from [Order Details] return 0Example 7-25. Stored procedure: DeleteOrderDetailsCREATE PROCEDURE DeleteOrderDetails @OrderID int, @ProductID intAS SET NOCOUNT ON delete from [Order Details] where OrderID=@OrderID and ProductID=@ProductID return 0Example 7-26. Stored procedure: InsertOrderDetailsCREATE PROCEDURE InsertOrderDetails @OrderID int, @ProductID int, @UnitPrice money, @Quantity smallint, @Discount realAS SET NOCOUNT ON insert [Order Details]( OrderID, ProductID, UnitPrice, Quantity, Discount) values ( @OrderID, @ProductID, @UnitPrice, @Quantity, @Discount) if @@rowcount=0 return 1 return 0Example 7-27. Stored procedure: UpdateOrderDetailsCREATE PROCEDURE UpdateOrderDetails @OrderID int, @ProductID int, @UnitPrice money, @Quantity smallint, @Discount realAS SET NOCOUNT ON update [Order Details ...