AbstractDAO implements the Data Access Object (DAO) pattern and provides the plumbing for rapid DAO development. To use it, do the following:
Create a new DAO object an inherit from
AbstractDAOImplement the abstract methods for:
CreateSelectOneCommandCreateInsertCommandCreateDeleteCommandCreateUpdateCommand
Implement the abstract
MapObjectmethod
Once you've done this, you can harness all of the capabilities of the DAO object. Below is a UML diagram showing all of the methods that are available with the AbstractDAO:
For this example we're going to create a CategoriesEntity object which implements the Transfer Object pattern (only holds data) and the CategoriesDAO object, which is responsible for all CRUD operations around the CategoriesEntity. CategoriesDAO should be able to save a CategoriesEntity to the underlying data store, along with updating, selecting and deleting.
Below is the CategoriesEntity
/******************************************
* Auto-generated by Codus
* 9/30/2005 5:18:20 PM
******************************************/
using System;
using System.Xml.Serialization;
using Test.Collections;
using Adapdev.Text;
namespace Test {
/// <summary>
/// An object representation of the Northwind Categories table
/// </summary>
[Serializable]
public abstract class CategoriesEntity{
private System.Int32 _CategoryID = 0;
private System.String _CategoryName = "";
private System.String _Description = "";
private System.Byte[] _Picture = null;
[XmlElement(ElementName = "CategoryID")]
public System.Int32 CategoryID {
get {
return this._CategoryID;
}
set {
this._CategoryID = value;
}
}
[XmlElement(ElementName = "CategoryName")]
public System.String CategoryName {
get {
return this._CategoryName;
}
set {
this._CategoryName = value;
}
}
[XmlElement(ElementName = "Description")]
public System.String Description {
get {
return this._Description;
}
set {
this._Description = value;
}
}
[XmlElement(ElementName = "Picture")]
public System.Byte[] Picture {
get {
return this._Picture;
}
set {
this._Picture = value;
}
}
/// <summary>
/// Returns a string representation of the object, displaying all
/// property and field names and values.
/// </summary>
public override string ToString() {
return StringUtil.ToString(this);
}
}
}
/******************************************
* Auto-generated by Codus
* 9/30/2005 5:18:20 PM
******************************************/
using System;
using System.Collections;
using System.Data;
using System.Data.Common;
using Adapdev.Data;
using Adapdev.Data.Sql;
namespace Test {
/// <summary>
/// Base Data Access Object for the Categories table.
/// </summary>
public abstract class CategoriesDAOBase : Adapdev.Data.AbstractDAO {
/// <summary>
/// A static representation of column CategoryID
/// </summary>
public static readonly string COLUMN_CATEGORYID = "CategoryID";
/// <summary>
/// A static representation of column CategoryName
/// </summary>
public static readonly string COLUMN_CATEGORYNAME = "CategoryName";
/// <summary>
/// A static representation of column Description
/// </summary>
public static readonly string COLUMN_DESCRIPTION = "Description";
/// <summary>
/// A static representation of column Picture
/// </summary>
public static readonly string COLUMN_PICTURE = "Picture";
/// <summary>
/// Provides access to the name of the primary key column (CategoryID)
/// </summary>
public static readonly string TABLE_PRIMARYKEY = "CategoryID";
/// <summary>
/// Provides access to the name of the table
/// </summary>
public static readonly string TABLE_NAME = "Categories";
/// <summary>
/// Provides access to the name of the database
/// </summary>
public static readonly string DATABASE_NAME = "Northwind";
/// <summary>
/// Constructor
/// </summary>
public CategoriesDAOBase() :
base(DbConstants.DatabaseProviderType, DbConstants.DatabaseType,
"Categories", DbConstants.ConnectionString) {
}
/// <summary>
/// Maps the IDataReader values to a CategoriesEntity object
/// </summary>
/// <param name="r">The IDataReader to map</param>
/// <returns>CategoriesEntity</returns>
protected override object MapObject(System.Data.IDataReader r) {
CategoriesEntity entity = new CategoriesEntity();
try{
int ordinal = r.GetOrdinal("CategoryID");
if (!r.IsDBNull(ordinal)) entity.CategoryID =
((System.Int32)(r.GetValue(ordinal)));
}
catch(Exception){}
try{
int ordinal = r.GetOrdinal("CategoryName");
if (!r.IsDBNull(ordinal)) entity.CategoryName =
((System.String)(r.GetValue(ordinal)));
}
catch(Exception){}
try{
int ordinal = r.GetOrdinal("Description");
if (!r.IsDBNull(ordinal)) entity.Description =
((System.String)(r.GetValue(ordinal)));
}
catch(Exception){}
try{
int ordinal = r.GetOrdinal("Picture");
if (!r.IsDBNull(ordinal)) entity.Picture =
((System.Byte[])(r.GetValue(ordinal)));
}
catch(Exception){}
return entity;
}
/// <summary>
/// Creates the sql insert command, using the values from the passed
/// in CategoriesEntity object
/// </summary>
/// <param name="o">A CategoriesEntity object,
/// from which the insert values are pulled</param>
/// <returns>An IDbCommand</returns>
protected override System.Data.IDbCommand CreateInsertCommand(object o) {
CategoriesEntity entity = ((CategoriesEntity)(o));
System.Data.IDbCommand cmd = this.CreateCommand(
"INSERT INTO [Categories] ( [CategoryName], [Description], [Picture] )" +
" VALUES ( @CategoryName, @Description, @Picture ) ");
IDataParameterCollection cmdParams = cmd.Parameters;
System.Data.IDbDataParameter parCategoryName = cmd.CreateParameter();
parCategoryName.ParameterName = "@CategoryName";
parCategoryName.Value = entity.CategoryName;
cmdParams.Add(parCategoryName);
System.Data.IDbDataParameter parDescription = cmd.CreateParameter();
parDescription.ParameterName = "@Description";
parDescription.Value = entity.Description;
cmdParams.Add(parDescription);
System.Data.IDbDataParameter parPicture = cmd.CreateParameter();
parPicture.ParameterName = "@Picture";
parPicture.Value = entity.Picture;
cmdParams.Add(parPicture);
return cmd;
}
/// <summary>
/// Creates the sql update command, using the values from the passed
/// in CategoriesEntity object
/// </summary>
/// <param name="o">A CategoriesEntity object,
/// from which the update values are pulled</param>
/// <returns>An IDbCommand</returns>
protected override System.Data.IDbCommand CreateUpdateCommand(object o) {
CategoriesEntity entity = ((CategoriesEntity)(o));
System.Data.IDbCommand cmd = this.CreateCommand(
"UPDATE [Categories] SET [CategoryName] = @CategoryName, [Description] = " +
"@Description, [Picture] = @Picture WHERE [CategoryID] = @CategoryID ");
IDataParameterCollection cmdParams = cmd.Parameters;
System.Data.IDbDataParameter parCategoryName = cmd.CreateParameter();
parCategoryName.ParameterName = "@CategoryName";
parCategoryName.Value = entity.CategoryName;
cmdParams.Add(parCategoryName);
System.Data.IDbDataParameter parDescription = cmd.CreateParameter();
parDescription.ParameterName = "@Description";
parDescription.Value = entity.Description;
cmdParams.Add(parDescription);
System.Data.IDbDataParameter parPicture = cmd.CreateParameter();
parPicture.ParameterName = "@Picture";
parPicture.Value = entity.Picture;
cmdParams.Add(parPicture);
System.Data.IDbDataParameter pkparCategoryID = cmd.CreateParameter();
pkparCategoryID.ParameterName = "@CategoryID";
pkparCategoryID.Value = entity.CategoryID;
cmdParams.Add(pkparCategoryID);
return cmd;
}
/// <summary>
/// Creates the sql delete command, using the passed in primary key
/// </summary>
/// <param name="id">The primary key of the object to delete</param>
/// <returns>An IDbCommand</returns>
protected override System.Data.IDbCommand CreateDeleteOneCommand(object id) {
System.Data.IDbCommand cmd = this.CreateCommand(
"DELETE FROM [Categories] WHERE [CategoryID] = @CategoryID ");
IDataParameterCollection cmdParams = cmd.Parameters;
System.Data.IDbDataParameter par = cmd.CreateParameter();
par.ParameterName = "@CategoryID";
par.Value = id;
cmdParams.Add(par);
return cmd;
}
/// <summary>
/// Creates the sql select command, using the passed in primary key
/// </summary>
/// <param name="o">The primary key of the object to select</param>
/// <returns>An IDbCommand</returns>
protected override System.Data.IDbCommand CreateSelectOneCommand(object id) {
System.Data.IDbCommand cmd = this.CreateCommand(
"SELECT [CategoryID], [CategoryName], [Description], [Picture] " +
"FROM [Categories] WHERE [CategoryID] = @CategoryID ");
IDataParameterCollection cmdParams = cmd.Parameters;
System.Data.IDbDataParameter par = cmd.CreateParameter();
par.ParameterName = "@CategoryID";
par.Value = id;
cmdParams.Add(par);
return cmd;
}
}
}
AbstractDAO provides a ton of functionality. Below are a few examples of what can be done.
Get All Objects
// returns all rows in the db
CategoriesDAO dao = new CategoriesDAO();
IList categories = dao.SelectAll();
// returns a collection of CategoriesEntity objects
Get a Specific Object
// get record 1 from the db
CategoriesDAO dao = new CategoriesDAO();
CategoriesEntity entity = dao.SelectById(1) as CategoriesEntity;
Save an Object
CategoriesEntity entity = new CategoriesEntity();
... // do some work
CategoriesDAO dao = new CategoriesDAO();
dao.Save(entity); // saves the object to the db
Update an Object
// get record 1 from the db
CategoriesDAO dao = new CategoriesDAO();
CategoriesEntity entity = dao.SelectById(1) as CategoriesEntity;
... // make some changes
dao.Update(entity); // persists the changes
Delete an Object
// deletes record 1 from the db
CategoriesDAO dao = new CategoriesDAO();
dao.Delete(1);Get a DataSet
// gets all records in a DataSet
CategoriesDAO dao = new CategoriesDAO();
DataSet ds = dao.SelectDataSet();
Get the Record Count
// gets the record count
CategoriesDAO dao = new CategoriesDAO();
int count = dao.GetCount();
Execute a Non Query
CategoriesDAO dao = new CategoriesDAO();
dao.ExecuteNonQuery("DELETE FROM Categories");Using parametized queries is strongly recommended for most applications. However, one of the downfalls of the IDbCommand object is that once you assign values to a CommandText, there's no way to "reverse" it out and see the resulting sql statement with the assigned values. Here's an example:
string cmd = "INSERT INTO Test (cInt, cString, cDate, cBool) VALUES (?, ?, ?, ?);";
OleDbCommand command = new OleDbCommand();
command.CommandText = cmd;
OleDbParameter cInt =
command.Parameters.Add("@cInt", OleDbType.Integer, 1);
cInt.Value = 1;
OleDbParameter cString =
command.Parameters.Add("@cString", OleDbType.VarChar, 20);
cString.Value = "test";
OleDbParameter cDate =
command.Parameters.Add("@cDate", OleDbType.DBTimeStamp, 20);
cDate.Value = DateTime.Now;
OleDbParameter cBool =
command.Parameters.Add("@cBool", OleDbType.Boolean, 20);
cBool.Value = false;
Console.WriteLine(command.CommandText);
// Output: INSERT INTO Test (cInt, cString, cDate, cBool) VALUES (?, ?, ?, ?);
Even though the values were assigned, there's no way to validate them. This can often cause problems during debugging when you want to confirm that values were actually assigned to a statement or verify the values passed in. The CommandTextViewer gives you this capability.
string cmd = "INSERT INTO Test (cInt, cString, cDate, cBool) VALUES (?, ?, ?, ?);";
OleDbCommand command = new OleDbCommand();
command.CommandText = cmd;
OleDbParameter cInt = command.Parameters.Add("@cInt", OleDbType.Integer, 1);
cInt.Value = 1;
OleDbParameter cString = command.Parameters.Add("@cString", OleDbType.VarChar, 20);
cString.Value = "test";
OleDbParameter cDate = command.Parameters.Add("@cDate", OleDbType.DBTimeStamp, 20);
cDate.Value = DateTime.Now;
OleDbParameter cBool = command.Parameters.Add("@cBool", OleDbType.Boolean, 20);
cBool.Value = false;
CommandTextViewer.ParseOleDbCommand(command.CommandText);
// Output:
INSERT INTO Test (cInt, cString, cDate, cBool)
VALUES (1, 'test', '2/25/2006 6:18:57 AM', False);The DataReaderDebugger is used to display all the contents of an IDataReader implementation
... // create your IDataReader and populate it
Console.WriteLine(DataReaderDebugger.ToString(dataReader));
The DataSetDebugger is used to display all the contents of a DataSet:
... // create your DataSet and populate it
Console.WriteLine(DataSetDebugger.ToString(dataSet));
// Example Output:
Table
Row #1-
CategoryID: 1
CategoryName: Beverages
Description: Soft drinks, coffees, teas, beers, and ales
Picture: System.Byte[]
Row #2-
CategoryID: 2
CategoryName: Condiments
Description: Sweet and savory sauces, relishes, spreads, and seasonings
Picture: System.Byte[]
DbProviderFactory allows for the creation of database code independent from the actual implementation. This provides for portable code that can automatically switch between databases.
The DbProviderFactory currently supports the following databases:
MS Access
MS Sql Server
Oracle
MySql
Additional databases will be added shortly.
IDbCommand is the base interface for objects such as SqlCommand, OleDbCommand, OracleCommand, etc. Using the CreateCommand method, you can retrieve an IDbCommand implementation. Below is an example.
// returns an IDbCommand targeted for mySql
IDbCommand command = DbProviderFactory.CreateCommand(DbProviderType.MYSQL);
command.CommandText = "SELECT * FROM SomeTable;";
... // continue
All connection objects inherit from IDbConnection. Using DbProviderFactory you can retrieve a database specific connection object.
IDbConnection connection = DbProviderFactory.CreateConnection(DbProviderType.ORACLE);
connection.ConnectionString = "...";
connection.Open();
A fast and simple way to retrieve a populated IDataReader is via the CreateDataReader method. All it requires is a connection and command.
// returns an open, populated data reader
IDataReader reader = DbProviderFactory.CreateDataReader("some connection",
"select * from sometable",
DbProviderType.SQLSERVER");
while (reader.Read())
Console.WriteLine("\t{0}\t{1}", reader.GetInt32(0), reader.GetString(1));
reader.Close();
There are several overriden methods that allow for IDbCommand objects, IDbConnection objects, etc. to be passed in.
To quickly retrieve a populated DataSet, the CreateDataSet method is provided. Below is an example:
// returns an open, populated data reader
DataSet dataset = DbProviderFactory.CreateDataSet("some connection",
"select * from sometable",
DbProviderType.SQLSERVER");
Console.WriteLine(DataSetDebugger.ToString(dataset));