Every so often a new project starts up near me, and this project requires interacting with a database. Someone who has used an ORM (Object Relation Mapping) tool in the past suggests using it again. Whether it is nHibernate, LLBLGen Pro or Entity Framework, the person must have had a somewhat positive experience, or they wouldn’t suggest using again.
Almost as certainly someone who has traditionally put CRUD and some business logic in stored procedures and not used an ORM tool in the past fights the suggestion to use the ORM tool. They most likely have gotten good at writing and tweaking stored procedures and had a positive experience with this approach or they would not fight against the ORM tool. As usual there is no right or wrong way, just different approaches that may be more appropriate for the task at hand.
Why Use An ORM Tool:
The reason I suggest that ORM tools are the right choice most of the time is because most projects I see have aggressive time lines and limited development resources. To that end the ORM tool lets me write a lot less code to do the same amount of work. Take the below two methods, one gets all customers using Entity Framework 4 and the other gets the list manually using a SqlConnection, SqlCommand and SqlDataReader:
/// <summary>
/// Method using ORM To Get Customers
/// </summary>
/// <returns></returns>
public List<Customer> GetAllCustomersEF()
{
using (NorthwindEntities nwDB = new NorthwindEntities())
{
return (from cus in nwDB.Customers
select cus).ToList();
}
}
/// <summary>
/// Method using straight SQL to get all customers
/// </summary>
/// <returns></returns>
public List<Customer> GetAllCustomersSQL()
{
var retList = new List<Customer>();
string cmdText = "SELECT * FROM CUSTOMERS";
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[0].ConnectionString))
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
using (SqlDataReader dataRD = cmd.ExecuteReader())
{
while(dataRD.Read())
{
retList.Add(CreateCustomerFromDataReader(dataRD));
}
}
}
return retList;
}
/// <summary>
/// Method To Create Customer From Datareader record
/// </summary>
/// <param name="dataRD"></param>
/// <returns></returns>
private static Customer CreateCustomerFromDataReader(SqlDataReader dataRD)
{
Customer customer = new Customer();
customer.Address = dataRD["Address"].ToString();
customer.City = dataRD["City"].ToString();
customer.CustomerID = dataRD["CustomerID"].ToString();
customer.CompanyName = dataRD["CompanyName"].ToString();
customer.ContactName = dataRD["ContactName"].ToString();
//.. repeat until all fields full
return customer;
}
As you can see above there is a lot less code required using Entity Framework 4. The data class, Customer, is actually code genned for me. And the projection into the object from the database is also done for me. All I have to do is write a query using LINQ syntax.
To get the data using a SQLCommand I have write the query in SQL either inline (as is done above) or in a stored procedure. I have to then iterate through the results and fill a Customer class of my own making, so I have to create and maintain the code for projecting the data from the database into my objects.
The above is a very simple case, add filtering or prefetching data in the object graph and the amount of extra code I have to write in the straight sql solution grows exponentially compared to the Entity 4 solution. Below is possible code for grabbing all customers but with their orders also.
/// <summary>
/// Method using ORM To Get Customers
/// </summary>
/// <returns></returns>
public List<Customer> GetAllCustomersWithOrdersEF()
{
using (NorthwindEntities nwDB = new NorthwindEntities())
{
return (from cus in nwDB.Customers.Include("Orders")
select cus).ToList();
}
}
/// <summary>
/// Method using straight SQL to get all customers
/// </summary>
/// <returns></returns>
public List<Customer> GetAllCustomersWithOrdersAllDataSQL()
{
var retList = new List<Customer>();
string cmdText = "SELECT * FROM CUSTOMERS"
+ " LEFT JOIN ORDERS "
+ " ON CUSTOMERS.CustomerID = ORDERS.CustomerID Order By Customers.CustomerID";
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[0].ConnectionString))
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
using (SqlDataReader dataRD = cmd.ExecuteReader())
{
Customer customer = null;
string customerID = "";
while(dataRD.Read())
{
if (customerID != dataRD["CustomerID"].ToString())
{
if (customer != null)
{
retList.Add(customer);
}
customer = CreateCustomerFromDataReader(dataRD);
}
customer.Orders.Add(CreateOrderFromDataReader(dataRD));
}
if (customer != null)
{
retList.Add(customer);
}
}
}
return retList;
}
private Order CreateOrderFromDataReader(SqlDataReader dataRD)
{
Order retOrder = new Order();
///...fill order
///
return retOrder;
}
/// <summary>
/// Method To Create Customer From Datareader record
/// </summary>
/// <param name="dataRD"></param>
/// <returns></returns>
private static Customer CreateCustomerFromDataReader(SqlDataReader dataRD)
{
Customer customer = new Customer();
customer.Address = dataRD["Address"].ToString();
customer.City = dataRD["City"].ToString();
customer.CustomerID = dataRD["CustomerID"].ToString();
customer.CompanyName = dataRD["CompanyName"].ToString();
customer.ContactName = dataRD["ContactName"].ToString();
//.. repeat until all fields full
return customer;
}
To add the prefetch of Orders in the straight SQL solution I have to write code to project into my customer than check each row to see if the customer changes, and then add orders to the customer. This supposes I do not mind that the customer data is duplicated for each order returned for the same company. If I want to optimize data transfer things get even more complex.
Even in a fairly simple example I have a lot less code to write and maintain using the ORM solution. When the database schema changes this means I have less code I’ll have to update to accommodate a schema change. If the ORM is statically typed, as is Entity Framework 4 and LLBL Gen Pro, then I’ll also have the benefit of seeing where my code breaks when the schema is changed at compile time. The less code I have to write by hand and alter when I make changes to my data model the better, and the easier these changes are to find the better.
ORM tools have not always been so friendly to data model changes. nHibernate uses an XML mapping file, or used to. Up until Entity Framework4 synchronizing changes to your schema into code was not that straightforward. LLBL Gen Pro code generates its data layer so handles changes to the schema pretty well, but was hard to learn to use. Even given these earlier drawbacks, I would still argue they allow more rapid development with limited resources available.
Also, ORM tools allow you to rely on the expertise of those who wrote them when it comes to creating the SQL that is actually run on the database. If you have a DBA and are confident that you can tweak your own SQL effectively this isn’t an issue. More and more, however, I run into projects where no one has a very deep SQL background.
Why Use Straight SQL:
None of this is to say that straight SQL does not have its place. I always use stored procedures in reporting oriented applications, as their needs are heavily data processing intensive. In circumstances where performance is the highest priority and time line and available resources are not an issue then the straight SQL solution is better suited. If you know what you are doing you can achieve higher performance with your own code. However, you have to know what you are doing. Writing your own data layer does not guarantee better performance.
Conclusion:
As the title suggests I believe an ORM tool is the best choice most of the time. Most projects have short time frames and limited resources. Where performance is the absolute top priority or you have heavily data processing tasks in the application then a straight SQL approach may make sense. Even in these cases I might suggest a hybrid approach, where straight SQL is used only where required, so areas of the application that don’t require heavy data processing or top performance are easier to build and maintain.