ordersbemethods.vb

wrox出版社的另一套经典的VB2005数据库编程学习书籍,收集了书中源码,郑重推荐,电子书,电子书下载

						End If
						.Add(prmInsert)
						prmInsert = New SqlParameter("@ShipPostalCode", SqlDbType.NVarChar, 10)
						If ordInsert.ShipPostalCode Is Nothing Then
							prmInsert.Value = Convert.DBNull
						Else
							prmInsert.Value = ordInsert.ShipPostalCode
						End If
						.Add(prmInsert)
						prmInsert = New SqlParameter("@ShipCountry", SqlDbType.NVarChar, 10)
						prmInsert.Value = ordInsert.ShipCountry
						.Add(prmInsert)
					End With
					strSQL = "INSERT INTO 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)"
					cmOrder.Connection = cnNwind
					cmOrder.CommandText = strSQL
					cnNwind.Open()
					intOrders = cmOrder.ExecuteNonQuery
					'Get the return value
					strSQL = "SELECT IDENT_CURRENT('Orders')"
					cmOrder.CommandText = strSQL
					intOrderID = CType(cmOrder.ExecuteScalar, Integer)
					.OrderID = intOrderID
				Else
					strSQL = "INSERT INTO Orders(CustomerID, EmployeeID, OrderDate, RequiredDate, " + _
					 "ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, " + _
					 "ShipPostalCode, ShipCountry) VALUES('" + .CustomerID + "', " + .EmployeeID.ToString + ", '" + _
					 .OrderDate.ToShortDateString + "', '" + .RequiredDate.ToShortDateString + "', "
					If .ShippedDate.HasValue Then
						strSQL += "'" + .ShippedDate.ToString + "', "
					Else
						strSQL += "NULL, "
					End If
					strSQL += .ShipVia.ToString + ", " + .Freight.ToString + ", '" + .ShipName + "', '" + _
					.ShipAddress + "', '" + .ShipCity + "', "
					'Workaround for template issue
					If .ShipRegion Is Nothing OrElse .ShipRegion = "" Then
						strSQL += "NULL, "
					Else
						strSQL += "'" + .ShipRegion + "', "
					End If
					If .ShipPostalCode Is Nothing OrElse .ShipPostalCode = "" Then
						strSQL += "NULL, '"
					Else
						strSQL += "'" + .ShipPostalCode + "', '"
					End If
					strSQL += .ShipCountry + "')"
					cmOrder = New SqlCommand(strSQL, cnNwind)
					cnNwind.Open()
					intOrders = cmOrder.ExecuteNonQuery()
					'Not the most efficient method
					strSQL = "SELECT TOP 1 OrderID FROM Orders ORDER BY OrderID DESC"
					cmOrder.CommandText = strSQL
					intOrderID = CType(cmOrder.ExecuteScalar, Integer)
					.OrderID = intOrderID
				End If
				cnNwind.Close()
				'Add default Order Details record
				Dim intDetails As Integer = InsertDetail(intOrderID, 1, 18D, 1, 0D)
				Return intOrders + intDetails
			End With
		Catch exc As Exception
			'Client handles the exception
			Throw exc
		Finally
			cnNwind.Close()
			cmOrder.Dispose()
			cnNwind.Dispose()
		End Try
	End Function

	Public Function DeleteOrder(ByVal original_OrderID As Integer) As Integer
		'There is no need to instantiate an object when deleting, unless you implement
		'business rules for deletions
		Dim strSQL As String = Nothing
		Dim cnNwind As New SqlConnection(strConn)
		Dim cmOrder As SqlCommand = Nothing
		Try
			'Delete Order Details and Orders records
			strSQL = "DELETE FROM [Order Details] WHERE OrderID = " + original_OrderID.ToString + _
			"; DELETE FROM Orders WHERE OrderID = " + original_OrderID.ToString
			cmOrder = New SqlCommand(strSQL, cnNwind)
			cnNwind.Open()
			Return cmOrder.ExecuteNonQuery()
		Catch exc As Exception
			'Client handles the exception
			Throw exc
		Finally
			cnNwind.Close()
			cmOrder.Dispose()
			cnNwind.Dispose()
		End Try
	End Function

	'****************************
	'Order Details SELECT methods
	'****************************

	Public Function GetAllDetails() As ArrayList
		Dim strSQL As String = "SELECT * FROM [Order Details] ORDER BY OrderID DESC"
		Return GetDetails(strSQL)
	End Function

	Public Function GetDetailsByCustomerID(ByVal CustomerID As String) As ArrayList
		Dim strSQL As String = "SELECT * FROM [Order Details] WHERE OrderID IN " + _
		 "(SELECT OrderID FROM Orders WHERE CustomerID = '" + CustomerID + "') " + _
		 "ORDER BY OrderID DESC"
		Return GetDetails(strSQL)
	End Function

	Public Function GetTopDetails(ByVal Number As Integer) As ArrayList
		Dim strSQL As String = "SELECT * FROM [Order Details] WHERE OrderID IN " + _
		 "(SELECT TOP " + Number.ToString + " OrderID FROM Orders ORDER BY OrderID DESC)"
		Return GetDetails(strSQL)
	End Function

	Public Function GetDetailsByOrderID(ByVal OrderID As Integer) As ArrayList
		Dim strSQL As String = "SELECT * FROM [Order Details] WHERE OrderID = " + OrderID.ToString
		Return GetDetails(strSQL)
	End Function

	Private Function GetDetails(ByVal strSQL As String) As ArrayList
		'Populates a DetailsBE object with OrderDetails records
		Dim cnNwind As New SqlConnection(strConn)
		Dim cmDetails As New SqlCommand(strSQL, cnNwind)
		Dim rdDetails As SqlDataReader = Nothing
		Try
			cnNwind.Open()
			rdDetails = cmDetails.ExecuteReader()
			With rdDetails
				If .HasRows Then
					Dim DetailsBE As New OrderDetails
					Dim aDetail(50) As OrderDetail
					Dim intRow As Integer
					While .Read()
						aDetail(intRow) = New OrderDetail
						aDetail(intRow).OrderID = .GetInt32(0)
						aDetail(intRow).ProductID = .GetInt32(1)
						aDetail(intRow).UnitPrice = .GetDecimal(2)
						aDetail(intRow).Quantity = .GetInt16(3)
						'Handles Single (real) or Decimal column type
						aDetail(intRow).Discount = CType(.Item(4), Decimal)
						intRow += 1
					End While
					.Close()
					ReDim Preserve aDetail(intRow - 1)
					DetailsBE.OrderDetails = aDetail
					Dim OrderDetails As New ArrayList
					For intRow = 0 To DetailsBE.OrderDetails.Length - 1
						OrderDetails.Add(DetailsBE.OrderDetails(intRow))
					Next
					Return OrderDetails
				Else
					Throw New Exception("Order Details not found for parameter supplied.")
				End If
			End With
		Catch excSql As SqlException
			Throw excSql
		Catch excSys As System.Exception
			Throw excSys
		Finally
			rdDetails.Close()
			cnNwind.Close()
			rdDetails.Dispose()
			cmDetails.Dispose()
			cnNwind.Dispose()
		End Try
	End Function

	'************************************************
	'Order Details UPDATE, INSERT, and DELETE methods
	'************************************************

	Public Function UpdateDetail( _
	 ByVal OrderID As Integer, ByVal ProductID As Integer, _
	 ByVal UnitPrice As Decimal, ByVal Quantity As Short, _
	 ByVal Discount As Decimal, ByVal original_OrderID As Integer, _
	 ByVal original_ProductID As Integer) As Integer
		'Throw an immediate exception if users attempt to update
		'an OrderDetail object with OrderID <> original_OrderID
		If OrderID <> original_OrderID Then
			Dim strMsg As String = "Updating an Order Detail record for a " + _
			"different Order isn't permitted."
			Dim excOrderID As New Exception(strMsg)
			Throw excOrderID
		End If
		Dim dtlUpdate As New OrderDetail
		Dim strSQL As String = Nothing
		Dim cnNwind As New SqlConnection(strConn)
		Dim cmOrder As New SqlCommand
		Try
			With dtlUpdate
				.OrderID = OrderID
				.ProductID = ProductID
				.UnitPrice = UnitPrice
				.Quantity = Quantity
				.Discount = Discount
				Dim blnUseParams As Boolean = True
				If blnUseParams Then
					With cmOrder.Parameters
						Dim prmUpdate As SqlParameter
						prmUpdate = New SqlParameter("@OrderID", SqlDbType.Int)
						prmUpdate.Value = dtlUpdate.OrderID
						.Add(prmUpdate)
						prmUpdate = New SqlParameter("@ProductID", SqlDbType.Int)
						prmUpdate.Value = dtlUpdate.ProductID
						.Add(prmUpdate)
						prmUpdate = New SqlParameter("@UnitPrice", SqlDbType.Money)
						prmUpdate.Value = dtlUpdate.UnitPrice
						.Add(prmUpdate)
						prmUpdate = New SqlParameter("@Quantity", SqlDbType.SmallInt)
						prmUpdate.Value = dtlUpdate.Quantity
						.Add(prmUpdate)
						prmUpdate = New SqlParameter("@Discount", SqlDbType.Real)
						prmUpdate.Value = dtlUpdate.Discount
						.Add(prmUpdate)
						prmUpdate = New SqlParameter("@original_OrderID", SqlDbType.Int)
						prmUpdate.Value = original_OrderID
						.Add(prmUpdate)
						prmUpdate = New SqlParameter("@original_ProductID", SqlDbType.Int)
						prmUpdate.Value = original_ProductID
						.Add(prmUpdate)
					End With
					strSQL = "UPDATE [Order Details] SET OrderID = @OrderID, " + _
					 "ProductID = @ProductID, UnitPrice = @UnitPrice, " + _
					 "Quantity = @Quantity, Discount = @Discount WHERE " + _
					 "OrderID = @original_OrderID AND ProductID = @original_ProductID"
					cmOrder.Connection = cnNwind
					cmOrder.CommandText = strSQL
					cnNwind.Open()
					Return cmOrder.ExecuteNonQuery
				End If
			End With
		Catch exc As Exception
			'Client handles the exception
			Throw exc
		Finally
			cnNwind.Close()
			cmOrder.Dispose()
			cnNwind.Dispose()
		End Try
	End Function

	Public Function InsertDetail( _
	 ByVal OrderID As Integer, ByVal ProductID As Integer, _
	 ByVal UnitPrice As Decimal, ByVal Quantity As Short, _
	 ByVal Discount As Decimal) As Integer
		Dim dtlInsert As New OrderDetail
		Dim strSQL As String = Nothing
		Dim cnNwind As New SqlConnection(strConn)
		Dim cmOrder As New SqlCommand
		Try
			With dtlInsert
				.OrderID = OrderID
				.ProductID = ProductID
				.UnitPrice = UnitPrice
				.Quantity = Quantity
				.Discount = Discount
				Dim blnUseParams As Boolean = True
				If blnUseParams Then
					With cmOrder.Parameters
						Dim prmInsert As SqlParameter
						prmInsert = New SqlParameter("@OrderID", SqlDbType.Int)
						prmInsert.Value = dtlInsert.OrderID
						.Add(prmInsert)
						prmInsert = New SqlParameter("@ProductID", SqlDbType.Int)
						prmInsert.Value = dtlInsert.ProductID
						.Add(prmInsert)
						prmInsert = New SqlParameter("@UnitPrice", SqlDbType.Money)
						prmInsert.Value = dtlInsert.UnitPrice
						.Add(prmInsert)
						prmInsert = New SqlParameter("@Quantity", SqlDbType.SmallInt)
						prmInsert.Value = dtlInsert.ProductID
						.Add(prmInsert)
						prmInsert = New SqlParameter("@Discount", SqlDbType.Real)
						prmInsert.Value = dtlInsert.Discount
						.Add(prmInsert)
					End With
					strSQL = "INSERT INTO [Order Details](OrderID, ProductID, UnitPrice, " + _
					"Quantity, Discount) VALUES(@OrderID, @ProductID, @UnitPrice, " + _
					"@Quantity, @Discount)"
					cmOrder.Connection = cnNwind
					cmOrder.CommandText = strSQL
					cnNwind.Open()
					Return cmOrder.ExecuteNonQuery
				End If
			End With
		Catch exc As Exception
			'Client handles the exception
			Throw exc
		Finally
			cnNwind.Close()
			cmOrder.Dispose()
			cnNwind.Dispose()
		End Try
	End Function

	Public Function DeleteDetail(ByVal original_OrderID As Integer, _
	 ByVal original_ProductID As Integer) As Integer
		'No need to instantiate an object when deleting, unless you implement
		'business rules for deletion
		Dim strSQL As String = Nothing
		Dim cnNwind As New SqlConnection(strConn)
		Dim cmOrder As SqlCommand = Nothing
		Try
			'Delete Order Details and Orders records
			strSQL = "DELETE FROM [Order Details] WHERE OrderID = " + original_OrderID.ToString + _
			" AND ProductID = " + original_ProductID.ToString
			cmOrder = New SqlCommand(strSQL, cnNwind)
			cnNwind.Open()
			Return cmOrder.ExecuteNonQuery()
		Catch exc As Exception
			'Client handles the exception
			Throw exc
		Finally
			cnNwind.Close()
			cmOrder.Dispose()
			cnNwind.Dispose()
		End Try
	End Function
End Class