salesorder.vb

来自「wrox出版社的另一套经典的VB2005数据库编程学习书籍,收集了书中源码,郑重」· VB 代码 · 共 420 行

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services
Imports System.Web.Services.Protocols

Partial Public Class WSOrdersBE
	'Version 1.2: Adds equivalents of GetOrdersAndDetails Web methods of services that return DataSets
	'This class contains the SalesOrder class definition
	Private Const strGetAllSalesOrders As String = "Version 1.2: Retrieves all SalesOrder objects in descending OrderID sequence."
	Private Const strGetTopSalesOrders As String = "Version 1.2: Retrieves the number of SalesOrder objects specified by the Number parameter in descending OrderID sequence (includes OrderDetail objects)."
	Private Const strGetSalesOrdersByCustomerID As String = "Version 1.2: Retrieves the SalesOrder objects specified by the CustomerID parameter in descending OrderID sequence (includes OrderDetail objects)."
	Private Const strGetSalesOrderByOrderID As String = "Version 1.2: Retrieves the SalesOrder object specified by the OrderID parameter (includes OrderDetail objects)."
	Private Const strInsertSalesOrder As String = "Version 1.2: Inserts a SalesOrder Object and and adds a default OrderDetail object to the OrderDetails field. Accepts an SalesOrder object as the parameter."
	Private Const strUpdateSalesOrder As String = "Version 1.2: Updates a SalesOrder Object and its OrderDetails field. Accepts an SalesOrder object as the parameter, and deletes all existing OrderDetail objects before inserting."

	<WebMethod(Description:=strGetSalesOrdersByCustomerID)> _
	 Public Function GetSalesOrdersByCustomerID(ByVal CustomerID As String) As SalesOrder()
		Dim strSQL As String = "SELECT OrderID FROM Orders WHERE CustomerID = '" + CustomerID + "' ORDER BY OrderID DESC"
		Return GetSalesOrdersArray(strSQL)
	End Function

	<WebMethod(Description:=strGetTopSalesOrders)> _
	 Public Function GetTopSalesOrders(ByVal Number As Integer) As SalesOrder()
		Dim strSQL As String = "SELECT TOP " + Number.ToString + " OrderID FROM Orders ORDER BY OrderID DESC"
		Return GetSalesOrdersArray(strSQL)
	End Function

	<WebMethod(Description:=strGetAllSalesOrders)> _
	 Public Function GetAllSalesOrders() As SalesOrder()
		Dim strSQL As String = "SELECT OrderID FROM Orders ORDER BY OrderID DESC"
		Return GetSalesOrdersArray(strSQL)
	End Function

	Private Function GetSalesOrdersArray(ByVal strSQL As String) As SalesOrder()
		'This process is inefficient because code executes GetSalesOrder for each order
        Dim strConn As String = ConfigurationManager.ConnectionStrings("NorthwindConnection").ConnectionString
		Dim cnNwind As New SqlConnection(strConn)
		Dim cmOrders As New SqlCommand(strSQL, cnNwind)
		Dim drOrders As SqlDataReader = Nothing
		Try
			cnNwind.Open()
			drOrders = cmOrders.ExecuteReader()
			Dim SalesOrders As New ArrayList
			With drOrders
				If .HasRows Then
					Dim OrderIDs As New ArrayList
					While .Read
						OrderIDs.Add(.GetInt32(0))
					End While
					.Close()
					cnNwind.Close()
					Dim intItem As Integer
					For intItem = 0 To OrderIDs.Count - 1
						Dim objOrder As New SalesOrder
						If intItem = OrderIDs.Count - 1 Then
							'Close the connection
							objOrder = GetSalesOrder(CType(OrderIDs.Item(intItem), Integer), False, True)
						Else
							If intItem = 0 Then
								'Open but don't close the connection
								objOrder = GetSalesOrder(CType(OrderIDs.Item(intItem), Integer), True, False)
							Else
								'Don't open or close the connection
								objOrder = GetSalesOrder(CType(OrderIDs.Item(intItem), Integer), False, False)
							End If
						End If
						SalesOrders.Add(objOrder)
					Next
					cnNwind.Close()
					Return CType(SalesOrders.ToArray(GetType(SalesOrder)), SalesOrder())
				Else
					Dim strMsg As String = "Orders for parameter supplied not found."
					Dim excSoap As New SoapException(strMsg, SoapException.ClientFaultCode, _
					 Context.Request.Url.AbsoluteUri)
					Throw excSoap
				End If
			End With
		Catch excSql As SqlException
			Dim excSoap As New SoapException("SQLException: " + excSql.Message, SoapException.ClientFaultCode, _
			 Context.Request.Url.AbsoluteUri)
			Throw excSoap
		Catch excSys As System.Exception
			Dim excSoap As New SoapException("SystemException: " + excSys.Message, SoapException.ClientFaultCode, _
			 Context.Request.Url.AbsoluteUri)
			Throw excSoap
		Finally
			drOrders.Close()
			cnNwind.Close()
			drOrders.Dispose()
			cmOrders.Dispose()
			cnNwind.Dispose()
		End Try
	End Function

	<WebMethod(Description:=strGetSalesOrderByOrderID)> _
	Public Function GetSalesOrderByOrderID(ByVal OrderID As Integer) As SalesOrder
		'Open and close the connection
		Return GetSalesOrder(OrderID, True, True)
	End Function

	Private Function GetSalesOrder(ByVal intOrderID As Integer, ByVal blnOpenConnection As Boolean, ByVal blnCloseConnection As Boolean) As SalesOrder
		'Populates an SalesOrder object with a single order
		Dim strSQL As String = "SELECT * FROM Orders WHERE OrderID = @OrderID " + _
		 "; SELECT * FROM [Order Details] WHERE OrderID = @OrderID"
        Dim strConn As String = ConfigurationManager.ConnectionStrings("NorthwindConnection").ConnectionString
		Static cnNwind As SqlConnection
		If blnOpenConnection Then
			cnNwind = New SqlConnection(strConn)
		End If
		Dim cmOrder As New SqlCommand(strSQL, cnNwind)

		'Parameterize SELECT query
		Dim prmSelect As SqlParameter
		prmSelect = New SqlParameter("@OrderID", SqlDbType.Int)
		prmSelect.Value = intOrderID
		cmOrder.Parameters.Add(prmSelect)

		Dim drOrder As SqlDataReader = Nothing
		Try
			If blnOpenConnection Then
				cnNwind.Open()
			End If
			drOrder = cmOrder.ExecuteReader()
			With drOrder
				If .HasRows Then
					Dim objOrder As New SalesOrder
					.Read()
					objOrder.OrderID = .GetInt32(0)
					objOrder.CustomerID = .GetString(1)
					objOrder.EmployeeID = .GetInt32(2)
					objOrder.OrderDate = .GetDateTime(3)
					If Not IsDBNull(.Item(4)) Then
						objOrder.RequiredDate = .GetDateTime(4)
					Else
						objOrder.RequiredDate = #12:00:00 AM#
					End If
					If Not IsDBNull(.Item(5)) Then
						objOrder.ShippedDate = .GetDateTime(5)
					Else
						objOrder.ShippedDate = #12:00:00 AM#
					End If
					objOrder.ShipVia = .GetInt32(6)
					If Not IsDBNull(.Item(7)) Then
						objOrder.Freight = .GetDecimal(7)
					Else
						objOrder.Freight = 0D
					End If
					objOrder.ShipName = .GetString(8)
					objOrder.ShipAddress = .GetString(9)
					objOrder.ShipCity = .GetString(10)
					If Not IsDBNull(.Item(11)) Then
						objOrder.ShipRegion = .GetString(11)
					End If
					If Not IsDBNull(.Item(12)) Then
						objOrder.ShipPostalCode = .GetString(12)
					End If
					objOrder.ShipCountry = .GetString(13)

					'Add the OrderDetails array of OrderDetail objects
					Dim Details As New ArrayList
					Dim objDetail As OrderDetail = Nothing
					If .NextResult Then
						While .Read
							objDetail = New OrderDetail
							objDetail.OrderID = .GetInt32(0)
							objDetail.ProductID = .GetInt32(1)
							objDetail.UnitPrice = .GetDecimal(2)
							objDetail.Quantity = .GetInt16(3)
							objDetail.Discount = CType(.Item(4), Decimal)
							Details.Add(objDetail)
						End While
						.Close()
						If blnCloseConnection Then
							cnNwind.Close()
						End If
                    Else
                        .Close()
                        'Business rule: All orders must have at least one order detail
                        Dim strMsg As String = "OrderDetails for " + intOrderID.ToString + " not found."
                        Dim excSoap As New SoapException(strMsg, SoapException.ClientFaultCode, _
                         Context.Request.Url.AbsoluteUri)
                        Throw excSoap
					End If
					'Add the array of OrderDetails
					objOrder.OrderDetails = CType(Details.ToArray(GetType(OrderDetail)), OrderDetail())
					Return objOrder
				Else
					Dim strMsg As String = "Order " + intOrderID.ToString + " not found."
					Dim excSoap As New SoapException(strMsg, SoapException.ClientFaultCode, _
					 Context.Request.Url.AbsoluteUri)
					Throw excSoap
				End If
			End With
		Catch excSql As SqlException
			Dim excSoap As New SoapException("SQLException: " + excSql.Message, SoapException.ClientFaultCode, _
			 Context.Request.Url.AbsoluteUri)
			Throw excSoap
		Catch excSys As System.Exception
			Dim excSoap As New SoapException("SystemException: " + excSys.Message, SoapException.ClientFaultCode, _
			 Context.Request.Url.AbsoluteUri)
			Throw excSoap
		Finally
			If blnCloseConnection Then
				cnNwind.Close()
				cnNwind.Dispose()
			End If
			drOrder.Close()
			drOrder.Dispose()
			cmOrder.Dispose()
		End Try
	End Function

	'***************************************
	'SalesOrder Insert and Update operations
	'***************************************

	<WebMethod(Description:=strInsertSalesOrder)> _
	Public Function InsertSalesOrder(ByVal objSalesOrder As SalesOrder) As Integer
		Return InsertOrUpdateSalesOrder(objSalesOrder, True)
	End Function

	<WebMethod(Description:=strUpdateSalesOrder)> _
	 Public Function UpdateSalesOrder(ByVal objSalesOrder As SalesOrder) As Integer
		Return InsertOrUpdateSalesOrder(objSalesOrder, False)
	End Function

	Private Function InsertOrUpdateSalesOrder(ByVal objSalesOrder As SalesOrder, ByVal blnInsert As Boolean) As Integer
		'Create an Order object (without the Details field)
		Dim objOrder As New Order
		With objSalesOrder
			objOrder.OrderID = .OrderID
			objOrder.CustomerID = .CustomerID
			objOrder.EmployeeID = .EmployeeID
			objOrder.OrderDate = .OrderDate
			objOrder.RequiredDate = .RequiredDate
			objOrder.ShippedDate = .ShippedDate
			objOrder.ShipVia = .ShipVia
			objOrder.Freight = .Freight
			objOrder.ShipName = .ShipName
			objOrder.ShipAddress = .ShipAddress
			objOrder.ShipCity = .ShipCity
			objOrder.ShipRegion = .ShipRegion
			objOrder.ShipPostalCode = .ShipPostalCode
			objOrder.ShipCountry = .ShipCountry
		End With
		If blnInsert Then
			Dim intInserts As Integer
			intInserts = InsertOrderObject(objOrder)
			Return intInserts
		Else
			Dim intUpdates As Integer
			intUpdates = UpdateOrderObject(objOrder)
			'Update the details field in a separate operation
			Dim objDetails As OrderDetail() = objSalesOrder.OrderDetails
			intUpdates += InsertDetailObjects(objDetails)
			Return intUpdates
		End If
	End Function

	'****************
	'SalesOrder Class
	'****************

	Public Class SalesOrder
		'Includes OrderDetails for SalesOrder
		Private orderIDField As Integer
		Private customerIDField As String
		Private employeeIDField As Integer
		Private orderDateField As Date
		Private requiredDateField As Date
		Private shippedDateField As Date
        'Private shippedDateField As Nullable(Of Date) 'Not interoperable
		Private shipViaField As Integer
		Private freightField As Decimal
		Private shipNameField As String
		Private shipAddressField As String
		Private shipCityField As String
		Private shipRegionField As String
		Private shipPostalCodeField As String
		Private shipCountryField As String
		Private orderDetailsField As OrderDetail()

		Public Property OrderID() As Integer
			Get
				Return Me.orderIDField
			End Get
			Set(ByVal value As Integer)
				Me.orderIDField = value
			End Set
		End Property

		Public Property CustomerID() As String
			Get
				Return Me.customerIDField
			End Get
			Set(ByVal value As String)
				Me.customerIDField = value
			End Set
		End Property

		Public Property EmployeeID() As Integer
			Get
				Return Me.employeeIDField
			End Get
			Set(ByVal value As Integer)
				Me.employeeIDField = value
			End Set
		End Property

		Public Property OrderDate() As Date
			Get
				Return Me.orderDateField
			End Get
			Set(ByVal value As Date)
				Me.orderDateField = value
			End Set
		End Property

		Public Property RequiredDate() As Date
			Get
				Return Me.requiredDateField
			End Get
			Set(ByVal value As Date)
				Me.requiredDateField = value
			End Set
		End Property

		Public Property ShippedDate() As Date
			Get
				Return Me.shippedDateField
			End Get
			Set(ByVal value As Date)
				Me.shippedDateField = value
			End Set
		End Property

		Public Property ShipVia() As Integer
			Get
				Return Me.shipViaField
			End Get
			Set(ByVal value As Integer)
				Me.shipViaField = value
			End Set
		End Property

		Public Property Freight() As Decimal
			Get
				Return Me.freightField
			End Get
			Set(ByVal value As Decimal)
				Me.freightField = value
			End Set
		End Property

		Public Property ShipName() As String
			Get
				Return Me.shipNameField
			End Get
			Set(ByVal value As String)
				Me.shipNameField = value
			End Set
		End Property

		Public Property ShipAddress() As String
			Get
				Return Me.shipAddressField
			End Get
			Set(ByVal value As String)
				Me.shipAddressField = value
			End Set
		End Property

		Public Property ShipCity() As String
			Get
				Return Me.shipCityField
			End Get
			Set(ByVal value As String)
				Me.shipCityField = value
			End Set
		End Property

		Public Property ShipRegion() As String
			Get
				Return Me.shipRegionField
			End Get
			Set(ByVal value As String)
				Me.shipRegionField = value
			End Set
		End Property

		Public Property ShipPostalCode() As String
			Get
				Return Me.shipPostalCodeField
			End Get
			Set(ByVal value As String)
				Me.shipPostalCodeField = value
			End Set
		End Property

		Public Property ShipCountry() As String
			Get
				Return Me.shipCountryField
			End Get
			Set(ByVal value As String)
				Me.shipCountryField = value
			End Set
		End Property

		Public Property OrderDetails() As OrderDetail()
			Get
				Return Me.orderDetailsField
			End Get
			Set(ByVal value As OrderDetail())
				Me.orderDetailsField = value
			End Set
		End Property
	End Class
End Class