Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Northwind45.BusinessObject
 
Namespace Northwind45.DataLayer.Base
     ''' <summary>
     ''' Base class for ProductsDataLayer.  Do not make changes to this class,
     ''' instead, put additional code in the ProductsDataLayer class
     ''' </summary>
     Public Class ProductsDataLayerBase
 
         ' constructor
         Public Sub New()
         End Sub
 
         ''' <summary>
         ''' Selects a record by primary key(s)
         ''' </summary>
         Public Shared Function SelectByPrimaryKey(ByVal productID As Integer) As Products
              Dim storedProcName As String = "[dbo].[aspx_Products_SelectByPrimaryKey]"
              Dim connection As SqlConnection = Dbase.GetConnection()
              Dim command As SqlCommand = Dbase.GetCommand(storedProcName, connection)
 
              ' parameters
              command.Parameters.AddWithValue("@productID", productID)
 
              Dim ds As DataSet = Dbase.GetDbaseDataSet(command)
              Dim objProducts As Products = Nothing
 
              If ds.Tables(0).Rows.Count > 0 Then
                  objProducts = New Products()
                  objProducts.ProductID = DirectCast(ds.Tables(0).Rows(0)("ProductID"), Integer)
                  objProducts.ProductName = DirectCast(ds.Tables(0).Rows(0)("ProductName"), String)
 
                  If Not ds.Tables(0).Rows(0)("SupplierID").Equals(System.DBNull.Value) Then
                      objProducts.SupplierID = DirectCast(ds.Tables(0).Rows(0)("SupplierID"), Integer)
                  Else
                      objProducts.SupplierID = Nothing
                  End If
 
 
                  If Not ds.Tables(0).Rows(0)("CategoryID").Equals(System.DBNull.Value) Then
                      objProducts.CategoryID = DirectCast(ds.Tables(0).Rows(0)("CategoryID"), Integer)
                  Else
                      objProducts.CategoryID = Nothing
                  End If
 
 
                  If Not ds.Tables(0).Rows(0)("QuantityPerUnit").Equals(System.DBNull.Value) Then
                      objProducts.QuantityPerUnit = DirectCast(ds.Tables(0).Rows(0)("QuantityPerUnit"), String)
                  Else
                      objProducts.QuantityPerUnit = Nothing
                  End If
 
                  If Not ds.Tables(0).Rows(0)("UnitPrice").Equals(System.DBNull.Value) Then
                      objProducts.UnitPrice = DirectCast(ds.Tables(0).Rows(0)("UnitPrice"), Decimal)
                  Else
                      objProducts.UnitPrice = Nothing
                  End If
 
                  If Not ds.Tables(0).Rows(0)("UnitsInStock").Equals(System.DBNull.Value) Then
                      objProducts.UnitsInStock = DirectCast(ds.Tables(0).Rows(0)("UnitsInStock"), Short)
                  Else
                      objProducts.UnitsInStock = Nothing
                  End If
 
                  If Not ds.Tables(0).Rows(0)("UnitsOnOrder").Equals(System.DBNull.Value) Then
                      objProducts.UnitsOnOrder = DirectCast(ds.Tables(0).Rows(0)("UnitsOnOrder"), Short)
                  Else
                      objProducts.UnitsOnOrder = Nothing
                  End If
 
                  If Not ds.Tables(0).Rows(0)("ReorderLevel").Equals(System.DBNull.Value) Then
                      objProducts.ReorderLevel = DirectCast(ds.Tables(0).Rows(0)("ReorderLevel"), Short)
                  Else
                      objProducts.ReorderLevel = Nothing
                  End If
                  objProducts.Discontinued = DirectCast(ds.Tables(0).Rows(0)("Discontinued"), Boolean)
 
              End If
 
            command.Dispose()
            connection.Close()
            connection.Dispose()
            ds.Dispose()
 
            Return objProducts
         End Function
 
         ''' <summary>
         ''' Gets the total number of records in the Products table
         ''' </summary>
         Public Shared Function GetRecordCount() As Integer
             Return GetRecordCountShared("[dbo].[aspx_Products_GetRecordCount]", Nothing, Nothing, True, Nothing)
         End Function
 
         ''' <summary>
         ''' Gets the total number of records in the Products table by SupplierID
         ''' </summary>
         Public Shared Function GetRecordCountBySupplierID(supplierID As Integer) As Integer
             Return GetRecordCountShared("[dbo].[aspx_Products_GetRecordCountBySupplierID]", "supplierID", supplierID, True, Nothing)
         End Function
 
         ''' <summary>
         ''' Gets the total number of records in the Products table by CategoryID
         ''' </summary>
         Public Shared Function GetRecordCountByCategoryID(categoryID As Integer) As Integer
             Return GetRecordCountShared("[dbo].[aspx_Products_GetRecordCountByCategoryID]", "categoryID", categoryID, True, Nothing)
         End Function
 
         Public Shared Function GetRecordCountShared(Optional ByVal storedProcName As String = Nothing, Optional ByVal param As String = Nothing, Optional ByVal paramValue As Object = Nothing, Optional ByVal isUseStoredProc As Boolean = True, Optional ByVal dynamicSQL As String = Nothing) As Integer
              Dim connection As SqlConnection = Dbase.GetConnection()
              Dim command As SqlCommand
 
              If isUseStoredProc Then
                  command = Dbase.GetCommand(storedProcName, connection)
              Else
                  command = new SqlCommand(dynamicSQL, connection)
              End If
 
              ' parameters
              Select Case param
                  Case "supplierID"
                      command.Parameters.AddWithValue("@supplierID", paramValue)
                  Case "categoryID"
                      command.Parameters.AddWithValue("@categoryID", paramValue)
                  Case Else
                      Exit Select
              End Select
 
              Dim ds As DataSet = Dbase.GetDbaseDataSet(command)
              Dim recordCount As Integer = 0
 
              If ds.Tables(0).Rows.Count > 0 Then
                  recordCount = DirectCast(ds.Tables(0).Rows(0)("RecordCount"), Integer)
              End If
 
              command.Dispose()
              connection.Close()
              connection.Dispose()
              ds.Dispose()
 
              Return recordCount
         End Function
 
         ''' <summary>
         ''' Gets the total number of records in the Products table based on search parameters
         ''' </summary>
         Public Shared Function GetRecordCountDynamicWhere(productID As Integer?, productName As String, supplierID As Integer?, categoryID As Integer?, quantityPerUnit As String, unitPrice As Decimal?, unitsInStock As Short?, unitsOnOrder As Short?, reorderLevel As Short?, discontinued As Boolean?) As Integer
              Dim storedProcName As String = "[dbo].[aspx_Products_GetRecordCountWhereDynamic]"
              Dim connection As SqlConnection = Dbase.GetConnection()
              Dim command As SqlCommand = Dbase.GetCommand(storedProcName, connection)
 
              ' search parameters
              AddSearchCommandParamsShared(command, productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued)
 
              Dim ds As DataSet = Dbase.GetDbaseDataSet(command)
              Dim recordCount As Integer = 0
 
              If ds.Tables(0).Rows.Count > 0 Then
                  recordCount = DirectCast(ds.Tables(0).Rows(0)("RecordCount"), Integer)
              End If
 
              command.Dispose()
              connection.Close()
              connection.Dispose()
              ds.Dispose()
 
              Return recordCount
         End Function
 
         ''' <summary>
         ''' Selects Products records sorted by the sortByExpression and returns records between the start and end
         ''' </summary>
         Public Shared Function SelectSkipAndTake(sortByExpression As String, start As Integer, ending As Integer) As ProductsCollection
             Return SelectShared("[dbo].[aspx_Products_SelectSkipAndTake]", Nothing, Nothing, True, Nothing, sortByExpression, start, ending)
         End Function
 
         ''' <summary>
         ''' Selects records by SupplierID as a collection (List) of Products sorted by the sortByExpression and returns the maximumRows (# of records) starting from the startRowIndex
         ''' </summary>
         Public Shared Function SelectSkipAndTakeBySupplierID(sortByExpression As String, start As Integer, ending As Integer, supplierID As Integer) As ProductsCollection
             Return SelectShared("[dbo].[aspx_Products_SelectSkipAndTakeBySupplierID]", "supplierID", supplierID, True, Nothing, sortByExpression, start, ending)
         End Function
 
         ''' <summary>
         ''' Selects records by CategoryID as a collection (List) of Products sorted by the sortByExpression and returns the maximumRows (# of records) starting from the startRowIndex
         ''' </summary>
         Public Shared Function SelectSkipAndTakeByCategoryID(sortByExpression As String, start As Integer, ending As Integer, categoryID As Integer) As ProductsCollection
             Return SelectShared("[dbo].[aspx_Products_SelectSkipAndTakeByCategoryID]", "categoryID", categoryID, True, Nothing, sortByExpression, start, ending)
         End Function
 
         Public Shared Function SelectSkipAndTakeDynamicWhere(productID As Integer?, productName As String, supplierID As Integer?, categoryID As Integer?, quantityPerUnit As String, unitPrice As Decimal?, unitsInStock As Short?, unitsOnOrder As Short?, reorderLevel As Short?, discontinued As Boolean?, sortByExpression As String, start As Integer, ending  As Integer) As ProductsCollection
              Dim storedProcName As String = "[dbo].[aspx_Products_SelectSkipAndTakeWhereDynamic]"
              Dim connection As SqlConnection = Dbase.GetConnection()
              Dim command As SqlCommand = Dbase.GetCommand(storedProcName, connection)
 
              ' select, skip, take, sort parameters
              command.Parameters.AddWithValue("@start", start)
              command.Parameters.AddWithValue("@end", ending)
              command.Parameters.AddWithValue("@sortByExpression", sortByExpression)
 
              ' search parameters
              AddSearchCommandParamsShared(command, productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued)
 
              Dim ds As DataSet = Dbase.GetDbaseDataSet(command)
              Dim objProductsCol As ProductsCollection = New ProductsCollection()
 
              If ds.Tables(0).Rows.Count > 0 Then
                  For Each dr As DataRow in ds.Tables(0).Rows
                      Dim objProducts As Products = CreateProductsFromDataRowShared(dr)
                      objProductsCol.Add(objProducts)
                  Next
              End If
 
              command.Dispose()
              connection.Close()
              connection.Dispose()
              ds.Dispose()
 
              Return objProductsCol
         End Function
 
         ''' <summary>
         ''' Gets the grand total or sum of fields with a money of decimal data type
         ''' </summary>
         Public Shared Function SelectTotals() As Products
              Dim storedProcName As String = "[dbo].[aspx_Products_SelectTotals]"
              Dim connection As SqlConnection = Dbase.GetConnection()
              Dim command As SqlCommand = Dbase.GetCommand(storedProcName, connection)
 
              Dim ds As DataSet = Dbase.GetDbaseDataSet(command)
              Dim objProducts As Products = Nothing
 
              If ds.Tables(0).Rows.Count > 0 Then
                  objProducts = New Products()
 
                 If Not DbNull.Value.Equals(ds.Tables(0).Rows(0)("UnitPriceTotal")) Then
                      objProducts.UnitPriceTotal = DirectCast(ds.Tables(0).Rows(0)("UnitPriceTotal"), Decimal)
                 End If
              End If
 
              command.Dispose()
              connection.Close()
              connection.Dispose()
              ds.Dispose()
 
              Return objProducts
         End Function
 
         ''' <summary>
         ''' Selects all Products
         ''' </summary>
         Public Shared Function SelectAll() As ProductsCollection
             Return SelectShared("[dbo].[aspx_Products_SelectAll]", String.Empty, Nothing)
         End Function
 
         ''' <summary>
         ''' Selects records based on the passed filters as a collection (List) of Products.
         ''' </summary>
         Public Shared Function SelectAllDynamicWhere(productID As System.Nullable(Of Integer), productName As String, supplierID As System.Nullable(Of Integer), categoryID As System.Nullable(Of Integer), quantityPerUnit As String, unitPrice As System.Nullable(Of Decimal), unitsInStock As System.Nullable(Of Short), unitsOnOrder As System.Nullable(Of Short), reorderLevel As System.Nullable(Of Short), discontinued As System.Nullable(Of Boolean)) As ProductsCollection
              Dim storedProcName As String = "[dbo].[aspx_Products_SelectAllWhereDynamic]"
              Dim connection As SqlConnection = Dbase.GetConnection()
              Dim command As SqlCommand = Dbase.GetCommand(storedProcName, connection)
 
              ' parameters
              command.Parameters.AddWithValue("@productID", productID)
              command.Parameters.AddWithValue("@productName", productName)
              command.Parameters.AddWithValue("@supplierID", supplierID)
              command.Parameters.AddWithValue("@categoryID", categoryID)
              command.Parameters.AddWithValue("@quantityPerUnit", quantityPerUnit)
              command.Parameters.AddWithValue("@unitPrice", unitPrice)
              command.Parameters.AddWithValue("@unitsInStock", unitsInStock)
              command.Parameters.AddWithValue("@unitsOnOrder", unitsOnOrder)
              command.Parameters.AddWithValue("@reorderLevel", reorderLevel)
              command.Parameters.AddWithValue("@discontinued", discontinued)
 
 
              Dim ds As DataSet = Dbase.GetDbaseDataSet(command)
              Dim objProductsCol As New ProductsCollection()
              Dim objProducts As Products
 
              If ds.Tables(0).Rows.Count > 0 Then
                 For Each dr As DataRow In ds.Tables(0).Rows
                     objProducts = New Products()
                     objProducts.ProductID = DirectCast(dr("ProductID"), Integer)
                     objProducts.ProductName = dr("ProductName").ToString()
 
                     If Not System.DbNull.Value.Equals(dr("SupplierID")) Then
                         objProducts.SupplierID = DirectCast(dr("SupplierID"), Integer)
                     Else
                         objProducts.SupplierID = Nothing
                     End If
 
 
                     If Not System.DbNull.Value.Equals(dr("CategoryID")) Then
                         objProducts.CategoryID = DirectCast(dr("CategoryID"), Integer)
                     Else
                         objProducts.CategoryID = Nothing
                     End If
 
 
                     If Not System.DbNull.Value.Equals(dr("QuantityPerUnit")) Then
                         objProducts.QuantityPerUnit = dr("QuantityPerUnit").ToString()
                     Else
                         objProducts.QuantityPerUnit = Nothing
                     End If
 
                     If Not System.DbNull.Value.Equals(dr("UnitPrice")) Then
                         objProducts.UnitPrice = DirectCast(dr("UnitPrice"), Decimal)
                     Else
                         objProducts.UnitPrice = Nothing
                     End If
 
                     If Not System.DbNull.Value.Equals(dr("UnitsInStock")) Then
                         objProducts.UnitsInStock = DirectCast(dr("UnitsInStock"), Short)
                     Else
                         objProducts.UnitsInStock = Nothing
                     End If
 
                     If Not System.DbNull.Value.Equals(dr("UnitsOnOrder")) Then
                         objProducts.UnitsOnOrder = DirectCast(dr("UnitsOnOrder"), Short)
                     Else
                         objProducts.UnitsOnOrder = Nothing
                     End If
 
                     If Not System.DbNull.Value.Equals(dr("ReorderLevel")) Then
                         objProducts.ReorderLevel = DirectCast(dr("ReorderLevel"), Short)
                     Else
                         objProducts.ReorderLevel = Nothing
                     End If
                     objProducts.Discontinued = DirectCast(dr("Discontinued"), Boolean)
 
                     objProductsCol.Add(objProducts)
                 Next
              End If
 
              command.Dispose()
              connection.Close()
              connection.Dispose()
              ds.Dispose()
 
              Return objProductsCol
         End Function
 
         ''' <summary>
         ''' Selects all Products by Suppliers, related to column SupplierID
         ''' </summary>
         Public Shared Function SelectProductsCollectionBySuppliers(supplierID As Integer) As ProductsCollection
             Return SelectShared("[dbo].[aspx_Products_SelectAllBySupplierID]", "supplierID", supplierID)
         End Function
 
         ''' <summary>
         ''' Selects all Products by Categories, related to column CategoryID
         ''' </summary>
         Public Shared Function SelectProductsCollectionByCategories(categoryID As Integer) As ProductsCollection
             Return SelectShared("[dbo].[aspx_Products_SelectAllByCategoryID]", "categoryID", categoryID)
         End Function
 
         ''' <summary>
         ''' Selects ProductID and ProductName columns for use with a DropDownList web control
         ''' </summary>
         Public Shared Function SelectProductsDropDownListData() As ProductsCollection
              Dim storedProcName As String = "[dbo].[aspx_Products_SelectDropDownListData]"
              Dim connection As SqlConnection = Dbase.GetConnection()
              Dim command As SqlCommand = Dbase.GetCommand(storedProcName, connection)
 
              Dim ds As DataSet = Dbase.GetDbaseDataSet(command)
              Dim objProductsCol As New ProductsCollection()
              Dim objProducts As Products
 
              If ds.Tables(0).Rows.Count > 0 Then
                 For Each dr As DataRow In ds.Tables(0).Rows
                     objProducts = New Products()
                     objProducts.ProductID = DirectCast(dr("ProductID"), Integer)
                     objProducts.ProductName = DirectCast(dr("ProductName"), String)
 
                     objProductsCol.Add(objProducts)
                 Next
              End If
 
              command.Dispose()
              connection.Close()
              connection.Dispose()
              ds.Dispose()
 
              Return objProductsCol
         End Function
 
         Public Shared Function SelectShared(storedProcName As String, param As String, paramValue As Object, Optional ByVal isUseStoredProc As Boolean = True, Optional ByVal dynamicSQL As String = Nothing, Optional ByVal sortByExpression As String = Nothing, Optional ByVal start As Integer? = Nothing, Optional ByVal ending As Integer? = Nothing) As ProductsCollection
              Dim connection As SqlConnection = Dbase.GetConnection()
              Dim command As SqlCommand
 
              If isUseStoredProc Then
                  command = Dbase.GetCommand(storedProcName, connection)
              Else
                  command = new SqlCommand(dynamicSQL, connection)
              End If
 
              ' select, skip, take, sort parameters
              If Not [String].IsNullOrEmpty(sortByExpression) AndAlso start IsNot Nothing AndAlso ending IsNot Nothing Then
                  command.Parameters.AddWithValue("@start", start.Value)
                  command.Parameters.AddWithValue("@end", ending.Value)
                  command.Parameters.AddWithValue("@sortByExpression", sortByExpression)
              End If
 
              ' parameters
              Select Case param
                  Case "supplierID"
                      command.Parameters.AddWithValue("@supplierID", paramValue)
                      Exit Select
                  Case "categoryID"
                      command.Parameters.AddWithValue("@categoryID", paramValue)
                      Exit Select
                  Case Else
                      Exit Select
              End Select
 
              Dim ds As DataSet = Dbase.GetDbaseDataSet(command)
              Dim objProductsCol As New ProductsCollection()
 
              If ds.Tables(0).Rows.Count > 0 Then
                 For Each dr As DataRow In ds.Tables(0).Rows
                     Dim objProducts As Products = CreateProductsFromDataRowShared(dr)
                     objProductsCol.Add(objProducts)
                 Next
              End If
 
              command.Dispose()
              connection.Close()
              connection.Dispose()
              ds.Dispose()
 
              Return objProductsCol
         End Function
 
         ''' <summary>
         ''' Inserts a record
         ''' </summary>
         Public Shared Function Insert(objProducts As Products) As Integer
             Dim storedProcName As String = "[dbo].[aspx_Products_Insert]"
             Return InsertUpdate(objProducts, False, storedProcName)
         End Function
 
         ''' <summary>
         ''' Updates a record
         ''' </summary>
         Public Shared Sub Update(objProducts As Products)
             Dim storedProcName As String = "[dbo].[aspx_Products_Update]"
             InsertUpdate(objProducts, True, storedProcName)
         End Sub
 
         Private Shared Function InsertUpdate(ByVal objProducts As Products, isUpdate As Boolean, storedProcName As String) As Integer
             Dim connection As SqlConnection = Dbase.GetConnection()
             Dim command As SqlCommand = Dbase.GetCommand(storedProcName, connection)
 
             Dim supplierID As Object = objProducts.SupplierID
             Dim categoryID As Object = objProducts.CategoryID
             Dim quantityPerUnit As Object = objProducts.QuantityPerUnit
             Dim unitPrice As Object = objProducts.UnitPrice
             Dim unitsInStock As Object = objProducts.UnitsInStock
             Dim unitsOnOrder As Object = objProducts.UnitsOnOrder
             Dim reorderLevel As Object = objProducts.ReorderLevel
 
             If objProducts.SupplierID Is Nothing Then
                 supplierID = System.DBNull.Value
             End If
 
             If objProducts.CategoryID Is Nothing Then
                 categoryID = System.DBNull.Value
             End If
 
             If String.IsNullOrEmpty(objProducts.QuantityPerUnit) Then
                 quantityPerUnit = System.DBNull.Value
             End If
 
             If objProducts.UnitPrice Is Nothing Then
                 unitPrice = System.DBNull.Value
             End If
 
             If objProducts.UnitsInStock Is Nothing Then
                 unitsInStock = System.DBNull.Value
             End If
 
             If objProducts.UnitsOnOrder Is Nothing Then
                 unitsOnOrder = System.DBNull.Value
             End If
 
             If objProducts.ReorderLevel Is Nothing Then
                 reorderLevel = System.DBNull.Value
             End If
 
             ' for update only
             If isUpdate Then
                 command.Parameters.AddWithValue("@productID", objProducts.ProductID)
             End If
 
             command.Parameters.AddWithValue("@productName", objProducts.ProductName)
             command.Parameters.AddWithValue("@supplierID", supplierID)
             command.Parameters.AddWithValue("@categoryID", categoryID)
             command.Parameters.AddWithValue("@quantityPerUnit", quantityPerUnit)
             command.Parameters.AddWithValue("@unitPrice", unitPrice)
             command.Parameters.AddWithValue("@unitsInStock", unitsInStock)
             command.Parameters.AddWithValue("@unitsOnOrder", unitsOnOrder)
             command.Parameters.AddWithValue("@reorderLevel", reorderLevel)
             command.Parameters.AddWithValue("@discontinued", objProducts.Discontinued)
 
             ' execute and return value
             Dim newlyCreatedProductID As Integer = objProducts.ProductID
 
             If isUpdate Then
                 command.ExecuteNonQuery()
             Else
                 newlyCreatedProductID = DirectCast(command.ExecuteScalar(), Integer)
             End If
 
             command.Dispose()
             connection.Close()
             connection.Dispose()
 
             Return newlyCreatedProductID
         End Function
 
         ''' <summary>
         ''' Deletes a record based on primary key(s)
         ''' </summary>
         Public Shared Sub Delete(ByVal productID As Integer)
             Dim connection As SqlConnection = Dbase.GetConnection()
             Dim command As SqlCommand = Dbase.GetCommand("[dbo].[aspx_Products_Delete]", connection)
 
             command.Parameters.AddWithValue("@productID", productID)
 
             ' execute stored proc
             command.ExecuteNonQuery()
             command.Dispose()
             connection.Close()
             connection.Dispose()
         End Sub
 
         ''' <summary>
         ''' Adds search parameters to the Command object
         ''' </summary>
         Private Shared Sub AddSearchCommandParamsShared(command As SqlCommand, productID As Integer?, productName As String, supplierID As Integer?, categoryID As Integer?, quantityPerUnit As String, unitPrice As Decimal?, unitsInStock As Short?, unitsOnOrder As Short?, reorderLevel As Short?, discontinued As Boolean?)
              If Not productID Is Nothing Then
                  command.Parameters.AddWithValue("@productID", productID)
              Else
                  command.Parameters.AddWithValue("@productID", System.DBNull.Value)
              End If
 
              If Not [String].IsNullOrEmpty(productName) Then
                  command.Parameters.AddWithValue("@productName", productName)
              Else
                  command.Parameters.AddWithValue("@productName", System.DBNull.Value)
              End If
 
              If Not supplierID Is Nothing Then
                  command.Parameters.AddWithValue("@supplierID", supplierID)
              Else
                  command.Parameters.AddWithValue("@supplierID", System.DBNull.Value)
              End If
 
              If Not categoryID Is Nothing Then
                  command.Parameters.AddWithValue("@categoryID", categoryID)
              Else
                  command.Parameters.AddWithValue("@categoryID", System.DBNull.Value)
              End If
 
              If Not [String].IsNullOrEmpty(quantityPerUnit) Then
                  command.Parameters.AddWithValue("@quantityPerUnit", quantityPerUnit)
              Else
                  command.Parameters.AddWithValue("@quantityPerUnit", System.DBNull.Value)
              End If
 
              If Not unitPrice Is Nothing Then
                  command.Parameters.AddWithValue("@unitPrice", unitPrice)
              Else
                  command.Parameters.AddWithValue("@unitPrice", System.DBNull.Value)
              End If
 
              If Not unitsInStock Is Nothing Then
                  command.Parameters.AddWithValue("@unitsInStock", unitsInStock)
              Else
                  command.Parameters.AddWithValue("@unitsInStock", System.DBNull.Value)
              End If
 
              If Not unitsOnOrder Is Nothing Then
                  command.Parameters.AddWithValue("@unitsOnOrder", unitsOnOrder)
              Else
                  command.Parameters.AddWithValue("@unitsOnOrder", System.DBNull.Value)
              End If
 
              If Not reorderLevel Is Nothing Then
                  command.Parameters.AddWithValue("@reorderLevel", reorderLevel)
              Else
                  command.Parameters.AddWithValue("@reorderLevel", System.DBNull.Value)
              End If
 
              If Not discontinued Is Nothing Then
                  command.Parameters.AddWithValue("@discontinued", discontinued)
              Else
                  command.Parameters.AddWithValue("@discontinued", System.DBNull.Value)
              End If
 
         End Sub
 
         ''' <summary>
         ''' Creates a Products object from the passed data row
         ''' </summary>
         Private Shared Function CreateProductsFromDataRowShared(dr As DataRow) As Products
             Dim objProducts As Products = New Products()
 
             objProducts.ProductID = DirectCast(dr("ProductID"), Integer)
             objProducts.ProductName = dr("ProductName").ToString()
 
             If Not DbNull.Value.Equals(dr("SupplierID")) Then
                 objProducts.SupplierID = DirectCast(dr("SupplierID"), Integer)
             Else
                 objProducts.SupplierID = Nothing
             End If
 
 
             If Not DbNull.Value.Equals(dr("CategoryID")) Then
                 objProducts.CategoryID = DirectCast(dr("CategoryID"), Integer)
             Else
                 objProducts.CategoryID = Nothing
             End If
 
 
             If Not DbNull.Value.Equals(dr("QuantityPerUnit")) Then
                 objProducts.QuantityPerUnit = dr("QuantityPerUnit").ToString()
             Else
                 objProducts.QuantityPerUnit = Nothing
             End If
 
             If Not DbNull.Value.Equals(dr("UnitPrice")) Then
                 objProducts.UnitPrice = DirectCast(dr("UnitPrice"), Decimal)
             Else
                 objProducts.UnitPrice = Nothing
             End If
 
             If Not DbNull.Value.Equals(dr("UnitsInStock")) Then
                 objProducts.UnitsInStock = DirectCast(dr("UnitsInStock"), Short)
             Else
                 objProducts.UnitsInStock = Nothing
             End If
 
             If Not DbNull.Value.Equals(dr("UnitsOnOrder")) Then
                 objProducts.UnitsOnOrder = DirectCast(dr("UnitsOnOrder"), Short)
             Else
                 objProducts.UnitsOnOrder = Nothing
             End If
 
             If Not DbNull.Value.Equals(dr("ReorderLevel")) Then
                 objProducts.ReorderLevel = DirectCast(dr("ReorderLevel"), Short)
             Else
                 objProducts.ReorderLevel = Nothing
             End If
             objProducts.Discontinued = DirectCast(dr("Discontinued"), Boolean)
 
             Return objProducts
         End Function
     End Class
End Namespace