// <fileinfo name="EmployeesCollection_Base.cs">
// <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>
using System;
using System.Data;
namespace MyCompany.MyProject.Db
{
/// <summary>
/// The base class for <see cref="EmployeesCollection"/>. Provides methods
/// for common database table operations.
/// </summary>
/// <remarks>
/// Do not change this source code. Update the <see cref="EmployeesCollection"/>
/// class if you need to add or change some functionality.
/// </remarks>
public abstract class EmployeesCollection_Base
{
// Constants
public const string EmployeeIDColumnName = "EmployeeID";
public const string LastNameColumnName = "LastName";
public const string FirstNameColumnName = "FirstName";
public const string TitleColumnName = "Title";
public const string TitleOfCourtesyColumnName = "TitleOfCourtesy";
public const string BirthDateColumnName = "BirthDate";
public const string HireDateColumnName = "HireDate";
public const string AddressColumnName = "Address";
public const string CityColumnName = "City";
public const string RegionColumnName = "Region";
public const string PostalCodeColumnName = "PostalCode";
public const string CountryColumnName = "Country";
public const string HomePhoneColumnName = "HomePhone";
public const string ExtensionColumnName = "Extension";
public const string PhotoColumnName = "Photo";
public const string NotesColumnName = "Notes";
public const string ReportsToColumnName = "ReportsTo";
public const string PhotoPathColumnName = "PhotoPath";
// Instance fields
private Northwind _db;
/// <summary>
/// Initializes a new instance of the <see cref="EmployeesCollection_Base"/>
/// class with the specified <see cref="Northwind"/>.
/// </summary>
/// <param name="db">The <see cref="Northwind"/> object.</param>
public EmployeesCollection_Base(Northwind db)
{
_db = db;
}
/// <summary>
/// Gets the database object that this table belongs to.
/// </summary>
/// <value>The <see cref="Northwind"/> object.</value>
protected Northwind Database
{
get { return _db; }
}
/// <summary>
/// Gets an array of all records from the <c>Employees</c> table.
/// </summary>
/// <returns>An array of <see cref="EmployeesRow"/> objects.</returns>
public virtual EmployeesRow[] GetAll()
{
return MapRecords(CreateGetAllCommand());
}
/// <summary>
/// Gets a <see cref="System.Data.DataTable"/> object that
/// includes all records from the <c>Employees</c> table.
/// </summary>
/// <returns>A reference to the <see cref="System.Data.DataTable"/> object.</returns>
public virtual DataTable GetAllAsDataTable()
{
return MapRecordsToDataTable(CreateGetAllCommand());
}
/// <summary>
/// Creates and returns an <see cref="System.Data.IDbCommand"/> object that is used
/// to retrieve all records from the <c>Employees</c> table.
/// </summary>
/// <returns>A reference to the <see cref="System.Data.IDbCommand"/> object.</returns>
protected virtual IDbCommand CreateGetAllCommand()
{
return CreateGetCommand(null, null);
}
/// <summary>
/// Gets the first <see cref="EmployeesRow"/> 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="EmployeesRow"/> or null reference
/// (Nothing in Visual Basic) if the object was not found.</returns>
public EmployeesRow GetRow(string whereSql)
{
int totalRecordCount = -1;
EmployeesRow[] rows = GetAsArray(whereSql, null, 0, 1, ref totalRecordCount);
return 0 == rows.Length ? null : rows[0];
}
/// <summary>
/// Gets an array of <see cref="EmployeesRow"/> 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="EmployeesRow"/> objects.</returns>
public EmployeesRow[] GetAsArray(string whereSql, string orderBySql)
{
int totalRecordCount = -1;
return GetAsArray(whereSql, orderBySql, 0, int.MaxValue, ref totalRecordCount);
}
/// <summary>
/// Gets an array of <see cref="EmployeesRow"/> 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="EmployeesRow"/> objects.</returns>
public virtual EmployeesRow[] GetAsArray(string whereSql, string orderBySql,
int startIndex, int length, ref int totalRecordCount)
{
using(IDataReader reader = _db.ExecuteReader(CreateGetCommand(whereSql, orderBySql)))
{
return MapRecords(reader, startIndex, length, ref totalRecordCount);
}
}
/// <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 DataTable GetAsDataTable(string whereSql, string orderBySql)
{
int totalRecordCount = -1;
return GetAsDataTable(whereSql, orderBySql, 0, int.MaxValue, ref totalRecordCount);
}
/// <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 virtual DataTable GetAsDataTable(string whereSql, string orderBySql,
int startIndex, int length, ref int totalRecordCount)
{
using(IDataReader reader = _db.ExecuteReader(CreateGetCommand(whereSql, orderBySql)))
{
return MapRecordsToDataTable(reader, startIndex, length, ref totalRecordCount);
}
}
/// <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 virtual IDbCommand CreateGetCommand(string whereSql, string orderBySql)
{
string sql = "SELECT * FROM [dbo].[Employees]";
if(null != whereSql && 0 < whereSql.Length)
sql += " WHERE " + whereSql;
if(null != orderBySql && 0 < orderBySql.Length)
sql += " ORDER BY " + orderBySql;
return _db.CreateCommand(sql);
}
/// <summary>
/// Gets <see cref="EmployeesRow"/> by the primary key.
/// </summary>
/// <param name="employeeID">The <c>EmployeeID</c> column value.</param>
/// <returns>An instance of <see cref="EmployeesRow"/> or null reference
/// (Nothing in Visual Basic) if the object was not found.</returns>
public virtual EmployeesRow GetByPrimaryKey(int employeeID)
{
string whereSql = "[EmployeeID]=" + _db.CreateSqlParameterName("EmployeeID");
IDbCommand cmd = CreateGetCommand(whereSql, null);
AddParameter(cmd, "EmployeeID", employeeID);
EmployeesRow[] tempArray = MapRecords(cmd);
return 0 == tempArray.Length ? null : tempArray[0];
}
/// <summary>
/// Gets an array of <see cref="EmployeesRow"/> objects
/// by the <c>FK_Employees_Employees</c> foreign key.
/// </summary>
/// <param name="reportsTo">The <c>ReportsTo</c> column value.</param>
/// <returns>An array of <see cref="EmployeesRow"/> objects.</returns>
public EmployeesRow[] GetByReportsTo(int reportsTo)
{
return GetByReportsTo(reportsTo, false);
}
/// <summary>
/// Gets an array of <see cref="EmployeesRow"/> objects
/// by the <c>FK_Employees_Employees</c> foreign key.
/// </summary>
/// <param name="reportsTo">The <c>ReportsTo</c> column value.</param>
/// <param name="reportsToNull">true if the method ignores the reportsTo
/// parameter value and uses DbNull instead of it; otherwise, false.</param>
/// <returns>An array of <see cref="EmployeesRow"/> objects.</returns>
public virtual EmployeesRow[] GetByReportsTo(int reportsTo, bool reportsToNull)
{
return MapRecords(CreateGetByReportsToCommand(reportsTo, reportsToNull));
}
/// <summary>
/// Gets a <see cref="System.Data.DataTable"/> object
/// by the <c>FK_Employees_Employees</c> foreign key.
/// </summary>
/// <param name="reportsTo">The <c>ReportsTo</c> column value.</param>
/// <returns>A reference to the <see cref="System.Data.DataTable"/> object.</returns>
public DataTable GetByReportsToAsDataTable(int reportsTo)
{
return GetByReportsToAsDataTable(reportsTo, false);
}
/// <summary>
/// Gets a <see cref="System.Data.DataTable"/> object
/// by the <c>FK_Employees_Employees</c> foreign key.
/// </summary>
/// <param name="reportsTo">The <c>ReportsTo</c> column value.</param>
/// <param name="reportsToNull">true if the method ignores the reportsTo
/// parameter value and uses DbNull instead of it; otherwise, false.</param>
/// <returns>A reference to the <see cref="System.Data.DataTable"/> object.</returns>
public virtual DataTable GetByReportsToAsDataTable(int reportsTo, bool reportsToNull)
{
return MapRecordsToDataTable(CreateGetByReportsToCommand(reportsTo, reportsToNull));
}
/// <summary>
/// Creates an <see cref="System.Data.IDbCommand"/> object that can be used to
/// return records by the <c>FK_Employees_Employees</c> foreign key.
/// </summary>
/// <param name="reportsTo">The <c>ReportsTo</c> column value.</param>
/// <param name="reportsToNull">true if the method ignores the reportsTo
/// parameter value and uses DbNull instead of it; otherwise, false.</param>
/// <returns>A reference to the <see cref="System.Data.IDbCommand"/> object.</returns>
protected virtual IDbCommand CreateGetByReportsToCommand(int reportsTo, bool reportsToNull)
{
string whereSql = "";
if(reportsToNull)
whereSql += "[ReportsTo] IS NULL";
else
whereSql += "[ReportsTo]=" + _db.CreateSqlParameterName("ReportsTo");
IDbCommand cmd = CreateGetCommand(whereSql, null);
if(!reportsToNull)
AddParameter(cmd, "ReportsTo", reportsTo);
return cmd;
}
/// <summary>
/// Adds a new record into the <c>Employees</c> table.
/// </summary>
/// <param name="value">The <see cref="EmployeesRow"/> object to be inserted.</param>
public virtual void Insert(EmployeesRow value)
{
string sqlStr = "INSERT INTO [dbo].[Employees] (" +
"[LastName], " +
"[FirstName], " +
"[Title], " +
"[TitleOfCourtesy], " +
"[BirthDate], " +
"[HireDate], " +
"[Address], " +
"[City], " +
"[Region], " +
"[PostalCode], " +
"[Country], " +
"[HomePhone], " +
"[Extension], " +
"[Photo], " +
"[Notes], " +
"[ReportsTo], " +
"[PhotoPath]" +
") VALUES (" +
_db.CreateSqlParameterName("LastName") + ", " +
_db.CreateSqlParameterName("FirstName") + ", " +
_db.CreateSqlParameterName("Title") + ", " +
_db.CreateSqlParameterName("TitleOfCourtesy") + ", " +
_db.CreateSqlParameterName("BirthDate") + ", " +
_db.CreateSqlParameterName("HireDate") + ", " +
_db.CreateSqlParameterName("Address") + ", " +
_db.CreateSqlParameterName("City") + ", " +
_db.CreateSqlParameterName("Region") + ", " +
_db.CreateSqlParameterName("PostalCode") + ", " +
_db.CreateSqlParameterName("Country") + ", " +
_db.CreateSqlParameterName("HomePhone") + ", " +
_db.CreateSqlParameterName("Extension") + ", " +
_db.CreateSqlParameterName("Photo") + ", " +
_db.CreateSqlParameterName("Notes") + ", " +
_db.CreateSqlParameterName("ReportsTo") + ", " +
_db.CreateSqlParameterName("PhotoPath") + ");SELECT @@IDENTITY";
IDbCommand cmd = _db.CreateCommand(sqlStr);
AddParameter(cmd, "LastName", value.LastName);
AddParameter(cmd, "FirstName", value.FirstName);
AddParameter(cmd, "Title", value.Title);
AddParameter(cmd, "TitleOfCourtesy", value.TitleOfCourtesy);
AddParameter(cmd, "BirthDate",
value.IsBirthDateNull ? DBNull.Value : (object)value.BirthDate);
AddParameter(cmd, "HireDate",
value.IsHireDateNull ? DBNull.Value : (object)value.HireDate);
AddParameter(cmd, "Address", value.Address);
AddParameter(cmd, "City", value.City);
AddParameter(cmd, "Region", value.Region);
AddParameter(cmd, "PostalCode", value.PostalCode);
AddParameter(cmd, "Country", value.Country);
AddParameter(cmd, "HomePhone", value.HomePhone);
AddParameter(cmd, "Extension", value.Extension);
AddParameter(cmd, "Photo", value.Photo);
AddParameter(cmd, "Notes", value.Notes);
AddParameter(cmd, "ReportsTo",
value.IsReportsToNull ? DBNull.Value : (object)value.ReportsTo);
AddParameter(cmd, "PhotoPath", value.PhotoPath);
value.EmployeeID = Convert.ToInt32(cmd.ExecuteScalar());
}
/// <summary>
/// Updates a record in the <c>Employees</c> table.
/// </summary>
/// <param name="value">The <see cref="EmployeesRow"/>
/// object used to update the table record.</param>
/// <returns>true if the record was updated; otherwise, false.</returns>
public virtual bool Update(EmployeesRow value)
{
string sqlStr = "UPDATE [dbo].[Employees] SET " +
"[LastName]=" + _db.CreateSqlParameterName("LastName") + ", " +
"[FirstName]=" + _db.CreateSqlParameterName("FirstName") + ", " +
"[Title]=" + _db.CreateSqlParameterName("Title") + ", " +
"[TitleOfCourtesy]=" + _db.CreateSqlParameterName("TitleOfCourtesy") + ", " +
"[BirthDate]=" + _db.CreateSqlParameterName("BirthDate") + ", " +
"[HireDate]=" + _db.CreateSqlParameterName("HireDate") + ", " +
"[Address]=" + _db.CreateSqlParameterName("Address") + ", " +
"[City]=" + _db.CreateSqlParameterName("City") + ", " +
"[Region]=" + _db.CreateSqlParameterName("Region") + ", " +
"[PostalCode]=" + _db.CreateSqlParameterName("PostalCode") + ", " +
"[Country]=" + _db.CreateSqlParameterName("Country") + ", " +
"[HomePhone]=" + _db.CreateSqlParameterName("HomePhone") + ", " +
"[Extension]=" + _db.CreateSqlParameterName("Extension") + ", " +
"[Photo]=" + _db.CreateSqlParameterName("Photo") + ", " +
"[Notes]=" + _db.CreateSqlParameterName("Notes") + ", " +
"[ReportsTo]=" + _db.CreateSqlParameterName("ReportsTo") + ", " +
"[PhotoPath]=" + _db.CreateSqlParameterName("PhotoPath") +
" WHERE " +
"[EmployeeID]=" + _db.CreateSqlParameterName("EmployeeID");
IDbCommand cmd = _db.CreateCommand(sqlStr);
AddParameter(cmd, "LastName", value.LastName);
AddParameter(cmd, "FirstName", value.FirstName);
AddParameter(cmd, "Title", value.Title);
AddParameter(cmd, "TitleOfCourtesy", value.TitleOfCourtesy);
AddParameter(cmd, "BirthDate",
value.IsBirthDateNull ? DBNull.Value : (object)value.BirthDate);
AddParameter(cmd, "HireDate",
value.IsHireDateNull ? DBNull.Value : (object)value.HireDate);
AddParameter(cmd, "Address", value.Address);
AddParameter(cmd, "City", value.City);
AddParameter(cmd, "Region", value.Region);
AddParameter(cmd, "PostalCode", value.PostalCode);
AddParameter(cmd, "Country", value.Country);
AddParameter(cmd, "HomePhone", value.HomePhone);
AddParameter(cmd, "Extension", value.Extension);
AddParameter(cmd, "Photo", value.Photo);
AddParameter(cmd, "Notes", value.Notes);
AddParameter(cmd, "ReportsTo",
value.IsReportsToNull ? DBNull.Value : (object)value.ReportsTo);
AddParameter(cmd, "PhotoPath", value.PhotoPath);
AddParameter(cmd, "EmployeeID", value.EmployeeID);
return 0 != cmd.ExecuteNonQuery();
}
/// <summary>
/// Updates the <c>Employees</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 void Update(DataTable table)
{
Update(table, true);
}
/// <summary>
/// Updates the <c>Employees</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 virtual void Update(DataTable table, bool acceptChanges)
{
DataRowCollection rows = table.Rows;
for(int i = rows.Count - 1; i >= 0; i--)
{
DataRow row = rows[i];
switch(row.RowState)
{
case DataRowState.Added:
Insert(MapRow(row));
if(acceptChanges)
row.AcceptChanges();
break;
case DataRowState.Deleted:
// Temporary reject changes to be able to access to the PK column(s)
row.RejectChanges();
try
{
DeleteByPrimaryKey((int)row["EmployeeID"]);
}
finally
{
row.Delete();
}
if(acceptChanges)
row.AcceptChanges();
break;
case DataRowState.Modified:
Update(MapRow(row));
if(acceptChanges)
row.AcceptChanges();
break;
}
}
}
/// <summary>
/// Deletes the specified object from the <c>Employees</c> table.
/// </summary>
/// <param name="value">The <see cref="EmployeesRow"/> object to delete.</param>
/// <returns>true if the record was deleted; otherwise, false.</returns>
public bool Delete(EmployeesRow value)
{
return DeleteByPrimaryKey(value.EmployeeID);
}
/// <summary>
/// Deletes a record from the <c>Employees</c> table using
/// the specified primary key.
/// </summary>
/// <param name="employeeID">The <c>EmployeeID</c> column value.</param>
/// <returns>true if the record was deleted; otherwise, false.</returns>
public virtual bool DeleteByPrimaryKey(int employeeID)
{
string whereSql = "[EmployeeID]=" + _db.CreateSqlParameterName("EmployeeID");
IDbCommand cmd = CreateDeleteCommand(whereSql);
AddParameter(cmd, "EmployeeID", employeeID);
return 0 < cmd.ExecuteNonQuery();
}
/// <summary>
/// Deletes records from the <c>Employees</c> table using the
/// <c>FK_Employees_Employees</c> foreign key.
/// </summary>
/// <param name="reportsTo">The <c>ReportsTo</c> column value.</param>
/// <returns>The number of records deleted from the table.</returns>
public int DeleteByReportsTo(int reportsTo)
{
return DeleteByReportsTo(reportsTo, false);
}
/// <summary>
/// Deletes records from the <c>Employees</c> table using the
/// <c>FK_Employees_Employees</c> foreign key.
/// </summary>
/// <param name="reportsTo">The <c>ReportsTo</c> column value.</param>
/// <param name="reportsToNull">true if the method ignores the reportsTo
/// parameter value and uses DbNull instead of it; otherwise, false.</param>
/// <returns>The number of records deleted from the table.</returns>
public int DeleteByReportsTo(int reportsTo, bool reportsToNull)
{
return CreateDeleteByReportsToCommand(reportsTo, reportsToNull).ExecuteNonQuery();