Tools

The Impedance Mismatch
If you’ve spent more than two months developing any form of software, chances are you’ve had to program against a database. Unfortunately, despite the rapid gains in software sophistication over the past decade, few well-recognized tools are available to deal with the problem of object-relational mapping, often referred to as the “impedance mismatch.” To quote Scott Ambler:

The object-oriented paradigm is based on proven software engineering principles. The relational paradigm, however, is based on proven mathematical principles. Because the underlying paradigms are different the two technologies do not work together seamlessly.

The impedance mismatch becomes apparent when you look at the preferred approach to access: with the object paradigm you traverse objects via their relationships whereas with the relational paradigm you join the data rows of tables. This fundamental difference results in a non-ideal combination of object and relational technologies…”

Fortunately, tools for object-relational mapping have started to gain prevalence. Java has excellent open-source projects such as Hibernate and Apache’s OJB. Likewise, .NET has started to see growth in the ORM field with frameworks such as nHibernate and Gentle.NET, and in commercial applications such as Wilson ORMapper and LLBLGen Pro. Object-Relational Mappers tend to take three primary forms:

• Code Generators: Code Generators are applications that inspect the relationships between tables and generate the necessary code to perform all database operations.  
• They normally are based on templates that can be modified by the developer.
• Run-Time Mappers: Run-time mappers utilize some form of ORM definitions, usually in the form of XML files. At run time, the ORM framework brokers calls between your objects and the database, creating all of the necessary SQL code. These XML mappings specify which objects should be loaded, what properties should be loaded, etc.
• Design-Time Mappers: Design-time mappers look for compiled information on mappings, normally in the form of attributes. These attributes specify which field a property maps to, etc.

There are pros and cons for each approach. One of the benefits that code generation has over the other two forms is that run-time performance normally improves since you can avoid internals like dynamic IL generation and/or run-time reflection. Additionally, code generators normally give you a better idea of how the framework operates and simplify the modification process. However, what form of ORM you use should depend on your specific situation.

Codus
A good example of an ORM tool is Codus, which is free and open source. Codus is a code generator, but due to its template-based nature, it can be used to generate other forms of mappers such as nHibernate XML mappings or Gentle.NET attributes.

The current version of Codus includes an out-of-the-box DAO Framework that generates all required ORM code, strongly typed collections, unit tests with mock objects, Web services, NAnt build files, and an automatically compiled VS.NET 2003 solution. Additional features include XML documentation, the creation of child properties (such as Customer.Orders), various foreign-key methods, and native ADO.NET functionality such as retrieving DataSets. Plugging in new frameworks is quite easy, since all generated code uses the NVelocity template language for its template definitions. Future versions will automatically generate mappings for frameworks such as nHibernate, iBatis, and Gentle.NET. What follows is a summary of the major pieces of Codus.



GUI
The GUI is a straightforward user interface that streamlines the generation process. It consists of a Project tab, which allows you to define your output location and database connection strings; a Tables tab, which allows you to select which tables and columns you want to generate and modify their properties (for example, setting a column to be a foreign key, or changing its name); and an Output tab, which is where you select which templates and features to generate. The GUI is multithreaded to improve performance and usability.

SchemaBuilder
One of the more difficult aspects of ORM is simply obtaining the relational information, which can then be used to generate code. The .NET framework provides two different ways to do this – via the OleDbDataReader:: GetSchemaTable method and the OleDbConnection:: GetOleDbSchemaTable method. Unfortunately, neither of these methods offers a full view of the database schema. Additionally, the GetSchemaTable call returns a weakly typed DataTable, which makes it difficult to retrieve all of the information you need. To resolve this, Codus includes a SchemaBuilder class that aggregates all schema information and builds up a DatabaseSchema object, which contains TableSchema objects.

SQL Generator
Because Codus works with different database platforms, to include SQL Server, Access, and Oracle (with mySQL, DB2, and Firebird around the corner), it was necessary to write a database-neutral, object-oriented API to generate code. This allows for easy future extensions without modification to the generation engine (see Listing 1).

   
Likewise, an interface-based approach to ADO.NET was developed to decouple database code from the underlying implementations (see Listing 2). Using these two techniques, it becomes extremely easy to integrate new databases into Codus since all that’s needed is the underlying implementations for the code to generate correctly. No code changes to the base engine are needed.

Template Engine
The template engine is based on the excellent NVelocity template language, which was ported from Apache’s Velocity framework. NVelocity is simple to use – all you need to do is place an object in “context” with an associated key, and you can then access that object in your template. Additionally, it has common language features such as conditional logic, the ability to set variables, etc. The Codus engine provides several objects in the template context, to include the full DatabaseSchema object, along with several helper objects. Listing 3 shows an example of the NVelocity language.
   
Originally, CodeDOM was considered for the code generation, and the earliest version of Codus used CodeDOM. Unfortunately, CodeDOM has a very verbose API and the development cycle was overly complicated. Most important, any changes had to be recompiled. With NVelocity, the templates are easy to read and changes simply require opening a file. The next time the generation engine runs, the latest template version is used.



VS.NET Integration
The most difficult part of Codus was developing the VS.NET integration framework. Unfortunately, VS.NET integration is via a COM-interop and is poorly documented. To further complicate things, Codus is multithreaded, which causes issues when dealing with VS.NET Automation.
  
To make life simpler, a reusable component is embedded within Codus that handles the creation of VS.NET 2003 solutions. The configuration of the solution is driven by a single XML file that defines all of the templates, how they should be processed, and what type of project they should be added to. This allows for new templates and template projects to be added without requiring any modifications to the underlying engine or to the VS.NET Automation component.

The DAO Framework
As mentioned previously, Codus comes with a default template called DAO Framework, which generates all code in C#. The DAO Framework is built on the Data Access Object (DAO)/Transfer Object (TO) design pattern. A Data Access Object is generated for each table, with a corresponding Entity object that is returned containing table information. The TO is Serializable, and the DAO is a MarshalByRefObject, which allows you to use the framework in a distributed environment.
   
Using the framework is very straightforward. Listing 4 shows some code samples that were generated for the Northwind database.

Roadmap
Codus is still in the early phases, with the first release out in January of this year. Moving forward, several new features are planned which should include support for other leading ORM frameworks, support for all major databases, and several improvements to the DAO Framework to include object caching and lazy loading. At any time you can download the existing application and code base from www.adapdev.com/codus.

Resources

• Codus homepage: www.adapdev.com/codus
• NVelocity homepage: nvelocity.sourceforge.net

Listing 1: Using the ISelectQuery interface

// Creates a sql-server specific SELECT statement 

// using OLEDB syntax

ISelectQuery query = QueryFactory.CreateSelectQuery 

	(DbType.SqlServer, DbProviderType.OleDb);

query.SetTable(“Employees”);

query.Add(“EmployeeId”);

query.Add(“FirstName”);

// Outputs SELECT [EmployeeID], [FirstName] FROM 

// [Employees]

Console.WriteLine(query.GetText());

// Now, add WHERE criteria

ICriteria criteria = query.CreateCriteria();

criteria.AddEqualTo(“EmployeeId”);

query.SetCriteria(criteria);

// Outputs SELECT [EmployeeID], [FirstName] FROM 

// [Employees] WHERE EmployeeId = ?

// If we had set the DbProviderType to 

// DbProviderType.SqlServer, it would instead use the

// Sql Server  syntax:

// SELECT [EmployeeID], [FirstName] FROM [Employees] //        

	WHERE EmployeeId = @EmployeeId

Console.WriteLine(query.GetText());

// Now we’ll set a specific value for the WHERE 

// clause:

ICriteria criteria = query.CreateCriteria();

criteria.AddEqualTo(“EmployeeId”, 1);

query.SetCriteria(criteria);

// Outputs SELECT [EmployeeID], [FirstName] FROM 

// [Employees] WHERE EmployeeId = 1

Console.WriteLine(query.GetText());

Listing 2: Codus utilizes ADO.NET interfaces for database independence

// Creates an OleDbConnection

IDbConnection connection = DbProviderFactory.CreateConnection
	
	(DbProviderType.OleDb);

connection.ConnectionString = “some connection...”;

Listing 3: The NVelocity language

## This code iterates through each ColumnSchema object contained in 

the TableSchema object,

## which was passed into context.  For each column it creates a 

property using the columns .NET Type (DateTime, String, etc.)

## and its name (Employees, etc.).  The $column properties such 

as Alias and MemberName are properties of the

## ColumnSchema object and can be accessed by NVelocity.  

Calling them will simply spit out their values.

## NVelocity can also call methods, etc.

#foreach($column in $tableschema.SortedColumns.Values)

        public $column.NetType $column.Alias {

            get {

                return this.${column.MemberName};

            }

            set {

                this.${column.MemberName} = value;

            }

        }            

#end

Listing 4: Using the Codus-generated code for Northwind

// Get all records and print out the Employees first // name

EmployeesDAO dao = new EmployeesDAO();

IList employees = dao.SelectAll();

foreach(EmployeesEntity e in employees){

  Console.WriteLine(e.FirstName);

}

// Create a new record and save

EmployeesEntity employee = new EmployeesEntity();

employee.FirstName = “Joe”;

employee.LastName = “Schmoe”;

// ...etc.

EmployeesDAO dao = new EmployeesDAO();

dao.Save(employee);

// Populate a DataSet

EmployeesDAO dao = new EmployeesDAO();

DataSet employeesDS = dao.SelectAllDS();

// Get the number of records

EmployeesDAO dao = new EmployeesDAO();

int count = dao.GetCount();

Add Your Feedback

In order to post a comment you need to be registered and logged in.

Register | Log in

Please wait while we process your request...