Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports NorthwindVB.BusinessObject
 
Namespace 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 objProducts As Products = Nothing
              Dim storedProcName As String = "[dbo].[sp_Products_SelectByPrimaryKey]"
 
              Using connection As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
                  connection.Open()
 
                  Using command As New SqlCommand(storedProcName, connection)
                      command.CommandType = CommandType.StoredProcedure
 
                      ' parameters
                      command.Parameters.AddWithValue("@productID", productID)
 
                      Using da As New SqlDataAdapter(command)
                          Dim dt As New DataTable()
                          da.Fill(dt)
 
                          If dt IsNot Nothing Then
                              If dt.Rows.Count > 0 Then
                                  objProducts = CreateProductsFromDataRowShared(dt.Rows(0))
                              End If
                          End If
                      End Using
                  End Using
              End Using
 
              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].[sp_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].[sp_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].[sp_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 dynamicSqlScript As String = Nothing) As Integer
              Dim recordCount As Integer = 0
 
              Using connection As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
                  connection.Open()
 
                  Using command As New SqlCommand(storedProcName, connection)
                      command.CommandType = CommandType.StoredProcedure
 
                      ' parameters
                      Select Case param
                          Case "supplierID"
                              command.Parameters.AddWithValue("@supplierID", paramValue)
                          Case "categoryID"
                              command.Parameters.AddWithValue("@categoryID", paramValue)
                          Case Else
                              Exit Select
                      End Select
 
                      Using da As New SqlDataAdapter(command)
                          Dim dt As New DataTable()
                          da.Fill(dt)
 
                          If dt IsNot Nothing Then
                              If dt.Rows.Count > 0 Then
                                  recordCount = DirectCast(dt.Rows(0)("RecordCount"), Integer)
                              End If
                          End If
                      End Using
                  End Using
              End Using
 
              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 recordCount As Integer = 0
              Dim storedProcName As String = "[dbo].[sp_Products_GetRecordCountWhereDynamic]"
 
              Using connection As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
                  connection.Open()
 
                  Using command As New SqlCommand(storedProcName, connection)
                      command.CommandType = CommandType.StoredProcedure
 
                      ' search parameters
                      AddSearchCommandParamsShared(command, productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued)
 
                      Using da As New SqlDataAdapter(command)
                          Dim dt As New DataTable()
                          da.Fill(dt)
 
                          If dt IsNot Nothing Then
                              If dt.Rows.Count > 0 Then
                                  recordCount = DirectCast(dt.Rows(0)("RecordCount"), Integer)
                              End If
                          End If
                      End Using
                  End Using
              End Using
 
              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].[sp_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].[sp_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].[sp_Products_SelectSkipAndTakeByCategoryID]", "categoryID", categoryID, True, Nothing, sortByExpression, start, ending)
         End Function
 
         ''' <summary>
         ''' Selects Products records sorted by the sortByExpression and returns records between the start and end based on search parameters
         ''' </summary>
         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  objProductsCol As ProductsCollection = Nothing
              Dim storedProcName As String = "[dbo].[sp_Products_SelectSkipAndTakeWhereDynamic]"
 
              Using connection As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
                  connection.Open()
 
                  Using command As New SqlCommand(storedProcName, connection)
                      command.CommandType = CommandType.StoredProcedure
 
                      ' 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)
 
                      Using da As New SqlDataAdapter(command)
                          Dim dt As New DataTable()
                          da.Fill(dt)
 
                          If dt IsNot Nothing Then
                              If dt.Rows.Count > 0 Then
                                  objProductsCol = New ProductsCollection()
 
                                  For Each dr As DataRow in dt.Rows
                                      Dim objProducts As Products = CreateProductsFromDataRowShared(dr)
                                      objProductsCol.Add(objProducts)
                                  Next
                              End If
                          End If
                      End Using
                  End Using
              End Using
 
              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 objProducts As Products = Nothing
              Dim storedProcName As String = "[dbo].[sp_Products_SelectTotals]"
 
              Using connection As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
                  connection.Open()
 
                  Using command As New SqlCommand(storedProcName, connection)
                      command.CommandType = CommandType.StoredProcedure
 
                      Using da As New SqlDataAdapter(command)
                          Dim dt As New DataTable()
                          da.Fill(dt)
 
                          If dt IsNot Nothing Then
                              If dt.Rows.Count > 0 Then
                                  If Not DbNull.Value.Equals(dt.Rows(0)("UnitPriceTotal")) Then
                                       objProducts.UnitPriceTotal = DirectCast(dt.Rows(0)("UnitPriceTotal"), Decimal)
                                  End If
                              End If
                          End If
                      End Using
                  End Using
              End Using
 
              Return objProducts
         End Function
 
         ''' <summary>
         ''' Selects all Products
         ''' </summary>
         Public Shared Function SelectAll() As ProductsCollection
             Return SelectShared("[dbo].[sp_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 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 ProductsCollection
              Dim objProductsCol As ProductsCollection = Nothing
              Dim storedProcName As String = "[dbo].[sp_Products_SelectAllWhereDynamic]"
 
              Using connection As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
                  connection.Open()
 
                  Using command As New SqlCommand(storedProcName, connection)
                      command.CommandType = CommandType.StoredProcedure
 
                      ' search parameters
                      AddSearchCommandParamsShared(command, productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued)
 
                      Using da As New SqlDataAdapter(command)
                          Dim dt As New DataTable()
                          da.Fill(dt)
 
                          If dt IsNot Nothing Then
                              If dt.Rows.Count > 0 Then
                                  objProductsCol = New ProductsCollection()
 
                                  For Each dr As DataRow In dt.Rows
                                      Dim objProducts As Products = CreateProductsFromDataRowShared(dr)
                                      objProductsCol.Add(objProducts)
                                  Next
                              End If
                          End If
                      End Using
                  End Using
              End Using
 
              Return objProductsCol
         End Function
 
         ''' <summary>
         ''' Selects all Products by Suppliers, related to column SupplierID
         ''' </summary>
         Public Shared Function SelectProductsCollectionBySupplierID(supplierID As Integer) As ProductsCollection
             Return SelectShared("[dbo].[sp_Products_SelectAllBySupplierID]", "supplierID", supplierID)
         End Function
 
         ''' <summary>
         ''' Selects all Products by Categories, related to column CategoryID
         ''' </summary>
         Public Shared Function SelectProductsCollectionByCategoryID(categoryID As Integer) As ProductsCollection
             Return SelectShared("[dbo].[sp_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 objProductsCol As ProductsCollection = Nothing
              Dim storedProcName As String = "[dbo].[sp_Products_SelectDropDownListData]"
 
              Using connection As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
                  connection.Open()
 
                  Using command As New SqlCommand(storedProcName, connection)
                      command.CommandType = CommandType.StoredProcedure
 
                      Using da As New SqlDataAdapter(command)
                          Dim dt As New DataTable()
                          da.Fill(dt)
 
                          If dt IsNot Nothing Then
                              If dt.Rows.Count > 0 Then
                                  objProductsCol = New ProductsCollection()
 
                                  For Each dr As DataRow In dt.Rows
                                      Dim objProducts As Products  = New Products()
                                      objProducts.ProductID = DirectCast(dr("ProductID"), Integer)
                                      objProducts.ProductName = DirectCast(dr("ProductName"), String)
 
                                      objProductsCol.Add(objProducts)
                                  Next
                              End If
                          End If
                      End Using
                  End Using
              End Using
 
              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 dynamicSqlScript As String = Nothing, Optional ByVal sortByExpression As String = Nothing, Optional ByVal start As Integer? = Nothing, Optional ByVal ending As Integer? = Nothing) As ProductsCollection
              Dim objProductsCol As ProductsCollection = Nothing
 
              Using connection As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
                  connection.Open()
 
                  Using command As New SqlCommand(storedProcName, connection)
                      command.CommandType = CommandType.StoredProcedure
 
                      ' 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
 
                      Using da As New SqlDataAdapter(command)
                          Dim dt As New DataTable()
                          da.Fill(dt)
 
                          If dt IsNot Nothing Then
                              If dt.Rows.Count > 0 Then
                                  objProductsCol = New ProductsCollection()
 
                                  For Each dr As DataRow In dt.Rows
                                      Dim objProducts As Products = CreateProductsFromDataRowShared(dr)
                                      objProductsCol.Add(objProducts)
                                  Next
                              End If
                          End If
                      End Using
                  End Using
              End Using
 
              Return objProductsCol
         End Function
 
         ''' <summary>
         ''' Inserts a record
         ''' </summary>
         Public Shared Function Insert(objProducts As Products) As Integer
             Dim storedProcName As String = "[dbo].[sp_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].[sp_Products_Update]"
             InsertUpdate(objProducts, True, storedProcName)
         End Sub
 
         Private Shared Function InsertUpdate(objProducts As Products, isUpdate As Boolean, storedProcName As String) As Integer
              Dim newlyCreatedProductID As Integer = objProducts.ProductID
 
              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
 
              Using connection As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
                  connection.Open()
 
                  Using command As New SqlCommand(storedProcName, connection)
                      command.CommandType = CommandType.StoredProcedure
 
                      ' parameters
                      If isUpdate Then
                          ' for update only
                          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)
 
                      If isUpdate Then
                          command.ExecuteNonQuery()
                      Else
                          newlyCreatedProductID = DirectCast(command.ExecuteScalar(), Integer)
                      End If
                  End Using
              End Using
 
              Return newlyCreatedProductID
         End Function
 
         ''' <summary>
         ''' Deletes a record based on primary key(s)
         ''' </summary>
         Public Shared Sub Delete(ByVal productID As Integer)
              Dim storedProcName As String = "[dbo].[sp_Products_Delete]"
 
              Using connection As New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
                  connection.Open()
 
                  Using command As New SqlCommand(storedProcName, connection)
                      command.CommandType = CommandType.StoredProcedure
 
                      ' parameters
                      command.Parameters.AddWithValue("@productID", productID)
 
                      ' execute
                      command.ExecuteNonQuery()
                  End Using
              End Using
         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