Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Northwind.BusinessObject
Namespace Northwind.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].[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>
''' Selects all Products
''' </summary>
Public Shared Function SelectAll() As ProductsCollection
Return SelectShared("[dbo].[Products_SelectAll]", String.Empty, Nothing)
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].[Products_SelectAllBySuppliers]", "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].[Products_SelectAllByCategories]", "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].[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) As ProductsCollection
Dim connection As SqlConnection = Dbase.GetConnection()
Dim command As SqlCommand = Dbase.GetCommand(storedProcName, connection)
' 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()
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 dr("SupplierID").Equals(System.DBNull.Value) Then
objProducts.SupplierID = DirectCast(dr("SupplierID"), Integer)
Else
objProducts.SupplierID = Nothing
End If
If Not dr("CategoryID").Equals(System.DBNull.Value) Then
objProducts.CategoryID = DirectCast(dr("CategoryID"), Integer)
Else
objProducts.CategoryID = Nothing
End If
If Not dr("QuantityPerUnit").Equals(System.DBNull.Value) Then
objProducts.QuantityPerUnit = dr("QuantityPerUnit").ToString()
Else
objProducts.QuantityPerUnit = Nothing
End If
If Not dr("UnitPrice").Equals(System.DBNull.Value) Then
objProducts.UnitPrice = DirectCast(dr("UnitPrice"), Decimal)
Else
objProducts.UnitPrice = Nothing
End If
If Not dr("UnitsInStock").Equals(System.DBNull.Value) Then
objProducts.UnitsInStock = DirectCast(dr("UnitsInStock"), Short)
Else
objProducts.UnitsInStock = Nothing
End If
If Not dr("UnitsOnOrder").Equals(System.DBNull.Value) Then
objProducts.UnitsOnOrder = DirectCast(dr("UnitsOnOrder"), Short)
Else
objProducts.UnitsOnOrder = Nothing
End If
If Not dr("ReorderLevel").Equals(System.DBNull.Value) 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>
''' Inserts a record
''' </summary>
Public Shared Function Insert(objProducts As Products) As Integer
Dim storedProcName As String = "[dbo].[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].[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].[Products_Delete]", connection)
command.Parameters.AddWithValue("@productID", productID)
' execute stored proc
command.ExecuteNonQuery()
command.Dispose()
connection.Close()
connection.Dispose()
End Sub
End Class
End Namespace
Submit an article or tip