ordersbemethods.vb

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

Option Explicit On
Option Strict On

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient

Partial Public Class OrdersBE
	'Constants are for Web service descriptions
	Private Const strGetOrderByID As String = "Returns an OrderBE object specified by the OrderID parameter (10248 to 11077)."
	'Follwing is for DALC or Web service
	'Private strConn As String = "Server=localhost;Database=Northwind;UID=sa;PWD=whidbey"
    'Following is for BE in the App_Code folder
	Private strConn As String = "Server=localhost;Database=Northwind;Integrated Security=True"

	'***************************
	'Orders table SELECT methods
	'***************************

	Public Function GetAllOrders() As ArrayList
		Return GetOrders("SELECT * FROM Orders ORDER BY OrderID DESC")
	End Function

	Public Function GetTopOrders(ByVal Number As Integer) As ArrayList
		Return GetOrders("SELECT TOP " + Number.ToString + " * FROM Orders ORDER BY OrderID DESC")
	End Function

	Public Function GetOrdersByCustomerID(ByVal CustomerID As String) As ArrayList
		Return GetOrders("SELECT * FROM Orders WHERE CustomerID = '" + CustomerID + "' ORDER BY OrderID DESC")
	End Function

	Private Function GetOrderByOrderIDShort(ByVal OrderID As Integer) As ArrayList
		'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

	Public Function GetOrderByOrderID(ByVal OrderID As Integer) As ArrayList
		Dim strSQL As String = "SELECT OrderID, CustomerID, EmployeeID, OrderDate, " + _
		 "RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, " + _
		 "ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders " + _
		 "WHERE OrderID = " + OrderID.ToString
		Return GetOrders(strSQL)
	End Function

	Private Function GetOrders(ByVal strSQL As String) As ArrayList
		'Populates an OrderBE object with a single order
		Dim cnNwind As New SqlConnection(strConn)
		Dim cmOrder As New SqlCommand(strSQL, cnNwind)
		Dim drOrder As SqlDataReader = Nothing
		Dim OrderBE As New Order
		Try
			cnNwind.Open()
			drOrder = cmOrder.ExecuteReader()
			With drOrder
				If .HasRows Then
					Dim Orders As New ArrayList
					While .Read
						OrderBE = New Order
						OrderBE.OrderID = .GetInt32(0)
						OrderBE.CustomerID = .GetString(1)
						OrderBE.EmployeeID = .GetInt32(2)
						OrderBE.OrderDate = .GetDateTime(3)
						If Not IsDBNull(.Item(4)) Then
							OrderBE.RequiredDate = .GetDateTime(4)
						End If
						If Not IsDBNull(.Item(5)) Then
							OrderBE.ShippedDate = .GetDateTime(5)
						End If
						OrderBE.ShipVia = .GetInt32(6)
						If Not IsDBNull(.Item(7)) Then
							OrderBE.Freight = .GetDecimal(7)
						End If
						OrderBE.ShipName = .GetString(8)
						OrderBE.ShipAddress = .GetString(9)
						OrderBE.ShipCity = .GetString(10)
						If Not IsDBNull(.Item(11)) Then
							OrderBE.ShipRegion = .GetString(11)
						End If
						If Not IsDBNull(.Item(12)) Then
							OrderBE.ShipPostalCode = .GetString(12)
						End If
						OrderBE.ShipCountry = .GetString(13)
						Orders.Add(OrderBE)
					End While
					.Close()
					Return Orders
				Else
					Throw New Exception("Order(s) not found for parameter supplied.")
				End If
			End With
		Catch excSql As SqlException
			Throw excSql
		Catch excSys As System.Exception
			Throw excSys
		Finally
			drOrder.Close()
			cnNwind.Close()
			drOrder.Dispose()
			cmOrder.Dispose()
			cnNwind.Dispose()
		End Try
	End Function

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

	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
		Dim cnNwind As New SqlConnection(strConn)
		Dim cmOrder As New SqlCommand
		Try
			Dim ordUpdate As New Order
			Dim strSQL As String = Nothing
			With ordUpdate
				.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

				Dim blnUseParams As Boolean = True
				If blnUseParams Then
					'Use prepared statements with parameters
					With cmOrder.Parameters
						Dim prmUpdate As SqlParameter
						prmUpdate = New SqlParameter("@CustomerID", SqlDbType.NChar, 5)
						prmUpdate.Value = ordUpdate.CustomerID
						.Add(prmUpdate)
						prmUpdate = New SqlParameter("@EmployeeID", SqlDbType.Int)
						prmUpdate.Value = ordUpdate.EmployeeID
						.Add(prmUpdate)
						prmUpdate = New SqlParameter("@OrderDate", SqlDbType.DateTime)
						prmUpdate.Value = ordUpdate.OrderDate
						.Add(prmUpdate)
						prmUpdate = New SqlParameter("@RequiredDate", SqlDbType.DateTime)
						prmUpdate.Value = ordUpdate.RequiredDate
						.Add(prmUpdate)
						prmUpdate = New SqlParameter("@ShippedDate", SqlDbType.DateTime)
						If ordUpdate.ShippedDate.HasValue 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(ordUpdate.ShippedDate, Date)
						Else
							prmUpdate.Value = Convert.DBNull
						End If
						.Add(prmUpdate)
						prmUpdate = New SqlParameter("@ShipVia", SqlDbType.Int)
						prmUpdate.Value = ordUpdate.ShipVia
						.Add(prmUpdate)
						prmUpdate = New SqlParameter("@Freight", SqlDbType.Money)
						prmUpdate.Value = ordUpdate.Freight
						.Add(prmUpdate)
						prmUpdate = New SqlParameter("@ShipName", SqlDbType.NVarChar, 40)
						prmUpdate.Value = ordUpdate.ShipName
						.Add(prmUpdate)
						prmUpdate = New SqlParameter("@ShipAddress", SqlDbType.NVarChar, 60)
						prmUpdate.Value = ordUpdate.ShipAddress
						.Add(prmUpdate)
						prmUpdate = New SqlParameter("@ShipCity", SqlDbType.NVarChar, 15)
						prmUpdate.Value = ordUpdate.ShipCity
						.Add(prmUpdate)
						prmUpdate = New SqlParameter("@ShipRegion", SqlDbType.NVarChar, 15)
						If ordUpdate.ShipRegion Is Nothing Then
							prmUpdate.Value = Convert.DBNull
						Else
							prmUpdate.Value = ordUpdate.ShipRegion
						End If
						.Add(prmUpdate)
						prmUpdate = New SqlParameter("@ShipPostalCode", SqlDbType.NVarChar, 10)
						If ordUpdate.ShipPostalCode Is Nothing Then
							prmUpdate.Value = Convert.DBNull
						Else
							prmUpdate.Value = ordUpdate.ShipPostalCode
						End If
						.Add(prmUpdate)
						prmUpdate = New SqlParameter("@ShipCountry", SqlDbType.NVarChar, 10)
						prmUpdate.Value = ordUpdate.ShipCountry
						.Add(prmUpdate)
						prmUpdate = New SqlParameter("OrderID", SqlDbType.Int)
						prmUpdate.Value = ordUpdate.OrderID
						.Add(prmUpdate)
					End With
					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"
				Else
					'Following is for consistency with SELECT statements
					strSQL = "UPDATE Orders SET CustomerID = '" + .CustomerID + "', " + _
					 "EmployeeID = " + .EmployeeID.ToString + ", OrderDate = '" + .OrderDate.ToShortDateString + "', " + _
					 "RequiredDate = '" + .RequiredDate.ToShortDateString + "', ShippedDate = "
					If .ShippedDate.HasValue Then
						strSQL += "'" + .ShippedDate.ToString + "', "
					Else
						strSQL += "NULL, "
					End If
					strSQL += "ShipVia = " + .ShipVia.ToString + ", Freight = " + .Freight.ToString + ", " + _
					 "ShipName = '" + ShipName + "', ShipAddress = '" + .ShipAddress + "', " + _
					 "ShipCity = '" + .ShipCity + "', ShipRegion = "
					'Workaround for template issue
					If .ShipRegion Is Nothing OrElse .ShipRegion = "" Then
						strSQL += "NULL, "
					Else
						strSQL += "'" + .ShipRegion + "', "
					End If
					strSQL += "ShipPostalCode = "
					If .ShipPostalCode Is Nothing OrElse .ShipPostalCode = "" Then
						strSQL += "NULL, "
					Else
						strSQL += "'" + .ShipPostalCode + "', "
					End If
					strSQL += "ShipCountry = '" + .ShipCountry + "' WHERE OrderID = " + .OrderID.ToString
				End If
			End With
			'cnNwind = New SqlConnection(strConn)
			cmOrder.Connection = cnNwind
			cmOrder.CommandText = strSQL
			cnNwind.Open()
			Return cmOrder.ExecuteNonQuery
			cnNwind.Close()
		Catch exc As Exception
			'Client handles the exception
			Throw exc
		Finally
			cnNwind.Close()
			cmOrder.Dispose()
			cnNwind.Dispose()
		End Try
	End Function

	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 ordInsert As New Order
		Dim strSQL As String = Nothing
		Dim cnNwind As New SqlConnection(strConn)
		Dim cmOrder As New SqlCommand
		Dim intOrderID As Integer
		Dim intOrders As Integer
		Try
			With ordInsert
				.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

				Dim blnUseParams As Boolean = True
				If blnUseParams Then
					With cmOrder.Parameters
						Dim prmInsert As SqlParameter
						prmInsert = New SqlParameter("@CustomerID", SqlDbType.NChar, 5)
						prmInsert.Value = ordInsert.CustomerID
						.Add(prmInsert)
						prmInsert = New SqlParameter("@EmployeeID", SqlDbType.Int)
						prmInsert.Value = ordInsert.EmployeeID
						.Add(prmInsert)
						prmInsert = New SqlParameter("@OrderDate", SqlDbType.DateTime)
						prmInsert.Value = ordInsert.OrderDate
						.Add(prmInsert)
						prmInsert = New SqlParameter("@RequiredDate", SqlDbType.DateTime)
						prmInsert.Value = ordInsert.RequiredDate
						.Add(prmInsert)
						prmInsert = New SqlParameter("@ShippedDate", SqlDbType.DateTime)
						If ordInsert.ShippedDate.HasValue 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(ordInsert.ShippedDate, Date)
						Else
							prmInsert.Value = Convert.DBNull
						End If
						.Add(prmInsert)
						prmInsert = New SqlParameter("@ShipVia", SqlDbType.Int)
						prmInsert.Value = ordInsert.ShipVia
						.Add(prmInsert)
						prmInsert = New SqlParameter("@Freight", SqlDbType.Money)
						prmInsert.Value = ordInsert.Freight
						.Add(prmInsert)
						prmInsert = New SqlParameter("@ShipName", SqlDbType.NVarChar, 40)
						prmInsert.Value = ordInsert.ShipName
						.Add(prmInsert)
						prmInsert = New SqlParameter("@ShipAddress", SqlDbType.NVarChar, 60)
						prmInsert.Value = ordInsert.ShipAddress
						.Add(prmInsert)
						prmInsert = New SqlParameter("@ShipCity", SqlDbType.NVarChar, 15)
						prmInsert.Value = ordInsert.ShipCity
						.Add(prmInsert)
						prmInsert = New SqlParameter("@ShipRegion", SqlDbType.NVarChar, 15)
						If ordInsert.ShipRegion Is Nothing Then
							prmInsert.Value = Convert.DBNull
						Else
							prmInsert.Value = ordInsert.ShipRegion