扫一扫
分享文章到微信
扫一扫
关注官方公众号
至顶头条
作者:陶刚编译 来源:yesky 2007年11月9日
关键字:
Const connString = "server=localhost;database=northwind;uid=sa;pwd=" Public connCustSvc As SqlClient.SqlConnection Public daCustomer As SqlClient.SqlDataAdapter Public cbCustomer As SqlClient.SqlCommandBuilder Public daOrders As SqlClient.SqlDataAdapter Public cbOrders As SqlClient.SqlCommandBuilder Public daOrderDetail As SqlClient.SqlDataAdapter Public cbOrderDetail As SqlClient.SqlCommandBuilder Public dsAllData As DataSet Public Sub Main() connCustSvc = New SqlClient.SqlConnection(connString) daCustomer = New SqlClient.SqlDataAdapter("SELECT * FROM Customer", connCustSvc) cbCustomer = New SqlClient.SqlCommandBuilder(daCustomer) daOrders = New SqlClient.SqlDataAdapter("SELECT * FROM Orders", connCustSvc) cbOrders = New SqlClient.SqlCommandBuilder(daOrders) daOrderDetail = New SqlClient.SqlDataAdapter("SELECT * FROM OrderDetail", connCustSvc) cbOrderDetail = New SqlClient.SqlCommandBuilder(daOrderDetail) dsAllData = New DataSet() daCustomer.MissingSchemaAction = MissingSchemaAction.AddWithKey daCustomer.Fill(dsAllData, "Customer") daOrders.MissingSchemaAction = MissingSchemaAction.AddWithKey daOrders.Fill(dsAllData, "Orders") dsAllData.Tables("Orders").Columns("Total").DefaultValue = 0 daOrderDetail.MissingSchemaAction = MissingSchemaAction.AddWithKey daOrderDetail.Fill(dsAllData, "OrderDetail") Application.Run(New frmCustomerMaintenance()) End Sub |
Private Sub CreateOrder() Dim dr As DataRow dr = dsAllData.Tables("Orders").NewRow With dr .Item("DateOrdered") = Now .Item("CustomerID") = 1 .Item("ShipToAddress") = "123 Main" .Item("ShipToCity") = "Kansas City" .Item("ShipToState") = "MO" .Item("ShipToZip") = "12345" End With dsAllData.Tables("Orders").Rows.Add(dr) AddOrderDetail(dr.Item("ID"), 1, 1, 9.99) AddOrderDetail(dr.Item("ID"), 2, 2, 4.99) daOrders.Update(dsAllData.Tables("Orders")) daOrderDetail.Update(dsAllData.Tables("OrderDetail")) End Sub Private Sub AddOrderDetail(ByVal OrderID As Integer, _ ByVal ProductID As Integer, ByVal Quantity As Integer, _ ByVal Price As Single) Dim dr As DataRow dr = dsAllData.Tables("OrderDetail").NewRow With dr .Item("OrderID") = OrderID .Item("ProductID") = ProductID .Item("Quantity") = Quantity .Item("Price") = Price End With dsAllData.Tables("OrderDetail").Rows.Add(dr) End Sub |
CREATE TRIGGER trg_UpdateOrderTotal ON [dbo].[OrderDetail] FOR INSERT, UPDATE, DELETE AS DECLARE @OrderID int SELECT @OrderID=OrderID FROM Inserted IF @OrderID IS NULL SELECT @OrderID=OrderID FROM Deleted UPDATE Orders SET Total= ( SELECT Sum(Price*Quantity) FROM OrderDetail WHERE OrderID=@OrderID ) WHERE ID=@OrderID |
如果您非常迫切的想了解IT领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。