wsordersbe.vb

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

Option Explicit On
Option Strict On
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Data.SqlClient


<WebService(Description:="Returns, updates, inserts, and deletes Order and OrderDetail business entity objects. Version 1.0 requires field parameters for updates; version 1.1 requires object parameters. Version 1.2 adds hierarchical SalesOrder objects.", _
  Namespace:="www.oakleaf.ws/webservices/northwind/orders/ordersbe")> _
Partial Public Class WSOrdersBE
	Inherits System.Web.Services.WebService

	'WebMethod descriptions
	Private Const strGetAllOrders As String = "Version 1.0: Retrieves all Order objects in descending OrderID sequence."
	Private Const strGetTopOrders As String = "Version 1.0: Retrieves the number of Order objects specified by the Number parameter in descending OrderID sequence."
	Private Const strGetOrdersByCustomerID As String = "Version 1.0: Retrieves the Order objects specified by the CustomerID parameter in descending OrderID sequence."
	Private Const strGetOrderByOrderID As String = "Version 1.0: Retrieves the Orders object specified by the OrderID parameter (10248 to 11077)."
	Private Const strGetAllDetails As String = "Version 1.0: Retrieves all OrderDetail objects in descending OrderID sequence."
	Private Const strGetTopDetails As String = "Version 1.0: Retrieves the OrderDetail objects for the number of Orders objects specified by the Number parameter in descending OrderID sequence."
	Private Const strGetDetailsByCustomerID As String = "Version 1.0: Retrieves the OrderDetail objects specified by the CustomerID parameter in descending OrderID sequence."
	Private Const strGetDetailsByOrderID As String = "Version 1.0: Retrieves the OrderDetail objects specified by the OrderID parameter (10248 to 11077)."
	'Methods that support ASP.NET UpdateMethod, InsertMethod, and DeleteMethod syntax
	Private Const strUpdateOrder As String = "Version 1.0: Updates an Order object and the Orders record; returns 1 if successful. (Use 1/1/0001 for NULL date.)"
	Private Const strInsertOrder As String = "Version 1.0: Creates new Order and OrderDetail objects and inserts new Orders and Order Details records; returns new OrderID if successful. (Use 1/1/0001 for NULL date.)"
	Private Const strDeleteOrder As String = "Version 1.0: Deletes an Orders and dependent Order Details records from the database and returns the number of deleted records."
	Private Const strUpdateDetail As String = "Version 1.0: Updates an OrderDetail object and the Order Details record; returns 1 if successful."
	Private Const strDeleteDetail As String = "Version 1.0: Deletes an Order Details record specified by OrderID and ProductID; returns 1 if successful."
	Private Const strInsertDetail As String = "Version 1.0: Creates a new OrderDetail object and inserts a new Order Details record; returns 1 if successful."

	'***************************
	'Version 1.0
	'Orders table SELECT methods
	'***************************

	<WebMethod(Description:=strGetAllOrders)> _
	Public Function GetAllOrders() As Order()
		Return GetOrders("SELECT * FROM Orders ORDER BY OrderID DESC")
	End Function

	<WebMethod(Description:=strGetTopOrders)> _
	Public Function GetTopOrders(ByVal Number As Integer) As Order()
		Return GetOrders("SELECT TOP " + Number.ToString + " * FROM Orders ORDER BY OrderID DESC")
	End Function

	<WebMethod(Description:=strGetOrdersByCustomerID)> _
	Public Function GetOrdersByCustomerID(ByVal CustomerID As String) As Order()
		Return GetOrders("SELECT * FROM Orders WHERE CustomerID = '" + CustomerID + "' ORDER BY OrderID DESC")
	End Function

	<WebMethod(Description:=strGetOrderByOrderID)> _
	Public Function GetOrderByOrderID(ByVal OrderID As Integer) As Order()
		'Replaced by function below to test effect of adding a field list on 
		'random GridView column and DetailsView field sequences (no effect)
		Return GetOrders("SELECT * FROM Orders WHERE OrderID = " + OrderID.ToString)
	End Function

	Private Function GetOrders(ByVal strSQL As String) As Order()
		'Populates an OrderBE object with a single order
        Dim strConn As String = ConfigurationManager.ConnectionStrings("NorthwindConnection").ConnectionString
		Dim cnNwind As New SqlConnection(strConn)
		Dim cmOrder As New SqlCommand(strSQL, cnNwind)
		Dim drOrder As SqlDataReader = Nothing
		Try
			cnNwind.Open()
			drOrder = cmOrder.ExecuteReader()
			With drOrder
				If .HasRows Then
					Dim Orders As New ArrayList
					While .Read
						Dim objOrder As New Order
						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)
						Orders.Add(objOrder)
					End While
					.Close()
					'Convert the ArrayList to a typed array
					Return CType(Orders.ToArray(GetType(Order)), Order())
				Else
					Dim strMsg As String = "Order(s) not found for parameter supplied."
					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
			drOrder.Close()
			cnNwind.Close()
			drOrder.Dispose()
			cmOrder.Dispose()
			cnNwind.Dispose()
		End Try
	End Function

	'****************************
	'Version 1.0
	'Order Details SELECT methods
	'****************************

	<WebMethod(Description:=strGetAllDetails)> _
	Public Function GetAllDetails() As OrderDetail()
		Dim strSQL As String = "SELECT * FROM [Order Details] ORDER BY OrderID DESC"
		Return GetDetails(strSQL)
	End Function

	<WebMethod(Description:=strGetDetailsByCustomerID)> _
	Public Function GetDetailsByCustomerID(ByVal CustomerID As String) As OrderDetail()
		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

	<WebMethod(Description:=strGetTopDetails)> _
	Public Function GetTopDetails(ByVal Number As Integer) As OrderDetail()
		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

	<WebMethod(Description:=strGetDetailsByOrderID)> _
	Public Function GetDetailsByOrderID(ByVal OrderID As Integer) As OrderDetail()
		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 OrderDetail()
		'Populates a OrderDetails object with OrderDetails records
        Dim strConn As String = ConfigurationManager.ConnectionStrings("NorthwindConnection").ConnectionString
		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 OrderDetails As New ArrayList
					While .Read()
						Dim BEDetail As New OrderDetail
						BEDetail.OrderID = .GetInt32(0)
						BEDetail.ProductID = .GetInt32(1)
						BEDetail.UnitPrice = .GetDecimal(2)
						BEDetail.Quantity = .GetInt16(3)
						'Handles Single (real) or Decimal column type
						BEDetail.Discount = CType(.Item(4), Decimal)
						OrderDetails.Add(BEDetail)
					End While
					.Close()
					Return CType(OrderDetails.ToArray(GetType(OrderDetail)), OrderDetail())
				Else
					Dim strMsg As String = "Order(s) not found for parameter supplied."
					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
			rdDetails.Close()
			cnNwind.Close()
			rdDetails.Dispose()
			cmDetails.Dispose()
			cnNwind.Dispose()
		End Try
	End Function

	'***********************************************
	'Orders table Update, Insert, and Delete methods
	'***********************************************

	<WebMethod(Description:=strUpdateOrder)> _
  Public Function UpdateOrder( _
  ByVal CustomerID As String, ByVal EmployeeID As Integer, _
  ByVal OrderDate As Date, ByVal RequiredDate As Date, _
  ByVal ShippedDate As Date, ByVal ShipVia As Integer, _
  ByVal Freight As Decimal, ByVal ShipName As String, _
  ByVal ShipAddress As String, ByVal ShipCity As String, _
  ByVal ShipRegion As String, ByVal ShipPostalCode As String, _
  ByVal ShipCountry As String, ByVal original_OrderID As Integer) As Integer
		'Create an Order object to pass to the Execute
		Dim objOrder As New Order
		With objOrder
			.OrderID = original_OrderID
			.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
		End With
		Return ExecuteUpdateOrder(objOrder)
	End Function

	Private Function ExecuteUpdateOrder(ByVal objOrder As Order) As Integer
		'Use prepared statements with parameters (can be easily altered to use
		'stored procedures)
		Dim cmOrder As New SqlCommand
		With cmOrder.Parameters
			Dim prmUpdate As SqlParameter
			prmUpdate = New SqlParameter("@CustomerID", SqlDbType.NChar, 5)
			prmUpdate.Value = objOrder.CustomerID
			.Add(prmUpdate)
			prmUpdate = New SqlParameter("@EmployeeID", SqlDbType.Int)
			prmUpdate.Value = objOrder.EmployeeID
			.Add(prmUpdate)
			prmUpdate = New SqlParameter("@OrderDate", SqlDbType.DateTime)
			prmUpdate.Value = objOrder.OrderDate
			.Add(prmUpdate)
			prmUpdate = New SqlParameter("@RequiredDate", SqlDbType.DateTime)
			prmUpdate.Value = objOrder.RequiredDate
			.Add(prmUpdate)
			prmUpdate = New SqlParameter("@ShippedDate", SqlDbType.DateTime)
			If objOrder.ShippedDate.ToShortDateString <> "1/1/0001" Then
				'Required to prevent "Failed to convert parameter value from a Nullable`1 
				'to a DateTime" and "Object must implement IConvertible" errors.
				prmUpdate.Value = CType(objOrder.ShippedDate, Date)
			Else
				prmUpdate.Value = Convert.DBNull
			End If
			.Add(prmUpdate)
			prmUpdate = New SqlParameter("@ShipVia", SqlDbType.Int)
			prmUpdate.Value = objOrder.ShipVia
			.Add(prmUpdate)
			prmUpdate = New SqlParameter("@Freight", SqlDbType.Money)
			prmUpdate.Value = objOrder.Freight
			.Add(prmUpdate)
			prmUpdate = New SqlParameter("@ShipName", SqlDbType.NVarChar, 40)
			prmUpdate.Value = objOrder.ShipName
			.Add(prmUpdate)
			prmUpdate = New SqlParameter("@ShipAddress", SqlDbType.NVarChar, 60)
			prmUpdate.Value = objOrder.ShipAddress
			.Add(prmUpdate)
			prmUpdate = New SqlParameter("@ShipCity", SqlDbType.NVarChar, 15)
			prmUpdate.Value = objOrder.ShipCity
			.Add(prmUpdate)
			prmUpdate = New SqlParameter("@ShipRegion", SqlDbType.NVarChar, 15)
			If objOrder.ShipRegion Is Nothing Then
				prmUpdate.Value = Convert.DBNull
			Else
				prmUpdate.Value = objOrder.ShipRegion
			End If
			.Add(prmUpdate)
			prmUpdate = New SqlParameter("@ShipPostalCode", SqlDbType.NVarChar, 10)
			If objOrder.ShipPostalCode Is Nothing Then
				prmUpdate.Value = Convert.DBNull
			Else
				prmUpdate.Value = objOrder.ShipPostalCode
			End If
			.Add(prmUpdate)
			prmUpdate = New SqlParameter("@ShipCountry", SqlDbType.NVarChar, 10)
			prmUpdate.Value = objOrder.ShipCountry
			.Add(prmUpdate)
			prmUpdate = New SqlParameter("OrderID", SqlDbType.Int)
			prmUpdate.Value = objOrder.OrderID
			.Add(prmUpdate)
		End With

		Dim strSQL As String = Nothing
		strSQL = "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"

        Dim strConn As String = ConfigurationManager.ConnectionStrings("NorthwindConnection").ConnectionString
		Dim cnNwind As New SqlConnection(strConn)
		Try
			cmOrder.Connection = cnNwind
			cmOrder.CommandText = strSQL
			cnNwind.Open()
			Return cmOrder.ExecuteNonQuery
			cnNwind.Close()
		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
			cnNwind.Close()
			cmOrder.Dispose()
			cnNwind.Dispose()
		End Try
	End Function