Welcome!

Microsoft Cloud Authors: Pat Romanski, Andreas Grabner, Nick Basinger, Kevin Benedict, Liz McMillan

Related Topics: Microsoft Cloud

Microsoft Cloud: Article

Microsoft .NET - Object-Relational Mapping with Codus

Let Codus do the work, while you do the thinking

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

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

More Stories By Sean McCormack

Sean is a C# MVP and serves as senior systems analyst for Miller Brewing Co., leading their .NET Architecture team. His previous experience includes serving as the director of technology for an eCommerce company and a touch-screen voting company, lead developer for an enterprise content management company, technical manager for a national automotive portal, and as an Arabic linguist and cryptologist for the Department of Defense. Sean currently leads the Adapdev open-source foundation, helping to develop top-notch open source applications for the .NET community. His two most recent applications include Zanebug (a unit testing tool) and Codus (a code generation tool).

Comments (2)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


IoT & Smart Cities Stories
Codete accelerates their clients growth through technological expertise and experience. Codite team works with organizations to meet the challenges that digitalization presents. Their clients include digital start-ups as well as established enterprises in the IT industry. To stay competitive in a highly innovative IT industry, strong R&D departments and bold spin-off initiatives is a must. Codete Data Science and Software Architects teams help corporate clients to stay up to date with the mod...
There are many examples of disruption in consumer space – Uber disrupting the cab industry, Airbnb disrupting the hospitality industry and so on; but have you wondered who is disrupting support and operations? AISERA helps make businesses and customers successful by offering consumer-like user experience for support and operations. We have built the world’s first AI-driven IT / HR / Cloud / Customer Support and Operations solution.
At CloudEXPO Silicon Valley, June 24-26, 2019, Digital Transformation (DX) is a major focus with expanded DevOpsSUMMIT and FinTechEXPO programs within the DXWorldEXPO agenda. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term. A total of 88% of Fortune 500 companies from a generation ago are now out of business. Only 12% still survive. Similar percentages are found throug...
Druva is the global leader in Cloud Data Protection and Management, delivering the industry's first data management-as-a-service solution that aggregates data from endpoints, servers and cloud applications and leverages the public cloud to offer a single pane of glass to enable data protection, governance and intelligence-dramatically increasing the availability and visibility of business critical information, while reducing the risk, cost and complexity of managing and protecting it. Druva's...
BMC has unmatched experience in IT management, supporting 92 of the Forbes Global 100, and earning recognition as an ITSM Gartner Magic Quadrant Leader for five years running. Our solutions offer speed, agility, and efficiency to tackle business challenges in the areas of service management, automation, operations, and the mainframe.
The Jevons Paradox suggests that when technological advances increase efficiency of a resource, it results in an overall increase in consumption. Writing on the increased use of coal as a result of technological improvements, 19th-century economist William Stanley Jevons found that these improvements led to the development of new ways to utilize coal. In his session at 19th Cloud Expo, Mark Thiele, Chief Strategy Officer for Apcera, compared the Jevons Paradox to modern-day enterprise IT, examin...
With 10 simultaneous tracks, keynotes, general sessions and targeted breakout classes, @CloudEXPO and DXWorldEXPO are two of the most important technology events of the year. Since its launch over eight years ago, @CloudEXPO and DXWorldEXPO have presented a rock star faculty as well as showcased hundreds of sponsors and exhibitors! In this blog post, we provide 7 tips on how, as part of our world-class faculty, you can deliver one of the most popular sessions at our events. But before reading...
DSR is a supplier of project management, consultancy services and IT solutions that increase effectiveness of a company's operations in the production sector. The company combines in-depth knowledge of international companies with expert knowledge utilising IT tools that support manufacturing and distribution processes. DSR ensures optimization and integration of internal processes which is necessary for companies to grow rapidly. The rapid growth is possible thanks, to specialized services an...
At CloudEXPO Silicon Valley, June 24-26, 2019, Digital Transformation (DX) is a major focus with expanded DevOpsSUMMIT and FinTechEXPO programs within the DXWorldEXPO agenda. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term. A total of 88% of Fortune 500 companies from a generation ago are now out of business. Only 12% still survive. Similar percentages are found throug...
Scala Hosting is trusted by 50 000 customers from 120 countries and hosting 700 000+ websites. The company has local presence in the United States and Europe and runs an internal R&D department which focuses on changing the status quo in the web hosting industry. Imagine every website owner running their online business on a fully managed cloud VPS platform at an affordable price that's very close to the price of shared hosting. The efforts of the R&D department in the last 3 years made that pos...