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);
}
}