Author: Iqbal M. Khan
O/R mapping tools are becoming more popular each day and people are realizing the productivity gain they provide to developers. Yet, many people don't 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 how it can be beneficial to you. I am not discussing any particular O/R mapping tool but rather all tools in general.
If you're developing an object oriented application that requires a relational database, you'll need to develop persistence 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. Hopefully, after reading this article, you'll 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 persistence objects to database tables and views, specify single-row transactional operations, queries, and stored procedure calls as methods to these objects. And, 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 persistence objects code for you. Below is a simple example of some persistence objects. Please note that in this persistence design pattern as explained in Domain Objects Persistence Pattern for .NET, the persistence 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; }
}
}
// Persistence 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 ();
// 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 a 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);
}
}Everything in O/R mapping starts with mapping your objects to your relational tables. Here are some specific features in this area that you should know:
As you saw, a popular design pattern separates persistence objects into "domain" and "factory" objects. One important O/R mapping feature is to let you decide whether you want to generate both domain and factory objects or use your existing domain objects and only generate factory objects that know about your domain objects.
Some people do not want to generate "domain" objects and instead develop them by hand and only generate the "factory" objects. The reason behind this is that their domain objects are being used in almost all subsystems of their application and therefore they don't want them changing frequently through subsequent code regenerations. But, they don't mind generating the "factory" objects since their use is localized to a few places (for load and save operations).
Therefore, the O/R mapping tool should let you use your existing domain objects and map and generate only the factory objects. It should use .NET Reflection to read your domain object definition and after you have done the mapping, it should generate the factory objects in such a way that these factory objects use your domain objects to hold all the data.
A database transaction allows you to group multiple operations as one atomic operation so either all operations succeed or none of them succeed. Transactional operations include create, read, update, and delete (also called insert, update, load, and delete). Each transaction operation is performed only on one row of data in a table.
You'll be working in one of two main transactional environments and your O/R mapping tools needs to know both of them so it can generate code accordingly. They options are:
The foundation of a relational database is that tables have relationships with other tables. Similarly, when you map objects to these tables, your objects also need to establish the same relationships with other mapped objects. Therefore, your O/R mapping tool must support this very important feature by letting you determine which relationships you want to keep in your objects. Below are the different types of relationships you must have:
As you already know, a very important aspect of object-oriented programming is inheritance. However, relational databases do not automatically provide inheritance in the relational model. But, there are a number of patterns on how to map object inheritance to a relational database. And, a good O/R mapping tool must provide this capability.
Here are a few ways in which object inheritance is mapped to relational databases.
The next most common thing that a database application does is to retrieve rows of data from one or more tables. The application does this done by using SQL queries (SELECT statements). However, an object-oriented application wants to fetch a collection of objects and not rows. So, the O/R mapping tool must provide a way for you to create queries that return collections of objects.
Static queries are those that are defined at compile time and the only thing that changes at runtime for them are the parameter values. These queries can be precompiled and run very efficiently. So, the O/R mapping tool must allow you to define static queries as methods of your objects and also specify whether these queries take any run-time parameters or not.
Dynamic queries on the other hand are those where either the query or its criteria is created at runtime. These queries cannot be precompiled and must run as "Dynamic SQL". However, the benefit of these queries is that they allow those situations in your application where you're performing ad hoc search operations and based on the user input you determine what the query should look like. These queries need to also be provided as methods to your objects but with the flexibility that you can specify the "WHERE clause" and "ORDER BY clause" at run-time.
Stored procedures have become very popular in high transaction environments because they allow you to put all your SQL inside the DBMS and in a compiled form. As a result, your SQL does not have to be compiled at runtime because that is a very expensive process. There are two situations that an O/R mapping tool must support when it comes to stored procedures as described below:
If your application is transaction intensive and supports high traffic, you really cannot live without effective caching built into your application. Microsoft provides ASP.NET Cache object but it is not sufficient for clustered environments where your application is running on multiple servers and needs a cache that is also clustered. However, there are commercial caching solutions available in .NET that cater for clustered environments.
Whichever caching product you use, you'll have to make sure that your persistence objects are making caching calls from appropriate locations. And, your O/R mapping tool should provide the ability to generate code that makes caching calls to one or more leading products.
Ideally, your O/R mapping tool should let you specify which objects you want to cache and which ones you do not want to cache. The most popular situation is where transactional objects (single row objects) are cached. However, you can also cache entire collections and even related objects
You'll always have situations where you need to customize generated code. However, if you change the generated code, it will most likely get overwritten the next time you generate code again. And, since software development is an iterative process, you'll have to generate code many times.
Additionally, whatever custom code you write must be called when the generated code is run. And, it must also be able to control the subsequent execution of the generated code. For example, if your custom code is called before doing an "Insert" and you find something wrong, you should be able to prevent the "Insert" from actually happening.
To prevent your code from being overwritten, the O/R mapping tool must allow you to mark your code as "Safe Code" which then does not get overwritten in future code regenerations. And, to ensure that your custom code gets called seamlessly, the O/R mapping tool needs to either support the concept of "Hooks" which are calls made from strategic places in the generated code and the result code returned by these "Hooks" determines what happens next. Or, the O/R mapping tool needs to let you derive the generated code and then use polymorphism to actually run your code instead of the generated code. You can then determine whether to call the "base class code" or not.
A good O/R mapping tool is very likely using code templates to determine how to generate the code. The O/R mapping tool combines the templates it has with a combination of your object mapping input and the database schema information to determine exactly how to generate the code.
Since the O/R mapping tool is generating code from templates, it would be great if it let you modify these templates (or add new templates) so you could affect how the generated code should look.
A very simple example would be when you would like to put your own copyright header in each source code file. If you could go an insert this header in the code template file, it would automatically get used next time you generate code. You should also be able to write your own code templates (although this is only for advanced users) and let the O/R mapping tool use your templates but do everything else the same way as it always does.
You should seriously consider using an O/R mapping tool as it will save you a lot of development and testing time. And, when it comes to evaluating which tool is best for you, you should know what to look for. I hope this article helps you gain a better understanding of O/R mapping.
Author: Iqbal M. Khan works for Alachisoft , a leading software company providing .NET and Java distributed caching, O/R Mapping and SharePoint Storage Optimization solutions. You can reach him at iqbal@alachisoft.com.