Introducing ADO.NET Entity Framework

This is the third and final part of my ORM series in which I am going to introduce the ADO.NET Entity Framework, an in-built Object Relational Mapping model of the .NET Framework.

Similar to the previous post, this one also covers the same four principles -

• Configuring a .NET project for the ADO.NET Entity Framework
• Inserting data from Objects directly
• Retrieving data using Object Lists an LINQ
• Changing the Database Management System

Though the screen-shots and the example codes emphasize on C#, the principles are same for all the .NET languages.

Requirements to run the example in this article - Visual C# Express, SQL Server Express and MySQL. Download Visual C# Express here, SQL Server Express here and MySQL here.

Configuring a .NET project for the ADO.NET Entity Framework

To configure a .NET project to work with the ADO.NET Entity Framework, an Entity Data Model is added to the project. The Entity Data Model is primarily a schematic representation of the database tables stored as a XML file. Each of these tables is converted into a class and foreign key relationships between these tables are maintained as Lists inside the objects.

Visual Studio provides a Wizard to create Entity Data Models. Right click on the project and select New Item from the Add menu. Choose the ADO.NET Entity Data Model template from the chooser. There are two approaches available to build the Entity Data Model - Database First Approach and the Model First Approach.

In the Database First Approach, the Entity Data Model is generated from existing table structures. The wizard allows the developer to setup a connection by providing the Database Server name and the Database name. The wizard also allows developers to choose tables, views and stored procedure that are to be a part of the Entity Data Model.

In the Model First Approach, the Entity Data Model is created from scratch and the database tables are generated based on this model.

Once the wizard completes, a designer opens up which shows a schematic representation of the generated .edmx file. This file contains the table mappings as a XML along with a code behind file with a .Designder.cs extension for the auto-generated classes corresponding to the tables. However a drawback with the Entity Data Model is that it combines all the classes into a single file which makes manual maintainence a little difficult.

I used the Database First approach to create the sample application to insert and retrieve data. You can get the MySQL and SQL Server scripts along with the Visual Studio solution here.

Inserting data from Objects directly

The Entity Model generates a class which inherits from ObjectContext. This class acts as a data manager to connect to the Database Management System to retrieve, insert, update and delete records.

ADO.NET Entity Framework maintains the records of the table as a List of objects. To insert new records into the table, create new objects, add them to the appropriate lists and then save the changes using a ObjectContext instance. The following piece of code shows how objects can be persisted -

using (Context context = new Context())
{
// Create the object
context.Objects.Add(object);
context.SaveChanges();
}

ADO.NET Entity Framework does a wonderful job while storing objects with foreign key dependencies. These dependencies are maintained using lists as objects and while storing these records, the appropriate identity keys are inserted into the child tables. However this feature is limited to a few DBMS like SQL Server.

Retrieving data using LINQ and Object Lists

The ADO.NET Entity Framework retrieves data from the back-end tables in the form of object lists. So accessing records is as simple as iterating through these lists.

using (Context context = new Context())
{
foreach (Object object in context.Objects)
// use the object appropriately
}

Since data retrieval is in the form of lists, developers can piggy-back on an other .NET framework feature - LINQ (Language Integrated Query). LINQ makes conditional querying of data a lot easier.

using (Context context = new Context())
{
var objects = from object in context.Objects where "condition" select object;
// use the objects
}

Changing the Database Management System

Before changing the DBMS of the Entity Model, it is important to understand how the ADO.NET Entity Framework stores the connection strings and the mapping between classes and the back-end tables. The connection string is stored in the App.Config file of the project and the table mappings are stored as a XML in the form of the .edmx file as mentioned earlier.

Unfortunately the ADO.NET Entity Framework varies it's implementation with the DBMS. Because of this modifying the XML manually isn't easy. For Example, ADO.NET Entity Framework does not support foreign key constraints in the form of lists for DBMS like MySQL.

The sample application contains another Entity Model which connects to a MySQL Server containing similar tables. To use MySQL with the ADO.NET Entity Framework, an connector is needed. The MySQL Connector/NET is available here.

Introducing Hibernate In Java Using NetBeans

In one of my recent posts, I introduced the theoretical topic of Object-Relational Mapping (ORM) - http://gautam-m.blogspot.com/2010/11/object-relation-mapping.html. In this post I am going to take a step forward and introduce Hibernate - an open source Java persistence framework from JBoss.

This post covers four basic principles of Hibernate -

• Configuring a Java project for Hibernate
• Inserting data using Object Persistence
• Retrieving data using Hibernate Query Language (HQL)
• Changing the database configuration to connect to another DBMS

Though the post and screen-shots emphasize on NetBeans, the concept is the same for all IDEs. Hibernate configuration files can definitely be written without an IDE but make sure all the required class libraries are properly referenced.

Requirements to run the example in the article - NetBeans, MySQL, JavaDB, Java and Hibernate. Java can be downloaded here, installing the All NetBeans package will cover JavaDB and Hibernate. and MySQL can be downloaded here.

Configuring a Java project for Hibernate

The crux of Hibernate is the creation and usage of configuration files. There are three types of configuration files which are to be setup for Hibernate -

1. The .cfg.xml file - this is the main configuration file which contains information about the database like the database URL, the driver, the username and password, etc. Hibernate can optimize it's behavior depending on the DBMS being used. To facilitate this, a property called Dialect is specified. However this is an optional property as Hibernate can deduce this depending on the JDBC metadata returned by the driver
2. The .reveng.xml file - this file holds the data corresponding to the schemas and tables being utilized by Hibernate in the application
3. The .hbm.xml - these files maps POJOs (Plain Old Java Objects) to the table schemas of the database

Typically one .cfg.xml and one .reveng.xml exist for a project and one .hbm.xml exists for each table (mapped to a class). The .hbm.xml maps the object properties to the table columns. It is possible to add new properties to the class which have no effect on the backend tables.

To create these files in a NetBeans project, select New File and select the following File Types from the Hibernate category -

1. Hibernate Configuration Wizard
2. Hibernate Reverse Engineering Wizard
3. Hibernate Mapping Files and POJOs from Database

Follow the wizards to complete the configuration setup. I used MySQL and JavaDB as my DBMS to create a sample application to insert and retrieve data. You can get the MySQL and JavaDB scripts along with the NetBeans project here.

Inserting data using Object Persistence

Inserting data is a cake-walk in Hibernate. All that is there to do is to create the object and store the object data in the database tables using the save method of a SessionFactory object. The following piece of code persists the object data -

SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
Session session = sessionFactory.openSession();

// Create the object
session.save(object);

Retrieving data using Hibernate Query Language (HQL)

Retrieving data is done through a query language designed for Hibernate called the Hibernate Query Language. HQL is a Object-Oriented Query Language and is very much similar to the traditional SQL we use. The beauty of HQL is that the result of the query is returned as a list of objects rather than as a ResultSet. These objects can be used directly in the code without any overheads. HQL is very wide topic, so I am going to skip the details here but there are several tutorials available for HQL on the Internet. The createQuery method of the above Session object is used along with the list method of the Query object to get the objects -

Query query = session.createQuery(queryString);
for (Object object : query.list()) {
// cast and use the object appropriately
}

Changing the database configuration to connect to another DBMS

The best feature of Hibernate according to me is it's ability to change a DBMS without any change to the application code. To change the DBMS, open the Hibernate Configuration File (typically hibernate.cfg.xml) and change the dialect, driver class, connection URL, username and password to the values corresponding to the new DBMS.

These changes can be done either through the design view or directly on the XML.