Top 10 Must Have Features in O/R Mapping Tools

By : Iqbal Khan Jul 28, 2005

Printer friendly

Object-to-relational (O/R) mapping tools are becoming more popular each day and people are realizing the productivity gain they provide to developers. Yet, many people do not know enough about O/R mapping to consider using these tools and many others are weary of using any code generators (including O/R mapping tools).

In this article, I will try to educate you about the various important features that a good O/R mapping tool would provide you, and explain how it can be beneficial to you. I am not discussing any particular O/R mapping tool but rather all tools in general.

What Is O/R Mapping?

If you are developing an object-oriented application that requires a relational database, you will need to develop persistent objects that are modeled against your database design, and know how to interact with different tables in the database. You can either develop these objects by hand or use an O/R mapping tool to map and generate them quickly. I hope that after reading this article, you will be convinced that developing by-hand is a bad idea in most situations.

An O/R mapping tool connects to your database and reads its schema then lets you map persistent objects to database tables and views, and specify single-row transactional operations, queries, and stored procedure calls as methods to these objects. It also lets you define one-to-one, one-to-many, many-to-one, and many-to-many relationships between objects based on relationships in the database. It then generates fully working persistent objects code for you. Below is a simple example of some persistent objects. Please note that in this persistence design pattern as explained in "Domain Objects Persistence Pattern for .NET," the persistent objects are broken up into "Domain Objects" and "Factory Objects." You can read more about this design pattern if you wish.

/* Note one-to-many self relationship thru "reports_to" */
CREATE TABLE t_employees (
employee_id int IDENTITY (1, 1) NOT NULL,
name nvarchar (40) NOT NULL,
     birth_date datetime NULL,
photo image NULL,
reports_to int NULL,
)
// Domain object class for t_employees table

public class Employee
{
     Int32          _employeeId;
     String          _name;
     DateTime          _birthDate;
     Byte[]          _photo;
     Int32          _reportsTo;
     ArrayList          _subordinates;
     Employee          _supervisor;
Int32 EmployeeId {
          get { return _employeeId;} set { _employeeId = value; }
     }
String Name {
          get { return _name; } set { _name = value; }
     }
DateTime BirthDate {
          get { return _birthDate; } set { _birthDate = value; }
     }
Byte[] Photo {
          get { return _photo; } set { _photo = value; }
     }
Int32 ReportsTo {
          get { return _reportsTo; } set { _reportsTo = value; }
     }
ArrayList Subordinates {
          get { return _subordinates; } set { _subordinates = value; }
     }
Employee Supervisor {
          get { return _employee; } set { _employee = value; }
     }
}

// Persistent object for Employee class
public interface IEmployeeFactory
{
     void Load(Employee object, int nDepth);
     void Insert(Employee object);
     void Update(Employee object);
     void Delete(Employee object);
          // Query methods
     ArrayList FindSomeEmployees(); <![endif]>
          // Relationship methods
     void LoadSupervisor ( Employee emp);
     void LoadSubordinates(Employee emp, int nDepth);
}

Below is an example of how a client application will use this code:

public class NorthwindApp
{
     static void Main (string[] args) {
          Employee emp = new Employee();
          EmployeeFactory empFactory = new EmployeeFactory();

          // Let's load an employee from Northwind database.
          emp.EmployeeId = 10045;
          empFactory.load(emp);

          // empList is a collection of Employee objects
          ArrayList empList = empFactory.FindSomeEmployees();
          // subList is a collection of Employee's subordinates objects
          ArrayList subList = empFactory.LoadSubordinates(emp, 1);

          // supervisor is Employee's supervisor object
          Employee supervisor = empFactory.LoadSupervisor(emp);
     }
}