' <fileinfo name="ProductsCollection_Base.vb">
' <copyright>
' All rights reserved.
' </copyright>
' <remarks>
' Do not change this source code manually. Changes to this file may
' cause incorrect behavior and will be lost if the code is regenerated.
' </remarks>
' <generator rewritefile="True" infourl="http://www.SharpPower.com">RapTier</generator>
' </fileinfo>
Option Strict Off
Option Explicit On
Imports System
Imports System.Data
''' <summary>
''' The base class for <see cref="ProductsCollection"/>. Provides methods
''' for common database table operations.
''' </summary>
''' <remarks>
''' Do not change this source code. Update the <see cref="ProductsCollection"/>
''' class if you need to add or change some functionality.
''' </remarks>
Public MustInherit Class ProductsCollection_Base
' Constants
Public Const ProductIDColumnName As String = "ProductID"
Public Const ProductNameColumnName As String = "ProductName"
Public Const SupplierIDColumnName As String = "SupplierID"
Public Const CategoryIDColumnName As String = "CategoryID"
Public Const QuantityPerUnitColumnName As String = "QuantityPerUnit"
Public Const UnitPriceColumnName As String = "UnitPrice"
Public Const UnitsInStockColumnName As String = "UnitsInStock"
Public Const UnitsOnOrderColumnName As String = "UnitsOnOrder"
Public Const ReorderLevelColumnName As String = "ReorderLevel"
Public Const DiscontinuedColumnName As String = "Discontinued"
' Instance fields
Private _db As Northwind
''' <summary>
''' Initializes a new instance of the <see cref="ProductsCollection_Base"/>
''' class with the specified <see cref="Northwind"/>.
''' </summary>
''' <param name="db">The <see cref="Northwind"/> object.</param>
Public Sub New(db As Northwind)
MyBase.New()
_db = db
End Sub
''' <summary>
''' Gets the database object that this table belongs to.
''' </summary>
''' <value>The <see cref="Northwind"/> object.</value>
Protected ReadOnly Property Database As Northwind
Get
Return _db
End Get
End Property
''' <summary>
''' Gets an array of all records from the <c>Products</c> table.
''' </summary>
''' <returns>An array of <see cref="ProductsRow"/> objects.</returns>
Public Overridable Function GetAll() As ProductsRow()
Return MapRecords(CreateGetAllCommand())
End Function
''' <summary>
''' Gets a <see cref="System.Data.DataTable"/> object that
''' includes all records from the <c>Products</c> table.
''' </summary>
''' <returns>A reference to the <see cref="System.Data.DataTable"/> object.</returns>
Public Overridable Function GetAllAsDataTable() As DataTable
Return MapRecordsToDataTable(CreateGetAllCommand())
End Function
''' <summary>
''' Creates and returns an <see cref="System.Data.IDbCommand"/> object that is used
''' to retrieve all records from the <c>Products</c> table.
''' </summary>
''' <returns>A reference to the <see cref="System.Data.IDbCommand"/> object.</returns>
Protected Overridable Function CreateGetAllCommand() As IDbCommand
Return _db.CreateCommand("dbo._Products_GetAll", True)
End Function
''' <summary>
''' Gets the first <see cref="ProductsRow"/> objects that
''' match the search condition.
''' </summary>
''' <param name="whereSql">The SQL search condition. For example:
''' <c>"FirstName='Smith' AND Zip=75038"</c>.</param>
''' <returns>An instance of <see cref="ProductsRow"/> or null reference
''' (Nothing in Visual Basic) if the object was not found.</returns>
Public Function GetRow(whereSql As String) As ProductsRow
Dim totalRecordCount As Integer = -1
Dim rows As ProductsRow() = GetAsArray(whereSql, Nothing, 0, 1, totalRecordCount)
If 0 = rows.Length Then
Return Nothing
End If
Return rows(0)
End Function
''' <summary>
''' Gets an array of <see cref="ProductsRow"/> objects that
''' match the search condition, in the the specified sort order.
''' </summary>
''' <param name="whereSql">The SQL search condition. For example:
''' <c>"FirstName='Smith' AND Zip=75038"</c>.</param>
''' <param name="orderBySql">The column name(s) followed by "ASC" (ascending) or "DESC" (descending).
''' Columns are sorted in ascending order by default. For example: <c>"LastName ASC, FirstName ASC"</c>.</param>
''' <returns>An array of <see cref="ProductsRow"/> objects.</returns>
Public Function GetAsArray(whereSql As String, orderBySql As String) As ProductsRow()
Dim totalRecordCount As Integer = -1
Return GetAsArray(whereSql, orderBySql, 0, Integer.MaxValue, totalRecordCount)
End Function
''' <summary>
''' Gets an array of <see cref="ProductsRow"/> objects that
''' match the search condition, in the the specified sort order.
''' </summary>
''' <param name="whereSql">The SQL search condition. For example:
''' <c>"FirstName='Smith' AND Zip=75038"</c>.</param>
''' <param name="orderBySql">The column name(s) followed by "ASC" (ascending) or "DESC" (descending).
''' Columns are sorted in ascending order by default. For example: <c>"LastName ASC, FirstName ASC"</c>.</param>
''' <param name="startIndex">The index of the first record to return.</param>
''' <param name="length">The number of records to return.</param>
''' <param name="totalRecordCount">A reference parameter that returns the total number
''' of records in the reader object if 0 was passed into the method; otherwise it returns -1.</param>
''' <returns>An array of <see cref="ProductsRow"/> objects.</returns>
Public Overridable Function GetAsArray(whereSql As String, orderBySql As String, _
startIndex As Integer, length As Integer, _
ByRef totalRecordCount As Integer) As ProductsRow()
Dim reader As IDataReader = _db.ExecuteReader(CreateGetCommand(whereSql, orderBySql))
Try
Return MapRecords(reader, startIndex, length, totalRecordCount)
Finally
reader.Dispose()
End Try
End Function
''' <summary>
''' Gets a <see cref="System.Data.DataTable"/> object filled with data that
''' match the search condition, in the the specified sort order.
''' </summary>
''' <param name="whereSql">The SQL search condition. For example: "FirstName='Smith' AND Zip=75038".</param>
''' <param name="orderBySql">The column name(s) followed by "ASC" (ascending) or "DESC" (descending).
''' Columns are sorted in ascending order by default. For example: "LastName ASC, FirstName ASC".</param>
''' <returns>A reference to the <see cref="System.Data.DataTable"/> object.</returns>
Public Function GetAsDataTable(whereSql As String, orderBySql As String) As DataTable
Dim totalRecordCount As Integer = -1
return GetAsDataTable(whereSql, orderBySql, 0, Integer.MaxValue, totalRecordCount)
End Function
''' <summary>
''' Gets a <see cref="System.Data.DataTable"/> object filled with data that
''' match the search condition, in the the specified sort order.
''' </summary>
''' <param name="whereSql">The SQL search condition. For example: "FirstName='Smith' AND Zip=75038".</param>
''' <param name="orderBySql">The column name(s) followed by "ASC" (ascending) or "DESC" (descending).
''' Columns are sorted in ascending order by default. For example: "LastName ASC, FirstName ASC".</param>
''' <param name="startIndex">The index of the first record to return.</param>
''' <param name="length">The number of records to return.</param>
''' <param name="totalRecordCount">A reference parameter that returns the total number
''' of records in the reader object if 0 was passed into the method; otherwise it returns -1.</param>
''' <returns>A reference to the <see cref="System.Data.DataTable"/> object.</returns>
Public Overridable Function GetAsDataTable(whereSql As String, orderBySql As String, _
startIndex As Integer, length As Integer, _
ByRef totalRecordCount As Integer) As DataTable
Dim reader As IDataReader = _db.ExecuteReader(CreateGetCommand(whereSql, orderBySql))
Try
Return MapRecordsToDataTable(reader, startIndex, length, totalRecordCount)
Finally
reader.Dispose()
End Try
End Function
''' <summary>
''' Creates an <see cref="System.Data.IDbCommand"/> object for the specified search criteria.
''' </summary>
''' <param name="whereSql">The SQL search condition. For example: "FirstName='Smith' AND Zip=75038".</param>
''' <param name="orderBySql">The column name(s) followed by "ASC" (ascending) or "DESC" (descending).
''' Columns are sorted in ascending order by default. For example: "LastName ASC, FirstName ASC".</param>
''' <returns>A reference to the <see cref="System.Data.IDbCommand"/> object.</returns>
Protected Overridable Function CreateGetCommand(whereSql As String, _
orderBySql As String) As IDbCommand
Dim sql As String = "SELECT * FROM [dbo].[Products]"
If Not(whereSql Is Nothing) AndAlso 0 < whereSql.Length Then
sql += " WHERE " + whereSql
End If
If Not(orderBySql Is Nothing) AndAlso 0 < orderBySql.Length Then
sql += " ORDER BY " + orderBySql
End If
Return _db.CreateCommand(sql)
End Function
''' <summary>
''' Gets <see cref="ProductsRow"/> by the primary key.
''' </summary>
''' <param name="productID">The <c>ProductID</c> column value.</param>
''' <returns>An instance of <see cref="ProductsRow"/> or null reference
''' (Nothing in Visual Basic) if the object was not found.</returns>
Public Overridable Function GetByPrimaryKey(productID As Integer) As ProductsRow
Dim cmd As IDbCommand = _db.CreateCommand("dbo._Products_GetByPrimaryKey", True)
AddParameter(cmd, "ProductID", productID)
Dim tempArray As ProductsRow() = MapRecords(cmd)
If 0 = tempArray.Length Then
Return Nothing
End If
Return tempArray(0)
End Function
''' <summary>
''' Gets an array of <see cref="ProductsRow"/> objects
''' by the <c>FK_Products_Categories</c> foreign key.
''' </summary>
''' <param name="categoryID">The <c>CategoryID</c> column value.</param>
''' <returns>An array of <see cref="ProductsRow"/> objects.</returns>
Public Function GetByCategoryID(categoryID As Integer) As ProductsRow()
Return GetByCategoryID(categoryID, False)
End Function
''' <summary>
''' Gets an array of <see cref="ProductsRow"/> objects
''' by the <c>FK_Products_Categories</c> foreign key.
''' </summary>
''' <param name="categoryID">The <c>CategoryID</c> column value.</param>
''' <param name="categoryIDNull">true if the method ignores the categoryID
''' parameter value and uses DbNull instead of it; otherwise, false.</param>
''' <returns>An array of <see cref="ProductsRow"/> objects.</returns>
Public Overridable Function GetByCategoryID(categoryID As Integer, categoryIDNull As Boolean) As ProductsRow()
Return MapRecords(CreateGetByCategoryIDCommand(categoryID, categoryIDNull))
End Function
''' <summary>
''' Gets a <see cref="System.Data.DataTable"/> object
''' by the <c>FK_Products_Categories</c> foreign key.
''' </summary>
''' <param name="categoryID">The <c>CategoryID</c> column value.</param>
''' <returns>A reference to the <see cref="System.Data.DataTable"/> object.</returns>
Public Function GetByCategoryIDAsDataTable(categoryID As Integer) As DataTable
Return GetByCategoryIDAsDataTable(categoryID, False)
End Function
''' <summary>
''' Gets a <see cref="System.Data.DataTable"/> object
''' by the <c>FK_Products_Categories</c> foreign key.
''' </summary>
''' <param name="categoryID">The <c>CategoryID</c> column value.</param>
''' <param name="categoryIDNull">true if the method ignores the categoryID
''' parameter value and uses DbNull instead of it; otherwise, false.</param>
''' <returns>A reference to the <see cref="System.Data.DataTable"/> object.</returns>
Public Overridable Function GetByCategoryIDAsDataTable(categoryID As Integer, categoryIDNull As Boolean) As DataTable
Return MapRecordsToDataTable(CreateGetByCategoryIDCommand(categoryID, categoryIDNull))
End Function
''' <summary>
''' Creates an <see cref="System.Data.IDbCommand"/> object that can be used to
''' return records by the <c>FK_Products_Categories</c> foreign key.
''' </summary>
''' <param name="categoryID">The <c>CategoryID</c> column value.</param>
''' <param name="categoryIDNull">true if the method ignores the categoryID
''' parameter value and uses DbNull instead of it; otherwise, false.</param>
''' <returns>A reference to the <see cref="System.Data.IDbCommand"/> object.</returns>
Protected Overridable Function CreateGetByCategoryIDCommand( _
categoryID As Integer, categoryIDNull As Boolean) As IDbCommand
Dim cmd As IDbCommand = _db.CreateCommand("dbo._Products_GetBy_CategoryID", True)
If categoryIDNull Then
AddParameter(cmd, "CategoryID", Nothing)
Else
AddParameter(cmd, "CategoryID", categoryID)
End If
Return cmd
End Function
''' <summary>
''' Gets an array of <see cref="ProductsRow"/> objects
''' by the <c>FK_Products_Suppliers</c> foreign key.
''' </summary>
''' <param name="supplierID">The <c>SupplierID</c> column value.</param>
''' <returns>An array of <see cref="ProductsRow"/> objects.</returns>
Public Function GetBySupplierID(supplierID As Integer) As ProductsRow()
Return GetBySupplierID(supplierID, False)
End Function
''' <summary>
''' Gets an array of <see cref="ProductsRow"/> objects
''' by the <c>FK_Products_Suppliers</c> foreign key.
''' </summary>
''' <param name="supplierID">The <c>SupplierID</c> column value.</param>
''' <param name="supplierIDNull">true if the method ignores the supplierID
''' parameter value and uses DbNull instead of it; otherwise, false.</param>
''' <returns>An array of <see cref="ProductsRow"/> objects.</returns>
Public Overridable Function GetBySupplierID(supplierID As Integer, supplierIDNull As Boolean) As ProductsRow()
Return MapRecords(CreateGetBySupplierIDCommand(supplierID, supplierIDNull))
End Function
''' <summary>
''' Gets a <see cref="System.Data.DataTable"/> object
''' by the <c>FK_Products_Suppliers</c> foreign key.
''' </summary>
''' <param name="supplierID">The <c>SupplierID</c> column value.</param>
''' <returns>A reference to the <see cref="System.Data.DataTable"/> object.</returns>
Public Function GetBySupplierIDAsDataTable(supplierID As Integer) As DataTable
Return GetBySupplierIDAsDataTable(supplierID, False)
End Function
''' <summary>
''' Gets a <see cref="System.Data.DataTable"/> object
''' by the <c>FK_Products_Suppliers</c> foreign key.
''' </summary>
''' <param name="supplierID">The <c>SupplierID</c> column value.</param>
''' <param name="supplierIDNull">true if the method ignores the supplierID
''' parameter value and uses DbNull instead of it; otherwise, false.</param>
''' <returns>A reference to the <see cref="System.Data.DataTable"/> object.</returns>
Public Overridable Function GetBySupplierIDAsDataTable(supplierID As Integer, supplierIDNull As Boolean) As DataTable
Return MapRecordsToDataTable(CreateGetBySupplierIDCommand(supplierID, supplierIDNull))
End Function
''' <summary>
''' Creates an <see cref="System.Data.IDbCommand"/> object that can be used to
''' return records by the <c>FK_Products_Suppliers</c> foreign key.
''' </summary>
''' <param name="supplierID">The <c>SupplierID</c> column value.</param>
''' <param name="supplierIDNull">true if the method ignores the supplierID
''' parameter value and uses DbNull instead of it; otherwise, false.</param>
''' <returns>A reference to the <see cref="System.Data.IDbCommand"/> object.</returns>
Protected Overridable Function CreateGetBySupplierIDCommand( _
supplierID As Integer, supplierIDNull As Boolean) As IDbCommand
Dim cmd As IDbCommand = _db.CreateCommand("dbo._Products_GetBy_SupplierID", True)
If supplierIDNull Then
AddParameter(cmd, "SupplierID", Nothing)
Else
AddParameter(cmd, "SupplierID", supplierID)
End If
Return cmd
End Function
''' <summary>
''' Adds a new record into the <c>Products</c> table.
''' </summary>
''' <param name="value">The <see cref="ProductsRow"/> object to be inserted.</param>
Public Overridable Sub Insert(value As ProductsRow)
Dim cmd As IDbCommand = _db.CreateCommand("dbo._Products_Insert", true)
AddParameter(cmd, "ProductName", value.ProductName)
If value.IsSupplierIDNull Then
AddParameter(cmd, "SupplierID", DBNull.Value)
Else
AddParameter(cmd, "SupplierID", value.SupplierID)
End If
If value.IsCategoryIDNull Then
AddParameter(cmd, "CategoryID", DBNull.Value)
Else
AddParameter(cmd, "CategoryID", value.CategoryID)
End If
AddParameter(cmd, "QuantityPerUnit", value.QuantityPerUnit)
If value.IsUnitPriceNull Then
AddParameter(cmd, "UnitPrice", DBNull.Value)
Else
AddParameter(cmd, "UnitPrice", value.UnitPrice)
End If
If value.IsUnitsInStockNull Then
AddParameter(cmd, "UnitsInStock", DBNull.Value)
Else
AddParameter(cmd, "UnitsInStock", value.UnitsInStock)
End If
If value.IsUnitsOnOrderNull Then
AddParameter(cmd, "UnitsOnOrder", DBNull.Value)
Else
AddParameter(cmd, "UnitsOnOrder", value.UnitsOnOrder)
End If
If value.IsReorderLevelNull Then
AddParameter(cmd, "ReorderLevel", DBNull.Value)
Else
AddParameter(cmd, "ReorderLevel", value.ReorderLevel)
End If
AddParameter(cmd, "Discontinued", value.Discontinued)
value.ProductID = Convert.ToInt32(cmd.ExecuteScalar())
End Sub
''' <summary>
''' Updates a record in the <c>Products</c> table.
''' </summary>
''' <param name="value">The <see cref="ProductsRow"/>
''' object used to update the table record.</param>
''' <returns>true if the record was updated; otherwise, false.</returns>
Public Overridable Function Update(value As ProductsRow) As Boolean
Dim cmd As IDbCommand = _db.CreateCommand("dbo._Products_Update", true)
AddParameter(cmd, "ProductName", value.ProductName)
If value.IsSupplierIDNull Then
AddParameter(cmd, "SupplierID", DBNull.Value)
Else
AddParameter(cmd, "SupplierID", value.SupplierID)
End If
If value.IsCategoryIDNull Then
AddParameter(cmd, "CategoryID", DBNull.Value)
Else
AddParameter(cmd, "CategoryID", value.CategoryID)
End If
AddParameter(cmd, "QuantityPerUnit", value.QuantityPerUnit)
If value.IsUnitPriceNull Then
AddParameter(cmd, "UnitPrice", DBNull.Value)
Else
AddParameter(cmd, "UnitPrice", value.UnitPrice)
End If
If value.IsUnitsInStockNull Then
AddParameter(cmd, "UnitsInStock", DBNull.Value)
Else
AddParameter(cmd, "UnitsInStock", value.UnitsInStock)
End If
If value.IsUnitsOnOrderNull Then
AddParameter(cmd, "UnitsOnOrder", DBNull.Value)
Else
AddParameter(cmd, "UnitsOnOrder", value.UnitsOnOrder)
End If
If value.IsReorderLevelNull Then
AddParameter(cmd, "ReorderLevel", DBNull.Value)
Else
AddParameter(cmd, "ReorderLevel", value.ReorderLevel)
End If
AddParameter(cmd, "Discontinued", value.Discontinued)
AddParameter(cmd, "ProductID", value.ProductID)
Return 0 <> cmd.ExecuteNonQuery()
End Function
''' <summary>
''' Updates the <c>Products</c> table and calls the <c>AcceptChanges</c> method
''' on the changed DataRow objects.
''' </summary>
''' <param name="table">The <see cref="System.Data.DataTable"/> used to update the data source.</param>
Public Sub Update(table As DataTable)
Update(table, true)
End Sub
''' <summary>
''' Updates the <c>Products</c> table. Pass <c>false</c> as the <c>acceptChanges</c>
''' argument when your code calls this method in an ADO.NET transaction context. Note that in
''' this case, after you call the Update method you need call either <c>AcceptChanges</c>
''' or <c>RejectChanges</c> method on the DataTable object.
''' <code>
''' MyDb db = new MyDb();
''' try
''' {
''' db.BeginTransaction();
''' db.MyCollection.Update(myDataTable, false);
''' db.CommitTransaction();
''' myDataTable.AcceptChanges();
''' }
''' catch(Exception)
''' {
''' db.RollbackTransaction();
''' myDataTable.RejectChanges();
''' }
''' </code>
''' </summary>
''' <param name="table">The <see cref="System.Data.DataTable"/> used to update the data source.</param>
''' <param name="acceptChanges">Specifies whether this method calls the <c>AcceptChanges</c>
''' method on the changed DataRow objects.</param>
Public Overridable Sub Update(table As DataTable, acceptChanges As Boolean)
Dim rows As DataRowCollection = table.Rows
Dim i As Integer
For i = rows.Count - 1 To 0 Step -1
Dim row As DataRow = rows(i)
Select row.RowState
Case DataRowState.Added
Insert(MapRow(row))
If acceptChanges Then
row.AcceptChanges()
End If
Case DataRowState.Deleted
row.RejectChanges()
Try
DeleteByPrimaryKey(CType(row("ProductID"), Integer))
Finally
row.Delete()
End Try
If acceptChanges Then
row.AcceptChanges()
End If
Case DataRowState.Modified
Update(MapRow(row))
If acceptChanges Then
row.AcceptChanges()
End If
End Select
Next
End Sub
''' <summary>
''' Deletes the specified object from the <c>Products</c> table.
''' </summary>
''' <param name="value">The <see cref="ProductsRow"/> object to delete.</param>
''' <returns>true if the record was deleted; otherwise, false.</returns>
Public Function Delete(value As ProductsRow) As Boolean
Return DeleteByPrimaryKey(value.ProductID)
End Function
''' <summary>
''' Deletes a record from the <c>Products</c> table using
''' the specified primary key.
''' </summary>
''' <param name="productID">The <c>ProductID</c> column value.</param>
''' <returns>true if the record was deleted; otherwise, false.</returns>
Public Overridable Function DeleteByPrimaryKey(productID As Integer) As Boolean
Dim cmd As IDbCommand = _db.CreateCommand("dbo._Products_DeleteByPrimaryKey", true)
AddParameter(cmd, "ProductID", productID)
Return 0 < cmd.ExecuteNonQuery()
End Function
''' <summary>
''' Deletes records from the <c>Products</c> table using the
''' <c>FK_Products_Categories</c> foreign key.
''' </summary>
''' <param name="categoryID">The <c>CategoryID</c> column value.</param>
''' <returns>The number of records deleted from the table.</returns>
Public Function DeleteByCategoryID(categoryID As Integer) As Integer
Return DeleteByCategoryID(categoryID, False)
End Function
''' <summary>
''' Deletes records from the <c>Products</c> table using the
''' <c>FK_Products_Categories</c> foreign key.
''' </summary>
''' <param name="categoryID">The <c>CategoryID</c> column value.</param>
''' <param name="categoryIDNull">true if the method ignores the categoryID
''' parameter value and uses DbNull instead of it; otherwise, false.</param>
''' <returns>The number of records deleted from the table.</returns>
Public Function DeleteByCategoryID(categoryID As Integer, categoryIDNull As Boolean) As Integer
Return CreateDeleteByCategoryIDCommand(categoryID, categoryIDNull).ExecuteNonQuery()
End Function
''' <summary>
''' Creates an <see cref="System.Data.IDbCommand"/> object that can be used to
''' delete records using the <c>FK_Products_Categories</c> foreign key.
''' </summary>
''' <param name="categoryID">The <c>CategoryID</c> column value.</param>
''' <param name="categoryIDNull">true if the method ignores the categoryID
''' parameter value and uses DbNull instead of it; otherwise, false.</param>
''' <returns>A reference to the <see cref="System.Data.IDbCommand"/> object.</returns>
Public Overridable Function CreateDeleteByCategoryIDCommand(categoryID As Integer, categoryIDNull As Boolean) As IDbCommand
Dim cmd As IDbCommand = _db.CreateCommand("dbo._Products_DeleteBy_CategoryID", True)
If categoryIDNull Then
AddParameter(cmd, "CategoryID", Nothing)
Else
AddParameter(cmd, "CategoryID", categoryID)
End If
Return cmd
End Function
''' <summary>
''' Deletes records from the <c>Products</c> table using the
''' <c>FK_Products_Suppliers</c> foreign key.
''' </summary>
''' <param name="supplierID">The <c>SupplierID</c> column value.</param>
''' <returns>The number of records deleted from the table.</returns>
Public Function DeleteBySupplierID(supplierID As Integer) As Integer
Return DeleteBySupplierID(supplierID, False)
End Function
''' <summary>
''' Deletes records from the <c>Products</c> table using the
''' <c>FK_Products_Suppliers</c> foreign key.
''' </summary>
''' <param name="supplierID">The <c>SupplierID</c> column value.</param>
''' <param name="supplierIDNull">true if the method ignores the supplierID
''' parameter value and uses DbNull instead of it; otherwise, false.</param>
''' <returns>The number of records deleted from the table.</returns>
Public Function DeleteBySupplierID(supplierID As Integer, supplierIDNull As Boolean) As Integer
Return CreateDeleteBySupplierIDCommand(supplierID, supplierIDNull).ExecuteNonQuery()
End Function
''' <summary>
''' Creates an <see cref="System.Data.IDbCommand"/> object that can be used to
''' delete records using the <c>FK_Products_Suppliers</c> foreign key.
''' </summary>
''' <param name="supplierID">The <c>SupplierID</c> column value.</param>
''' <param name="supplierIDNull">true if the method ignores the supplierID
''' parameter value and uses DbNull instead of it; otherwise, false.</param>
''' <returns>A reference to the <see cref="System.Data.IDbCommand"/> object.</returns>
Public Overridable Function CreateDeleteBySupplierIDCommand(supplierID As Integer, supplierIDNull As Boolean) As IDbCommand
Dim cmd As IDbCommand = _db.CreateCommand("dbo._Products_DeleteBy_SupplierID", True)
If supplierIDNull Then
AddParameter(cmd, "SupplierID", Nothing)
Else
AddParameter(cmd, "SupplierID", supplierID)
End If
Return cmd
End Function
''' <summary>
''' Deletes <c>Products</c> records that match the specified criteria.
''' </summary>
''' <param name="whereSql">The SQL search condition.
''' For example: <c>"FirstName='Smith' AND Zip=75038"</c>.</param>
''' <returns>The number of deleted records.</returns>
Public Function Delete(whereSql As String) As Integer
Return CreateDeleteCommand(whereSql).ExecuteNonQuery()
End Function
''' <summary>
''' Creates an <see cref="System.Data.IDbCommand"/> object that can be used
''' to delete <c>Products</c> records that match the specified criteria.
''' </summary>
''' <param name="whereSql">The SQL search condition.
''' For example: <c>"FirstName='Smith' AND Zip=75038"</c>.</param>
''' <returns>A reference to the <see cref="System.Data.IDbCommand"/> object.</returns>
Protected Overridable Function CreateDeleteCommand(whereSql As String) As IDbCommand
Dim sql As String = "DELETE FROM [dbo].[Products]"
If Not(whereSql Is Nothing) AndAlso 0 < whereSql.Length Then
sql += " WHERE " + whereSql
End If
Return _db.CreateCommand(sql)
End Function
''' <summary>
''' Deletes all records from the <c>Products</c> table.
''' </summary>
''' <returns>The number of deleted records.</returns>
Public Function DeleteAll() As Integer
Return _db.CreateCommand("dbo._Products_DeleteAll", true).ExecuteNonQuery()
End Function
''' <summary>
''' Reads data using the specified command and returns
''' an array of mapped objects.
''' </summary>
''' <param name="command">The <see cref="System.Data.IDbCommand"/> object.</param>
''' <returns>An array of <see cref="ProductsRow"/> objects.</returns>
Protected Function MapRecords(command As IDbCommand) As ProductsRow()
Dim reader As IDataReader = _db.ExecuteReader(command)
Try
Return MapRecords(reader)
Finally
reader.Dispose()
End Try
End Function
''' <summary>
''' Reads data from the provided data reader and returns
''' an array of mapped objects.
''' </summary>
''' <param name="reader">The <see cref="System.Data.IDataReader"/> object to read data from the table.</param>
''' <returns>An array of <see cref="ProductsRow"/> objects.</returns>
Protected Function MapRecords(reader As IDataReader) As ProductsRow()
Dim totalRecordCount As Integer = -1
Return MapRecords(reader, 0, Integer.MaxValue, totalRecordCount)
End Function
''' <summary>
''' Reads data from the provided data reader and returns
''' an array of mapped objects.
''' </summary>
''' <param name="reader">The <see cref="System.Data.IDataReader"/> object to read data from the table.</param>
''' <param name="startIndex">The index of the first record to map.</param>
''' <param name="length">The number of records to map.</param>
''' <param name="totalRecordCount">A reference parameter that returns the total number
''' of records in the reader object if 0 was passed into the method; otherwise it returns -1.</param>
''' <returns>An array of <see cref="ProductsRow"/> objects.</returns>
Protected Overridable Function MapRecords(reader As IDataReader, startIndex As Integer, _
length As Integer, ByRef totalRecordCount As Integer) As ProductsRow()
If 0 > startIndex Then
Throw New ArgumentOutOfRangeException("startIndex", startIndex, "StartIndex cannot be less than zero.")
End If
If 0 > length Then
Throw New ArgumentOutOfRangeException("length", length, "Length cannot be less than zero.")
End If
Dim productIDColumnIndex As Integer = reader.GetOrdinal("ProductID")
Dim productNameColumnIndex As Integer = reader.GetOrdinal("ProductName")
Dim supplierIDColumnIndex As Integer = reader.GetOrdinal("SupplierID")
Dim categoryIDColumnIndex As Integer = reader.GetOrdinal("CategoryID")
Dim quantityPerUnitColumnIndex As Integer = reader.GetOrdinal("QuantityPerUnit")
Dim unitPriceColumnIndex As Integer = reader.GetOrdinal("UnitPrice")
Dim unitsInStockColumnIndex As Integer = reader.GetOrdinal("UnitsInStock")
Dim unitsOnOrderColumnIndex As Integer = reader.GetOrdinal("UnitsOnOrder")
Dim reorderLevelColumnIndex As Integer = reader.GetOrdinal("ReorderLevel")
Dim discontinuedColumnIndex As Integer = reader.GetOrdinal("Discontinued")
Dim recordList As System.Collections.ArrayList = New System.Collections.ArrayList()
Dim ri As Integer = -startIndex
While(reader.Read())
ri = ri + 1
If ri > 0 AND ri <= length Then
Dim record As ProductsRow = New ProductsRow()
recordList.Add(record)
record.ProductID = Convert.ToInt32(reader.GetValue(productIDColumnIndex))
record.ProductName = Convert.ToString(reader.GetValue(productNameColumnIndex))
If Not reader.IsDBNull(supplierIDColumnIndex) Then
record.SupplierID = Convert.ToInt32(reader.GetValue(supplierIDColumnIndex))
End If
If Not reader.IsDBNull(categoryIDColumnIndex) Then
record.CategoryID = Convert.ToInt32(reader.GetValue(categoryIDColumnIndex))
End If
If Not reader.IsDBNull(quantityPerUnitColumnIndex) Then
record.QuantityPerUnit = Convert.ToString(reader.GetValue(quantityPerUnitColumnIndex))
End If
If Not reader.IsDBNull(unitPriceColumnIndex) Then
record.UnitPrice = Convert.ToDecimal(reader.GetValue(unitPriceColumnIndex))
End If
If Not reader.IsDBNull(unitsInStockColumnIndex) Then
record.UnitsInStock = Convert.ToInt16(reader.GetValue(unitsInStockColumnIndex))
End If
If Not reader.IsDBNull(unitsOnOrderColumnIndex) Then
record.UnitsOnOrder = Convert.ToInt16(reader.GetValue(unitsOnOrderColumnIndex))
End If
If Not reader.IsDBNull(reorderLevelColumnIndex) Then
record.ReorderLevel = Convert.ToInt16(reader.GetValue(reorderLevelColumnIndex))
End If
record.Discontinued = Convert.ToBoolean(reader.GetValue(discontinuedColumnIndex))
If ri = length AND 0 <> totalRecordCount Then
Exit While
End If
End If
End While
If 0 = totalRecordCount
totalRecordCount = ri + startIndex
Else
totalRecordCount = -1
End If
Return CType(recordList.ToArray(GetType(ProductsRow)), ProductsRow())
End Function
''' <summary>
''' Reads data using the specified command and returns
''' a filled <see cref="System.Data.DataTable"/> object.
''' </summary>
''' <param name="command">The <see cref="System.Data.IDbCommand"/> object.</param>
''' <returns>A reference to the <see cref="System.Data.DataTable"/> object.</returns>
Protected Function MapRecordsToDataTable(command As IDbCommand) As DataTable
Dim reader As IDataReader = _db.ExecuteReader(command)
Try
Return MapRecordsToDataTable(reader)
Finally
reader.Dispose()
End Try
End Function
''' <summary>
''' Reads data from the provided data reader and returns
''' a filled <see cref="System.Data.DataTable"/> object.
''' </summary>
''' <param name="reader">The <see cref="System.Data.IDataReader"/> object to read data from the table.</param>
''' <returns>A reference to the <see cref="System.Data.DataTable"/> object.</returns>
Protected Function MapRecordsToDataTable(reader As IDataReader) As DataTable
Dim totalRecordCount As Integer = 0
Return MapRecordsToDataTable(reader, 0, Integer.MaxValue, totalRecordCount)
End Function
''' <summary>
''' Reads data from the provided data reader and returns
''' a filled <see cref="System.Data.DataTable"/> object.
''' </summary>
''' <param name="reader">The <see cref="System.Data.IDataReader"/> object to read data from the table.</param>
''' <param name="startIndex">The index of the first record to read.</param>
''' <param name="length">The number of records to read.</param>
''' <param name="totalRecordCount">A reference parameter that returns the total number
''' of records in the reader object if 0 was passed into the method; otherwise it returns -1.</param>
''' <returns>A reference to the <see cref="System.Data.DataTable"/> object.</returns>
Protected Overridable Function MapRecordsToDataTable(reader As IDataReader, startIndex As Integer, _
length As Integer, ByRef totalRecordCount As Integer) As DataTable
If 0 > startIndex Then
Throw New ArgumentOutOfRangeException("startIndex", startIndex, "StartIndex cannot be less than zero.")
End If
If 0 > length Then
Throw New ArgumentOutOfRangeException("length", length, "Length cannot be less than zero.")
End If
Dim columnCount As Integer = reader.FieldCount
Dim ri As Integer = -startIndex
Dim dataTable As DataTable = CreateDataTable()
dataTable.BeginLoadData()
Dim values(columnCount - 1) As Object
While(reader.Read())
ri = ri + 1
If ri > 0 AND ri <= length Then
reader.GetValues(values)
dataTable.LoadDataRow(values, True)
If ri = length AND 0 <> totalRecordCount Then
Exit While
End If
End If
End While
dataTable.EndLoadData()
If 0 = totalRecordCount
totalRecordCount = ri + startIndex
Else
totalRecordCount = -1
End If
Return dataTable
End Function
''' <summary>
''' Converts <see cref="System.Data.DataRow"/> to <see cref="ProductsRow"/>.
''' </summary>
''' <param name="row">The <see cref="System.Data.DataRow"/> object to be mapped.</param>
''' <returns>A reference to the <see cref="ProductsRow"/> object.</returns>
Protected Overridable Function MapRow(row As DataRow) As ProductsRow
Dim mappedObject As ProductsRow = New ProductsRow()
Dim dataTable As DataTable = row.Table