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.SelectOne(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.SelectOneDS(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

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: