' <fileinfo name="AlphabeticallistofproductsCollection_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="AlphabeticallistofproductsCollection"/>. Provides methods
''' for common database view operations.
''' </summary>
''' <remarks>
''' Do not change this source code. Update the <see cref="AlphabeticallistofproductsCollection"/>
''' class if you need to add or change some functionality.
''' </remarks>
Public MustInherit Class AlphabeticallistofproductsCollection_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"
Public Const CategoryNameColumnName As String = "CategoryName"
' Instance fields
Private _db As Northwind
''' <summary>
''' Initializes a new instance of the <see cref="AlphabeticallistofproductsCollection_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 view 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>Alphabetical list of products</c> view.
''' </summary>
''' <returns>An array of <see cref="AlphabeticallistofproductsRow"/> objects.</returns>
Public Overridable Function GetAll() As AlphabeticallistofproductsRow()
Return MapRecords(CreateGetAllCommand())
End Function
''' <summary>
''' Gets a <see cref="System.Data.DataTable"/> object that
''' includes all records from the <c>Alphabetical list of products</c> view.
''' </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>Alphabetical list of products</c> view.
''' </summary>
''' <returns>A reference to the <see cref="System.Data.IDbCommand"/> object.</returns>
Protected Overridable Function CreateGetAllCommand() As IDbCommand
Return _db.CreateCommand("dbo._Alphabeticallistofproducts_GetAll", True)
End Function
''' <summary>
''' Gets the first <see cref="AlphabeticallistofproductsRow"/> 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="AlphabeticallistofproductsRow"/> or null reference
''' (Nothing in Visual Basic) if the object was not found.</returns>
Public Function GetRow(whereSql As String) As AlphabeticallistofproductsRow
Dim totalRecordCount As Integer = -1
Dim rows As AlphabeticallistofproductsRow() = 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="AlphabeticallistofproductsRow"/> 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="AlphabeticallistofproductsRow"/> objects.</returns>
Public Function GetAsArray(whereSql As String, orderBySql As String) As AlphabeticallistofproductsRow()
Dim totalRecordCount As Integer = -1
Return GetAsArray(whereSql, orderBySql, 0, Integer.MaxValue, totalRecordCount)
End Function
''' <summary>
''' Gets an array of <see cref="AlphabeticallistofproductsRow"/> 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="AlphabeticallistofproductsRow"/> objects.</returns>
Public Overridable Function GetAsArray(whereSql As String, orderBySql As String, _
startIndex As Integer, length As Integer, _
ByRef totalRecordCount As Integer) As AlphabeticallistofproductsRow()
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].[Alphabetical list of 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>
''' 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="AlphabeticallistofproductsRow"/> objects.</returns>
Protected Function MapRecords(command As IDbCommand) As AlphabeticallistofproductsRow()
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 view.</param>
''' <returns>An array of <see cref="AlphabeticallistofproductsRow"/> objects.</returns>
Protected Function MapRecords(reader As IDataReader) As AlphabeticallistofproductsRow()
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 view.</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="AlphabeticallistofproductsRow"/> objects.</returns>
Protected Overridable Function MapRecords(reader As IDataReader, startIndex As Integer, _
length As Integer, ByRef totalRecordCount As Integer) As AlphabeticallistofproductsRow()
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 categoryNameColumnIndex As Integer = reader.GetOrdinal("CategoryName")
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 AlphabeticallistofproductsRow = New AlphabeticallistofproductsRow()
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))
record.CategoryName = Convert.ToString(reader.GetValue(categoryNameColumnIndex))
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(AlphabeticallistofproductsRow)), AlphabeticallistofproductsRow())
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 view.</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 view.</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="AlphabeticallistofproductsRow"/>.
''' </summary>
''' <param name="row">The <see cref="System.Data.DataRow"/> object to be mapped.</param>
''' <returns>A reference to the <see cref="AlphabeticallistofproductsRow"/> object.</returns>
Protected Overridable Function MapRow(row As DataRow) As AlphabeticallistofproductsRow
Dim mappedObject As AlphabeticallistofproductsRow = New AlphabeticallistofproductsRow()
Dim dataTable As DataTable = row.Table
Dim dataColumn As DataColumn
' Column "ProductID"
dataColumn = dataTable.Columns("ProductID")
If Not row.IsNull(dataColumn) Then
mappedObject.ProductID = CType(row(dataColumn), Integer)
End If
' Column "ProductName"
dataColumn = dataTable.Columns("ProductName")
If Not row.IsNull(dataColumn) Then
mappedObject.ProductName = CType(row(dataColumn), String)
End If
' Column "SupplierID"
dataColumn = dataTable.Columns("SupplierID")
If Not row.IsNull(dataColumn) Then
mappedObject.SupplierID = CType(row(dataColumn), Integer)
End If
' Column "CategoryID"
dataColumn = dataTable.Columns("CategoryID")
If Not row.IsNull(dataColumn) Then
mappedObject.CategoryID = CType(row(dataColumn), Integer)
End If
' Column "QuantityPerUnit"
dataColumn = dataTable.Columns("QuantityPerUnit")
If Not row.IsNull(dataColumn) Then
mappedObject.QuantityPerUnit = CType(row(dataColumn), String)
End If
' Column "UnitPrice"
dataColumn = dataTable.Columns("UnitPrice")
If Not row.IsNull(dataColumn) Then
mappedObject.UnitPrice = CType(row(dataColumn), Decimal)
End If
' Column "UnitsInStock"
dataColumn = dataTable.Columns("UnitsInStock")
If Not row.IsNull(dataColumn) Then
mappedObject.UnitsInStock = CType(row(dataColumn), Short)
End If
' Column "UnitsOnOrder"
dataColumn = dataTable.Columns("UnitsOnOrder")
If Not row.IsNull(dataColumn) Then
mappedObject.UnitsOnOrder = CType(row(dataColumn), Short)
End If
' Column "ReorderLevel"
dataColumn = dataTable.Columns("ReorderLevel")
If Not row.IsNull(dataColumn) Then
mappedObject.ReorderLevel = CType(row(dataColumn), Short)
End If
' Column "Discontinued"
dataColumn = dataTable.Columns("Discontinued")
If Not row.IsNull(dataColumn) Then
mappedObject.Discontinued = CType(row(dataColumn), Boolean)
End If
' Column "CategoryName"
dataColumn = dataTable.Columns("CategoryName")
If Not row.IsNull(dataColumn) Then
mappedObject.CategoryName = CType(row(dataColumn), String)
End If
Return mappedObject
End Function
''' <summary>
''' Creates a <see cref="System.Data.DataTable"/> object for
''' the <c>Alphabetical list of products</c> view.
''' </summary>
''' <returns>A reference to the <see cref="System.Data.DataTable"/> object.</returns>
Protected Overridable Function CreateDataTable() As DataTable
Dim dataTable As DataTable = new DataTable()
dataTable.TableName = "Alphabeticallistofproducts"
Dim dataColumn As DataColumn
' Create the "ProductID" column
dataColumn = dataTable.Columns.Add("ProductID", GetType(Integer))
dataColumn.AllowDBNull = False
dataColumn.ReadOnly = True
' Create the "ProductName" column
dataColumn = dataTable.Columns.Add("ProductName", GetType(String))
dataColumn.MaxLength = 40
dataColumn.AllowDBNull = False
dataColumn.ReadOnly = True
' Create the "SupplierID" column
dataColumn = dataTable.Columns.Add("SupplierID", GetType(Integer))
dataColumn.ReadOnly = True
' Create the "CategoryID" column
dataColumn = dataTable.Columns.Add("CategoryID", GetType(Integer))
dataColumn.ReadOnly = True
' Create the "QuantityPerUnit" column
dataColumn = dataTable.Columns.Add("QuantityPerUnit", GetType(String))
dataColumn.MaxLength = 20
dataColumn.ReadOnly = True
' Create the "UnitPrice" column
dataColumn = dataTable.Columns.Add("UnitPrice", GetType(Decimal))
dataColumn.ReadOnly = True
' Create the "UnitsInStock" column
dataColumn = dataTable.Columns.Add("UnitsInStock", GetType(Short))
dataColumn.ReadOnly = True
' Create the "UnitsOnOrder" column
dataColumn = dataTable.Columns.Add("UnitsOnOrder", GetType(Short))
dataColumn.ReadOnly = True
' Create the "ReorderLevel" column
dataColumn = dataTable.Columns.Add("ReorderLevel", GetType(Short))
dataColumn.ReadOnly = True
' Create the "Discontinued" column
dataColumn = dataTable.Columns.Add("Discontinued", GetType(Boolean))
dataColumn.AllowDBNull = False
dataColumn.ReadOnly = True
' Create the "CategoryName" column
dataColumn = dataTable.Columns.Add("CategoryName", GetType(String))
dataColumn.MaxLength = 15
dataColumn.AllowDBNull = False
dataColumn.ReadOnly = True
Return dataTable
End Function
''' <summary>
''' Adds a new parameter to the specified command.
''' </summary>
''' <param name="cmd">The <see cref="System.Data.IDbCommand"/> object to add the parameter to.</param>
''' <param name="paramName">The name of the parameter.</param>
''' <param name="value">The value of the parameter.</param>
''' <returns>A reference to the added parameter.</returns>
Protected Overridable Function AddParameter(cmd As IDbCommand, paramName As String, _
value As Object) As IDbDataParameter
Dim parameter As IDbDataParameter
Select paramName
Case "ProductID"
parameter = _db.AddParameter(cmd, paramName, DbType.Int32, value)
Case "ProductName"
parameter = _db.AddParameter(cmd, paramName, DbType.String, value)
Case "SupplierID"
parameter = _db.AddParameter(cmd, paramName, DbType.Int32, value)
Case "CategoryID"
parameter = _db.AddParameter(cmd, paramName, DbType.Int32, value)
Case "QuantityPerUnit"
parameter = _db.AddParameter(cmd, paramName, DbType.String, value)
Case "UnitPrice"
parameter = _db.AddParameter(cmd, paramName, DbType.Currency, value)
Case "UnitsInStock"
parameter = _db.AddParameter(cmd, paramName, DbType.Int16, value)
Case "UnitsOnOrder"
parameter = _db.AddParameter(cmd, paramName, DbType.Int16, value)
Case "ReorderLevel"
parameter = _db.AddParameter(cmd, paramName, DbType.Int16, value)
Case "Discontinued"
parameter = _db.AddParameter(cmd, paramName, DbType.Boolean, value)
Case "CategoryName"
parameter = _db.AddParameter(cmd, paramName, DbType.String, value)
Case Else
Throw New ArgumentException("Unknown parameter name (" + paramName + ").")
End Select
Return parameter
End Function
End Class