' <fileinfo name="ProductsbyCategoryCollection_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="ProductsbyCategoryCollection"/>. Provides methods 
''' for common database view operations. 
''' </summary>
''' <remarks>
''' Do not change this source code. Update the <see cref="ProductsbyCategoryCollection"/>
''' class if you need to add or change some functionality.
''' </remarks>
Public MustInherit Class ProductsbyCategoryCollection_Base
    ' Constants
    Public Const CategoryNameColumnName As String = "CategoryName"
    Public Const ProductNameColumnName As String = "ProductName"
    Public Const QuantityPerUnitColumnName As String = "QuantityPerUnit"
    Public Const UnitsInStockColumnName As String = "UnitsInStock"
    Public Const DiscontinuedColumnName As String = "Discontinued"

    ' Instance fields
    Private _db As Northwind

    ''' <summary>
    ''' Initializes a new instance of the <see cref="ProductsbyCategoryCollection_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>Products by Category</c> view.
    ''' </summary>
    ''' <returns>An array of <see cref="ProductsbyCategoryRow"/> objects.</returns>
    Public Overridable Function GetAll() As ProductsbyCategoryRow()
        Return MapRecords(CreateGetAllCommand())
    End Function

    ''' <summary>
    ''' Gets a <see cref="System.Data.DataTable"/> object that 
    ''' includes all records from the <c>Products by Category</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>Products by Category</c> view.
    ''' </summary>
    ''' <returns>A reference to the <see cref="System.Data.IDbCommand"/> object.</returns>
    Protected Overridable Function CreateGetAllCommand() As IDbCommand
        Return CreateGetCommand(Nothing, Nothing)
    End Function

    ''' <summary>
    ''' Gets the first <see cref="ProductsbyCategoryRow"/> 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="ProductsbyCategoryRow"/> or null reference 
    ''' (Nothing in Visual Basic) if the object was not found.</returns>
    Public Function GetRow(whereSql As String) As ProductsbyCategoryRow
        Dim totalRecordCount As Integer = -1
        Dim rows As ProductsbyCategoryRow() = 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="ProductsbyCategoryRow"/> 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="ProductsbyCategoryRow"/> objects.</returns>
    Public Function GetAsArray(whereSql As String, orderBySql As String) As ProductsbyCategoryRow()
        Dim totalRecordCount As Integer = -1
        Return GetAsArray(whereSql, orderBySql, 0, Integer.MaxValue, totalRecordCount)
    End Function

    ''' <summary>
    ''' Gets an array of <see cref="ProductsbyCategoryRow"/> 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="ProductsbyCategoryRow"/> objects.</returns>
    Public Overridable Function GetAsArray(whereSql As String, orderBySql As String, _
                        startIndex As Integer, length As Integer, _
                        ByRef totalRecordCount As Integer) As ProductsbyCategoryRow()
        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 by Category]"
        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="ProductsbyCategoryRow"/> objects.</returns>
    Protected Function MapRecords(command As IDbCommand) As ProductsbyCategoryRow()
        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="ProductsbyCategoryRow"/> objects.</returns>
    Protected Function MapRecords(reader As IDataReader) As ProductsbyCategoryRow()
        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="ProductsbyCategoryRow"/> objects.</returns>
    Protected Overridable Function MapRecords(reader As IDataReader, startIndex As Integer, _
                        length As Integer, ByRef totalRecordCount As Integer) As ProductsbyCategoryRow()
        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 categoryNameColumnIndex As Integer = reader.GetOrdinal("CategoryName")
        Dim productNameColumnIndex As Integer = reader.GetOrdinal("ProductName")
        Dim quantityPerUnitColumnIndex As Integer = reader.GetOrdinal("QuantityPerUnit")
        Dim unitsInStockColumnIndex As Integer = reader.GetOrdinal("UnitsInStock")
        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 ProductsbyCategoryRow = New ProductsbyCategoryRow()
                recordList.Add(record)

                record.CategoryName = Convert.ToString(reader.GetValue(categoryNameColumnIndex))
                record.ProductName = Convert.ToString(reader.GetValue(productNameColumnIndex))
                If Not reader.IsDBNull(quantityPerUnitColumnIndex) Then
                    record.QuantityPerUnit = Convert.ToString(reader.GetValue(quantityPerUnitColumnIndex))
                End If
                If Not reader.IsDBNull(unitsInStockColumnIndex) Then
                    record.UnitsInStock = Convert.ToInt16(reader.GetValue(unitsInStockColumnIndex))
                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(ProductsbyCategoryRow)), ProductsbyCategoryRow())
    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="ProductsbyCategoryRow"/>.
    ''' </summary>
    ''' <param name="row">The <see cref="System.Data.DataRow"/> object to be mapped.</param>
    ''' <returns>A reference to the <see cref="ProductsbyCategoryRow"/> object.</returns>
    Protected Overridable Function MapRow(row As DataRow) As ProductsbyCategoryRow
        Dim mappedObject As ProductsbyCategoryRow = New ProductsbyCategoryRow()
        Dim dataTable As DataTable = row.Table
        Dim dataColumn As DataColumn
        ' Column "CategoryName"
        dataColumn = dataTable.Columns("CategoryName")
        If Not row.IsNull(dataColumn) Then
            mappedObject.CategoryName = CType(row(dataColumn), String)
        End If
        ' Column "ProductName"
        dataColumn = dataTable.Columns("ProductName")
        If Not row.IsNull(dataColumn) Then
            mappedObject.ProductName = CType(row(dataColumn), String)
        End If
        ' Column "QuantityPerUnit"
        dataColumn = dataTable.Columns("QuantityPerUnit")
        If Not row.IsNull(dataColumn) Then
            mappedObject.QuantityPerUnit = CType(row(dataColumn), String)
        End If
        ' Column "UnitsInStock"
        dataColumn = dataTable.Columns("UnitsInStock")
        If Not row.IsNull(dataColumn) Then
            mappedObject.UnitsInStock = 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
        Return mappedObject
    End Function

    ''' <summary>
    ''' Creates a <see cref="System.Data.DataTable"/> object for 
    ''' the <c>Products by Category</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 = "ProductsbyCategory"
        Dim dataColumn As DataColumn
        ' Create the "CategoryName" column
        dataColumn = dataTable.Columns.Add("CategoryName", GetType(String))
        dataColumn.MaxLength = 15
        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 "QuantityPerUnit" column
        dataColumn = dataTable.Columns.Add("QuantityPerUnit", GetType(String))
        dataColumn.MaxLength = 20
        dataColumn.ReadOnly = True
        ' Create the "UnitsInStock" column
        dataColumn = dataTable.Columns.Add("UnitsInStock", GetType(Short))
        dataColumn.ReadOnly = True
        ' Create the "Discontinued" column
        dataColumn = dataTable.Columns.Add("Discontinued", GetType(Boolean))
        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 "CategoryName"
                parameter = _db.AddParameter(cmd, paramName, DbType.String, value)
            Case "ProductName"
                parameter = _db.AddParameter(cmd, paramName, DbType.String, value)
            Case "QuantityPerUnit"
                parameter = _db.AddParameter(cmd, paramName, DbType.String, value)
            Case "UnitsInStock"
                parameter = _db.AddParameter(cmd, paramName, DbType.Int16, value)
            Case "Discontinued"
                parameter = _db.AddParameter(cmd, paramName, DbType.Boolean, value)
            Case Else
                Throw New ArgumentException("Unknown parameter name (" + paramName + ").")
        End Select
        Return parameter
    End Function
End Class