wsordersbe.vb

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

	<WebMethod(Description:=strInsertOrder)> _
	Public Function InsertOrder( _
	 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) As Integer
		Dim objOrder As New Order
		With objOrder
			.OrderID = 0
			.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 ExecuteInsertOrder(objOrder)
	End Function

	Private Function ExecuteInsertOrder(ByVal objOrder As Order) As Integer
		Dim cmOrder As New SqlCommand
		With cmOrder.Parameters
			Dim prmInsert As SqlParameter
			prmInsert = New SqlParameter("@CustomerID", SqlDbType.NChar, 5)
			prmInsert.Value = objOrder.CustomerID
			.Add(prmInsert)
			prmInsert = New SqlParameter("@EmployeeID", SqlDbType.Int)
			prmInsert.Value = objOrder.EmployeeID
			.Add(prmInsert)
			prmInsert = New SqlParameter("@OrderDate", SqlDbType.DateTime)
			prmInsert.Value = objOrder.OrderDate
			.Add(prmInsert)
			prmInsert = New SqlParameter("@RequiredDate", SqlDbType.DateTime)
			prmInsert.Value = objOrder.RequiredDate
			.Add(prmInsert)
			prmInsert = 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.
				prmInsert.Value = CType(objOrder.ShippedDate, Date)
			Else
				prmInsert.Value = Convert.DBNull
			End If
			.Add(prmInsert)
			prmInsert = New SqlParameter("@ShipVia", SqlDbType.Int)
			prmInsert.Value = objOrder.ShipVia
			.Add(prmInsert)
			prmInsert = New SqlParameter("@Freight", SqlDbType.Money)
			prmInsert.Value = objOrder.Freight
			.Add(prmInsert)
			prmInsert = New SqlParameter("@ShipName", SqlDbType.NVarChar, 40)
			prmInsert.Value = objOrder.ShipName
			.Add(prmInsert)
			prmInsert = New SqlParameter("@ShipAddress", SqlDbType.NVarChar, 60)
			prmInsert.Value = objOrder.ShipAddress
			.Add(prmInsert)
			prmInsert = New SqlParameter("@ShipCity", SqlDbType.NVarChar, 15)
			prmInsert.Value = objOrder.ShipCity
			.Add(prmInsert)
			prmInsert = New SqlParameter("@ShipRegion", SqlDbType.NVarChar, 15)
			If objOrder.ShipRegion Is Nothing Then
				prmInsert.Value = Convert.DBNull
			Else
				prmInsert.Value = objOrder.ShipRegion
			End If
			.Add(prmInsert)
			prmInsert = New SqlParameter("@ShipPostalCode", SqlDbType.NVarChar, 10)
			If objOrder.ShipPostalCode Is Nothing Then
				prmInsert.Value = Convert.DBNull
			Else
				prmInsert.Value = objOrder.ShipPostalCode
			End If
			.Add(prmInsert)
			prmInsert = New SqlParameter("@ShipCountry", SqlDbType.NVarChar, 10)
			prmInsert.Value = objOrder.ShipCountry
			.Add(prmInsert)
		End With

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

        Dim strConn As String = ConfigurationManager.ConnectionStrings("NorthwindConnection").ConnectionString
		Dim cnNwind As New SqlConnection(strConn)
		Dim intOrderID As Integer
		Dim intOrders As Integer
		Try
			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)
			objOrder.OrderID = intOrderID
			cnNwind.Close()
			'Add default Order Details record
			Dim intDetails As Integer = InsertDetail(intOrderID, 1, 18D, 1, 0D)
			Return intOrderID
		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

	<WebMethod(Description:=strDeleteOrder)> _
	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 strConn As String = ConfigurationManager.ConnectionStrings("NorthwindConnection").ConnectionString
		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 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

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

	<WebMethod(Description:=strUpdateDetail)> _
	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
		'Update a single OrderDetail object
		If OrderID <> original_OrderID Then
			'Throw an immediate exception if users attempt to update
			'an OrderDetail object with OrderID <> original_OrderID
			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 objDetail As New OrderDetail
		With objDetail
			.OrderID = OrderID
			.ProductID = ProductID
			.UnitPrice = UnitPrice
			.Quantity = Quantity
			.Discount = Discount
		End With

		Dim cmOrder As New SqlCommand
		With cmOrder.Parameters
			Dim prmUpdate As SqlParameter
			prmUpdate = New SqlParameter("@OrderID", SqlDbType.Int)
			prmUpdate.Value = objDetail.OrderID
			.Add(prmUpdate)
			prmUpdate = New SqlParameter("@ProductID", SqlDbType.Int)
			prmUpdate.Value = objDetail.ProductID
			.Add(prmUpdate)
			prmUpdate = New SqlParameter("@UnitPrice", SqlDbType.Money)
			prmUpdate.Value = objDetail.UnitPrice
			.Add(prmUpdate)
			prmUpdate = New SqlParameter("@Quantity", SqlDbType.SmallInt)
			prmUpdate.Value = objDetail.Quantity
			.Add(prmUpdate)
			prmUpdate = New SqlParameter("@Discount", SqlDbType.Real)
			prmUpdate.Value = objDetail.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

		Dim strSQL As String = Nothing
		strSQL = "UPDATE [Order Details] SET OrderID = @OrderID, " + _
		 "ProductID = @ProductID, UnitPrice = @UnitPrice, " + _
		 "Quantity = @Quantity, Discount = @Discount WHERE " + _
		 "OrderID = @original_OrderID AND ProductID = @original_ProductID"

        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
		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

	<WebMethod(Description:=strInsertDetail)> _
 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
		'Insert a single OrderDetail object
		Dim objDetail As New OrderDetail
		With objDetail
			.OrderID = OrderID
			.ProductID = ProductID
			.UnitPrice = UnitPrice
			.Quantity = Quantity
			.Discount = Discount
		End With
		Return ExecuteInsertDetail(objDetail)
	End Function

	Private Function ExecuteInsertDetail(ByVal objDetail As OrderDetail) As Integer
		Dim cmOrder As New SqlCommand
		With cmOrder.Parameters
			Dim prmInsert As SqlParameter
			prmInsert = New SqlParameter("@OrderID", SqlDbType.Int)
			prmInsert.Value = objDetail.OrderID
			.Add(prmInsert)
			prmInsert = New SqlParameter("@ProductID", SqlDbType.Int)
			prmInsert.Value = objDetail.ProductID
			.Add(prmInsert)
			prmInsert = New SqlParameter("@UnitPrice", SqlDbType.Money)
			prmInsert.Value = objDetail.UnitPrice
			.Add(prmInsert)
			prmInsert = New SqlParameter("@Quantity", SqlDbType.SmallInt)
			prmInsert.Value = objDetail.ProductID
			.Add(prmInsert)
			prmInsert = New SqlParameter("@Discount", SqlDbType.Real)
			prmInsert.Value = objDetail.Discount
			.Add(prmInsert)
		End With

		Dim strSQL As String = Nothing
		strSQL = "INSERT INTO [Order Details](OrderID, ProductID, UnitPrice, " + _
		"Quantity, Discount) VALUES(@OrderID, @ProductID, @UnitPrice, " + _
		"@Quantity, @Discount)"

        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
		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

	<WebMethod(Description:=strDeleteDetail)> _
	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 strConn As String = ConfigurationManager.ConnectionStrings("NorthwindConnection").ConnectionString
		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 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
End Class