Getting the total number of records in a table

EmployeesDAO dao = new EmployeesDAO();
int count = dao.GetCount();

Getting all records in a table

// Using IList
EmployeesDAO dao = new EmployeesDAO();
IList employees = dao.SelectAll();
foreach(EmployeesEntity e in employees){
   Console.WriteLine(e.FirstName);
}

// Populate an ArrayList
EmployeesDAO dao = new EmployeesDAO();
ArrayList employees = dao.SelectAll() as ArrayList;
foreach(EmployeesEntity e in employees){
   Console.WriteLine(e.FirstName);
}

// Populate a strongly-typed collection
EmployeesDAO dao = new EmployeesDAO();
EmployeesEntityCollection employees = new EmployeesEntityCollection(dao.SelectAll());
foreach(EmployeesEntity e in employees){
   Console.WriteLine(e.FirstName);
}

Getting the top 10 records in a table

// Populate an ArrayList
EmployeesDAO dao = new EmployeesDAO();
IList employees = dao.SelectAllWithLimit(10);
foreach(EmployeesEntity e in employees){
   Console.WriteLine(e.FirstName);
}

// Populate a strongly-typed collection
EmployeesDAO dao = new EmployeesDAO();
EmployeesEntityCollection employees = new EmployeesEntityCollection(dao.SelectAllWithLimit(10));
foreach(EmployeesEntity e in employees){
   Console.WriteLine(e.FirstName);
}

Getting all the records in a table, via a DataSet

// Populate a DataSet
EmployeesDAO dao = new EmployeesDAO();
DataSet employeesDS = dao.SelectAllDS();

Getting a specific record

// Retrieve employee record 22
EmployeesDAO dao = new EmployeesDAO();
EmployeeEntity employee = dao.SelectById(22) as EmployeesEntity; // SelectOne uses the primary key value as the parameter

Getting a DataSet containing a specific record

// Retrieve employee record 22
EmployeesDAO dao = new EmployeesDAO();
DataSet employeeDS = dao.SelectDatasetById(22); // SelectOneDS uses the primary key value as the parameter

Getting a custom-defined set of records

// Get records using a custom sql statement
EmployeesDAO dao = new EmployeesDAO();
ArrayList al = new ArrayList(dao.Select("SELECT * FROM Employees WHERE FirstName LIKE 'm%'"));

Getting a custom-defined set of records, via a DataSet

// Get records using a custom sql statement
EmployeesDAO dao = new EmployeesDAO();
DataSet ds = dao.SelectDS("SELECT * FROM Employees WHERE FirstName LIKE 'm%'");

Getting all records with a specified foreign key

// Get records that contain a specified foreign key
// Codus generates SelectAllByXXX methods for each foreign key in a table
EmployeesDAO dao = new EmployeesDAO();
ArrayList al = new ArrayList(dao.SelectAllByReportsTo(2));
foreach(EmployeesEntity employee in al){
   Console.WriteLine(employee.FirstName);
}

Create a new record

// Create a new record
EmployeesEntity employee = new EmployeesEntity();
employee.FirstName = "Joe";
employee.LastName = "Schmoe";
// ...etc.

EmployeesDAO dao = new EmployeesDAO();
dao.Save(employee);

Update an existing record

EmployeesDAO dao = new EmployeesDAO();

// Grab employee 22 and update who they report to
EmployeesEntity employee = dao.SelectOne(22) as EmployeesEntity;
employee.ReportsTo = 5;

dao.Update(employee);

Delete an existing record

EmployeesDAO dao = new EmployeesDAO();
dao.Delete(22); // Delete using the primary key

The DAO Framework supports two types of transactions:

Local Transactions

Local transactions allow you to perform transactions on a single database, such as Sql Server. Local transactions are provided via ADO.NET.

To use a local transaction that spans multiple tables or statements, you must create a connection, create a transaction, and then pass the connection and transaction to each action.

// Create the Entity objects
EmployeesEntity employee = new EmployeesEntity();
RegionEntity region = new RegionEntity();
// ...set some values

// Open the db connection and start the transaction
IDbConnection connection = ConnectionFactory.CreateConnection();
connection.Open();
IDbTransaction transaction = connection.BeginTransaction();

// Save the entities to their respective tables using a single transaction
EmployeesDAO employeesDAO = new EmployeesDAO();
RegionDAO regionDAO = new RegionDAO();

// Pass the connection and transaction object through for each operation
employeesDAO.Save(employee, connection, transaction);
regionDAO.Save(region, connection, transaction);

connection.Close();

Distributed Transactions

Distributed transactions allow code to span mutliple platforms in a single transaction. For example, a distributed transaction could do send a message via MSMQ, insert a record into SQL Server and then delete a record from Oracle. If any of these operations fail, the entire transaction will rollback. Distributed transactions are possible via COM+ and System.EnterpriseServices. The DAO Framework provides a special class, TransactionScope, which provides you the benefits of distributed transactions but avoids the standard overhead of COM+ (i.e. dynamic registration, strong-named assemblies, etc.)

// Create the Entity objects
EmployeesEntity employee = new EmployeesEntity();
RegionEntity region = new RegionEntity();
// ...set some values

EmployeesDAO employeesDAO = new EmployeesDAO();
RegionDAO regionDAO = new RegionDAO();

// Save them using a distributed transaction
using(Adapdev.Transactions.TransactionScope transaction = new TransactionScope()){

    employeesDAO.Save(employee);
    regionDAO.Save(region);

    transaction.Complete();
}

By default, each DAO call creates and destroys a database connection. For example:

// Connection is created and destroyed behind the scenes
EmployeesDAO dao = new EmployeesDAO();
dao.Delete(22); // Delete using the primary key

Unfortunately, creating and destroying connections is an expensive operation. If you have a program that is making multiple DAO calls, you're better served using only one open connection. Almost all DAO methods support the ability to pass in an open database connection. Below is an example:

EmployeesDAO dao = new EmployeesDAO();

// The using directive automatically closes the connection.  
// See MS.NET documentation for details.
// If you don't use the "using" directive, then you'll need to explicitly
// open and close the connection.
using(IDbConnection connection = dao.CreateConnection()){
   // open the connection
   connection.Open();

   // get an existing record
   EmployeesEntity employee = dao.SelectOne(8, connection) as EmployeesEntity;
   employee.LastName = "Hilton";

   // update it
   dao.Update(employee, connection);

   // delete another record
   dao.Delete(22);
}

The above example will achieve better performance, since it is only using 1 open connection for all calls, vs. 3 separate connections.

It's not always easy to remember the column names or table names for a database table. To make things easier, each generated DAO class contains constant values for the column names, table name and database name. This makes writing sql statements much easier. The following items are generated:

Below is an example for using these constants:

string sql = String.Format("SELECT {0}, {1}, {2} FROM {3} WHERE {4} LIKE 'm%'",
							EmployeesDAO.COLUMN_FIRSTNAME,
							EmployeesDAO.COLUMN_LASTNAME,
							EmployeesDAO.COLUMN_EMPLOYEEID,
							EmployeesDAO.TABLE_NAME,
							EmployeesDAO.COLUMN_FIRSTNAME);

string sql = String.Format("SELECT [{0}] FROM {3} WHERE [{4}] LIKE 'm%'",
							EmployeesDAO.COLUMN_FIRSTNAME,
							EmployeesDAO.TABLE_NAME,
							EmployeesDAO.COLUMN_FIRSTNAME);

SELECT [First Name] FROM Employees WHERE [First Name] LIKE 'm%'

Beyond the capabilities listed above for selecting records, etc., the DAO Framework has built in support for executing custom statements that don't return anything.

EmployeesDAO dao = new EmployeesDAO();
dao.ExecuteNonQuery("UPDATE FirstName SET FirstName = 'John' WHERE EmployeeID = 5");

The DAO Framework generates two classes:

Retrieving records is straight forward.

Selecting All Records

// Instantiate the DAO for the Northwind Employees table
EmployeesDAO dao = new EmployeesDAO();

// Returns all records stored in the Northwind Employees table
IList employees = dao.SelectAll();

// Iterate through each item and write it to the Console
foreach(EmployeesEntity employee in employees){
  Console.WriteLine(employee);
}

Selecting a Specific Record

// Instantiate the DAO for the Northwind Employees table
EmployeesDAO dao = new EmployeesDAO();

// Return Employee #2
EmployeesEntity employee = dao.SelectById(2);

// Write it to the console
Console.WriteLine(employee);

Selecting Specific Records by Foreign Key

// Instantiate the DAO for the Northwind Employees table
EmployeesDAO dao = new EmployeesDAO();

// Returns all employees that report to Employee #2.
IList employees = dao.SelectAllByReportsTo(2);

// Iterate through each item and write it to the Console
foreach(EmployeesEntity employee in employees){
  Console.WriteLine(employee);
}

Getting the Total Number of Records

// Instantiate the DAO for the Northwind Employees table
EmployeesDAO dao = new EmployeesDAO();

// Get the total number of records for the Employees table
int count = dao.Count;

Custom Queries

The DAO object exposes the underlying nHibernate API. Below is an example of a custom query using the nHibernate API directly. Please see nHibernate documentation for guidance on custom queries.

// Instantiate the DAO for the Northwind Employees table
EmployeesDAO dao = new EmployeesDAO();

// Get the total number of records for the Employees table using the nHibernate API
int count = 0;
ISession session = dao.ISessionFactory.OpenSession();
IList results = session.Find("select count(o.EmployeeID) from Test.EmployeesEntity as o");
if(results[0] != null) count = (int)results[0];
session.Close();

Modifying records is done easily via the Save and Delete methods.

Creating a new record

EmployeesDAO dao = new EmployeesDAO();

EmployeesEntity employee = new EmployeesEntity();
employee.FirstName = "John";
employee.LastName = "Doe";
// ... set the rest of the values

// Save the record
dao.Save(employee);

Editing an existing record

EmployeesDAO dao = new EmployeesDAO();

// Get employee #2
EmployeesEntity employee = dao.SelectById(2);

// Edit some values
employee.FirstName = "John";
employee.LastName = "Doe";

// Update the record
dao.Save(employee);

Deleting a record

EmployeesDAO dao = new EmployeesDAO();

// Get employee #2
EmployeesEntity employee = dao.SelectById(2);

// Delete the record
dao.Delete(employee);