' <fileinfo name="RegionCollection_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="RegionCollection"/>. Provides methods
''' for common database table operations.
''' </summary>
''' <remarks>
''' Do not change this source code. Update the <see cref="RegionCollection"/>
''' class if you need to add or change some functionality.
''' </remarks>
Public MustInherit Class RegionCollection_Base
' Constants
Public Const RegionIDColumnName As String = "RegionID"
Public Const RegionDescriptionColumnName As String = "RegionDescription"
' Instance fields
Private _db As Northwind
''' <summary>
''' Initializes a new instance of the <see cref="RegionCollection_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>Region</c> table.
''' </summary>
''' <returns>An array of <see cref="RegionRow"/> objects.</returns>
Public Overridable Function GetAll() As RegionRow()
Return MapRecords(CreateGetAllCommand())
End Function
''' <summary>
''' Gets a <see cref="System.Data.DataTable"/> object that
''' includes all records from the <c>Region</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>Region</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._Region_GetAll", True)
End Function
''' <summary>
''' Gets the first <see cref="RegionRow"/> 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="RegionRow"/> or null reference
''' (Nothing in Visual Basic) if the object was not found.</returns>
Public Function GetRow(whereSql As String) As RegionRow
Dim totalRecordCount As Integer = -1
Dim rows As RegionRow() = 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="RegionRow"/> 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="RegionRow"/> objects.</returns>
Public Function GetAsArray(whereSql As String, orderBySql As String) As RegionRow()
Dim totalRecordCount As Integer = -1
Return GetAsArray(whereSql, orderBySql, 0, Integer.MaxValue, totalRecordCount)
End Function
''' <summary>
''' Gets an array of <see cref="RegionRow"/> 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="RegionRow"/> objects.</returns>
Public Overridable Function GetAsArray(whereSql As String, orderBySql As String, _
startIndex As Integer, length As Integer, _
ByRef totalRecordCount As Integer) As RegionRow()
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].[Region]"
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="RegionRow"/> by the primary key.
''' </summary>
''' <param name="regionID">The <c>RegionID</c> column value.</param>
''' <returns>An instance of <see cref="RegionRow"/> or null reference
''' (Nothing in Visual Basic) if the object was not found.</returns>
Public Overridable Function GetByPrimaryKey(regionID As Integer) As RegionRow
Dim cmd As IDbCommand = _db.CreateCommand("dbo._Region_GetByPrimaryKey", True)
AddParameter(cmd, "RegionID", regionID)
Dim tempArray As RegionRow() = MapRecords(cmd)
If 0 = tempArray.Length Then
Return Nothing
End If
Return tempArray(0)
End Function
''' <summary>
''' Adds a new record into the <c>Region</c> table.
''' </summary>
''' <param name="value">The <see cref="RegionRow"/> object to be inserted.</param>
Public Overridable Sub Insert(value As RegionRow)
Dim cmd As IDbCommand = _db.CreateCommand("dbo._Region_Insert", true)
AddParameter(cmd, "RegionID", value.RegionID)
AddParameter(cmd, "RegionDescription", value.RegionDescription)
cmd.ExecuteNonQuery()
End Sub
''' <summary>
''' Updates a record in the <c>Region</c> table.
''' </summary>
''' <param name="value">The <see cref="RegionRow"/>
''' object used to update the table record.</param>
''' <returns>true if the record was updated; otherwise, false.</returns>
Public Overridable Function Update(value As RegionRow) As Boolean
Dim cmd As IDbCommand = _db.CreateCommand("dbo._Region_Update", true)
AddParameter(cmd, "RegionDescription", value.RegionDescription)
AddParameter(cmd, "RegionID", value.RegionID)
Return 0 <> cmd.ExecuteNonQuery()
End Function
''' <summary>
''' Updates the <c>Region</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>Region</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("RegionID"), 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>Region</c> table.
''' </summary>
''' <param name="value">The <see cref="RegionRow"/> object to delete.</param>
''' <returns>true if the record was deleted; otherwise, false.</returns>
Public Function Delete(value As RegionRow) As Boolean
Return DeleteByPrimaryKey(value.RegionID)
End Function
''' <summary>
''' Deletes a record from the <c>Region</c> table using
''' the specified primary key.
''' </summary>
''' <param name="regionID">The <c>RegionID</c> column value.</param>
''' <returns>true if the record was deleted; otherwise, false.</returns>
Public Overridable Function DeleteByPrimaryKey(regionID As Integer) As Boolean
Dim cmd As IDbCommand = _db.CreateCommand("dbo._Region_DeleteByPrimaryKey", true)
AddParameter(cmd, "RegionID", regionID)
Return 0 < cmd.ExecuteNonQuery()
End Function
''' <summary>
''' Deletes <c>Region</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>Region</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].[Region]"
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>Region</c> table.
''' </summary>
''' <returns>The number of deleted records.</returns>
Public Function DeleteAll() As Integer
Return _db.CreateCommand("dbo._Region_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="RegionRow"/> objects.</returns>
Protected Function MapRecords(command As IDbCommand) As RegionRow()
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="RegionRow"/> objects.</returns>
Protected Function MapRecords(reader As IDataReader) As RegionRow()
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="RegionRow"/> objects.</returns>
Protected Overridable Function MapRecords(reader As IDataReader, startIndex As Integer, _
length As Integer, ByRef totalRecordCount As Integer) As RegionRow()
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 regionIDColumnIndex As Integer = reader.GetOrdinal("RegionID")
Dim regionDescriptionColumnIndex As Integer = reader.GetOrdinal("RegionDescription")
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 RegionRow = New RegionRow()
recordList.Add(record)
record.RegionID = Convert.ToInt32(reader.GetValue(regionIDColumnIndex))
record.RegionDescription = Convert.ToString(reader.GetValue(regionDescriptionColumnIndex))
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(RegionRow)), RegionRow())
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="RegionRow"/>.
''' </summary>
''' <param name="row">The <see cref="System.Data.DataRow"/> object to be mapped.</param>
''' <returns>A reference to the <see cref="RegionRow"/> object.</returns>
Protected Overridable Function MapRow(row As DataRow) As RegionRow
Dim mappedObject As RegionRow = New RegionRow()
Dim dataTable As DataTable = row.Table
Dim dataColumn As DataColumn
' Column "RegionID"
dataColumn = dataTable.Columns("RegionID")
If Not row.IsNull(dataColumn) Then
mappedObject.RegionID = CType(row(dataColumn), Integer)
End If
' Column "RegionDescription"
dataColumn = dataTable.Columns("RegionDescription")
If Not row.IsNull(dataColumn) Then
mappedObject.RegionDescription = CType(row(dataColumn), String)
End If
Return mappedObject
End Function
''' <summary>
''' Creates a <see cref="System.Data.DataTable"/> object for
''' the <c>Region</c> table.
''' </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 = "Region"
Dim dataColumn As DataColumn
' Create the "RegionID" column
dataColumn = dataTable.Columns.Add("RegionID", GetType(Integer))
dataColumn.AllowDBNull = False
' Create the "RegionDescription" column
dataColumn = dataTable.Columns.Add("RegionDescription", GetType(String))
dataColumn.MaxLength = 50
dataColumn.AllowDBNull = False
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 "RegionID"
parameter = _db.AddParameter(cmd, paramName, DbType.Int32, value)
Case "RegionDescription"
parameter = _db.AddParameter(cmd, paramName, DbType.StringFixedLength, value)
Case Else
Throw New ArgumentException("Unknown parameter name (" + paramName + ").")
End Select
Return parameter
End Function
End Class