' <fileinfo name="CustomerandSuppliersbyCityCollection_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="CustomerandSuppliersbyCityCollection"/>. Provides methods 
''' for common database view operations. 
''' </summary>
''' <remarks>
''' Do not change this source code. Update the <see cref="CustomerandSuppliersbyCityCollection"/>
''' class if you need to add or change some functionality.
''' </remarks>
Public MustInherit Class CustomerandSuppliersbyCityCollection_Base
    ' Constants
    Public Const CityColumnName As String = "City"
    Public Const CompanyNameColumnName As String = "CompanyName"
    Public Const ContactNameColumnName As String = "ContactName"
    Public Const RelationshipColumnName As String = "Relationship"

    ' Instance fields
    Private _db As Northwind

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

    ''' <summary>
    ''' Gets a <see cref="System.Data.DataTable"/> object that 
    ''' includes all records from the <c>Customer and Suppliers by City</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>Customer and Suppliers by City</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="CustomerandSuppliersbyCityRow"/> 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="CustomerandSuppliersbyCityRow"/> or null reference 
    ''' (Nothing in Visual Basic) if the object was not found.</returns>
    Public Function GetRow(whereSql As String) As CustomerandSuppliersbyCityRow
        Dim totalRecordCount As Integer = -1
        Dim rows As CustomerandSuppliersbyCityRow() = 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="CustomerandSuppliersbyCityRow"/> 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="CustomerandSuppliersbyCityRow"/> objects.</returns>
    Public Function GetAsArray(whereSql As String, orderBySql As String) As CustomerandSuppliersbyCityRow()
        Dim totalRecordCount As Integer = -1
        Return GetAsArray(whereSql, orderBySql, 0, Integer.MaxValue, totalRecordCount)
    End Function

    ''' <summary>
    ''' Gets an array of <see cref="CustomerandSuppliersbyCityRow"/> 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="CustomerandSuppliersbyCityRow"/> objects.</returns>
    Public Overridable Function GetAsArray(whereSql As String, orderBySql As String, _
                        startIndex As Integer, length As Integer, _
                        ByRef totalRecordCount As Integer) As CustomerandSuppliersbyCityRow()
        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].[Customer and Suppliers by City]"
        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="CustomerandSuppliersbyCityRow"/> objects.</returns>
    Protected Function MapRecords(command As IDbCommand) As CustomerandSuppliersbyCityRow()
        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="CustomerandSuppliersbyCityRow"/> objects.</returns>
    Protected Function MapRecords(reader As IDataReader) As CustomerandSuppliersbyCityRow()
        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="CustomerandSuppliersbyCityRow"/> objects.</returns>
    Protected Overridable Function MapRecords(reader As IDataReader, startIndex As Integer, _
                        length As Integer, ByRef totalRecordCount As Integer) As CustomerandSuppliersbyCityRow()
        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 cityColumnIndex As Integer = reader.GetOrdinal("City")
        Dim companyNameColumnIndex As Integer = reader.GetOrdinal("CompanyName")
        Dim contactNameColumnIndex As Integer = reader.GetOrdinal("ContactName")
        Dim relationshipColumnIndex As Integer = reader.GetOrdinal("Relationship")

        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 CustomerandSuppliersbyCityRow = New CustomerandSuppliersbyCityRow()
                recordList.Add(record)

                If Not reader.IsDBNull(cityColumnIndex) Then
                    record.City = Convert.ToString(reader.GetValue(cityColumnIndex))
                End If
                record.CompanyName = Convert.ToString(reader.GetValue(companyNameColumnIndex))
                If Not reader.IsDBNull(contactNameColumnIndex) Then
                    record.ContactName = Convert.ToString(reader.GetValue(contactNameColumnIndex))
                End If
                record.Relationship = Convert.ToString(reader.GetValue(relationshipColumnIndex))

                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(CustomerandSuppliersbyCityRow)), CustomerandSuppliersbyCityRow())
    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="CustomerandSuppliersbyCityRow"/>.
    ''' </summary>
    ''' <param name="row">The <see cref="System.Data.DataRow"/> object to be mapped.</param>
    ''' <returns>A reference to the <see cref="CustomerandSuppliersbyCityRow"/> object.</returns>
    Protected Overridable Function MapRow(row As DataRow) As CustomerandSuppliersbyCityRow
        Dim mappedObject As CustomerandSuppliersbyCityRow = New CustomerandSuppliersbyCityRow()
        Dim dataTable As DataTable = row.Table
        Dim dataColumn As DataColumn
        ' Column "City"
        dataColumn = dataTable.Columns("City")
        If Not row.IsNull(dataColumn) Then
            mappedObject.City = CType(row(dataColumn), String)
        End If
        ' Column "CompanyName"
        dataColumn = dataTable.Columns("CompanyName")
        If Not row.IsNull(dataColumn) Then
            mappedObject.CompanyName = CType(row(dataColumn), String)
        End If
        ' Column "ContactName"
        dataColumn = dataTable.Columns("ContactName")
        If Not row.IsNull(dataColumn) Then
            mappedObject.ContactName = CType(row(dataColumn), String)
        End If
        ' Column "Relationship"
        dataColumn = dataTable.Columns("Relationship")
        If Not row.IsNull(dataColumn) Then
            mappedObject.Relationship = CType(row(dataColumn), String)
        End If
        Return mappedObject
    End Function

    ''' <summary>
    ''' Creates a <see cref="System.Data.DataTable"/> object for 
    ''' the <c>Customer and Suppliers by City</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 = "CustomerandSuppliersbyCity"
        Dim dataColumn As DataColumn
        ' Create the "City" column
        dataColumn = dataTable.Columns.Add("City", GetType(String))
        dataColumn.MaxLength = 15
        dataColumn.ReadOnly = True
        ' Create the "CompanyName" column
        dataColumn = dataTable.Columns.Add("CompanyName", GetType(String))
        dataColumn.MaxLength = 40
        dataColumn.AllowDBNull = False
        dataColumn.ReadOnly = True
        ' Create the "ContactName" column
        dataColumn = dataTable.Columns.Add("ContactName", GetType(String))
        dataColumn.MaxLength = 30
        dataColumn.ReadOnly = True
        ' Create the "Relationship" column
        dataColumn = dataTable.Columns.Add("Relationship", GetType(String))
        dataColumn.MaxLength = 9
        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 "City"
                parameter = _db.AddParameter(cmd, paramName, DbType.String, value)
            Case "CompanyName"
                parameter = _db.AddParameter(cmd, paramName, DbType.String, value)
            Case "ContactName"
                parameter = _db.AddParameter(cmd, paramName, DbType.String, value)
            Case "Relationship"
                parameter = _db.AddParameter(cmd, paramName, DbType.AnsiString, value)
            Case Else
                Throw New ArgumentException("Unknown parameter name (" + paramName + ").")
        End Select
        Return parameter
    End Function
End Class