Map your self using ORM

Many of you must know this word, "ORM". Then why this post? But trust me, even though we have lots and lots of ORMs in the market and some companies are still using age-old technology. Just a few days back at one company, I had to face the same old question about connection class and command class. Why?????????? It is so true that one must know these things, but this question has always been a nightmare for me.

When I had Java advanced as a subject, I used to do everything other than JDBC connection. I had never completed a single database connection using JDBC. And maybe that is the reason why I haven't chosen a career with Java. I am not a Ninja anymore. It is my unfulfilled dream!!!

Let's come back to .NET. We used to have connection and command classes rule the arena in .NET. Every interview starts with that and becomes a blast because of that. Questions start with

What is the connection class? And what are the uses of it?

What is the command class?

Difference between dataset and datareader?

Sometimes I do wonder if I was going to be selected for making applications or just "connecting to" the database class. And then we had a few more options to connect to the database. A few other questions which are still unanswered, like

Where should I put the connection string? In web.config or in a single class?

How much time should I allow the connection to be open?

How can I use connection pooling?

I still don't know the answer to the last question. And the problem doesn't end there. We need to implement design patterns, had to implement interfaces to follow DRY. And what actually happened is that we became tired of doing all this stuff. At least I became tired, pushing myself more for technical stuff rather than concentrating on giving output. Microsoft tried to help us by pushing libraries like DAAB. But the open source community is still far ahead of this. Java had Hibernate, and then comes its .NET port, nHibernate. And then Microsoft comes with LINQ to SQL.

All these are ORMs. The full form of ORM is Object Relational Mapping. What it does is map the database table with the equivalent object. This makes CRUD operations easy as anything. And internally it takes care of everything starting from connection lifecycle to generating the equivalent query for that.

So now I don't worry about connections and commands but work more on business logic. Yes, of course I am saving the name Entity Framework for my example of an ORM. It is the final blow from Microsoft. And to do RAD type development, it is the best product till now from Microsoft, even better than Web Forms.

Now, the example. I am using the age-old Northwind database. Also using SQL CE. Now, what you need to do is add a new file and choose the data option, find the Entity file and add it. Choose the northwind file and "next next next". Just don't forget to choose all database tables. That's it, it's done.

I am a lazy person by default. So, I am using MVCScaffolding to generate classes. You can do as you wish.

So, what I have done here is add an Entity Framework file (dbml file). Which is an XML file which shows the mapping between database and object. Then I fired a scaffold command. That's it. After this I got an error.

Basically, the Scaffold command is designed for EF Code First by default, and I haven't customized it for simple Entity Framework. So, I had to modify some of the code, and that's it. It is done and working perfectly. Here is the code snippet with details.

I have taken the customer table for an example.

Created an interface for basic CRUD operations.

public interface ICustomerRepository { 
IQueryableCustomer All { get ; } 
IQueryableCustomer AllIncluding( params ExpressionFuncCustomer , object[] includeProperties); 
Customer Find( string id); 
void InsertOrUpdate( Customer customer); 
void Delete( string id); 
void Save(); 

}

and then create the repository class to do the task.

public class CustomerRepository : ICustomerRepository { 
NorthwindEntities context = new NorthwindEntities (); 
public IQueryableCustomer All { get { return context.Customers; } } 
public IQueryableCustomer AllIncluding( params ExpressionFuncCustomer , object [] includeProperties) 
  { 
    IQueryableCustomer query = context.Customers; 
    foreach ( var includeProperty in includeProperties) 
      { query = query.Include(includeProperty); } 
    return query; 
  } 
public Customer Find( string id) { 
  return context.Customers.Where(c => c.Customer_ID == id).FirstOrDefault(); 
} 
public void InsertOrUpdate( Customer customer) { 
  var _customer = Find(customer.Customer_ID); 
  if (_customer != null ) { 
    _customer.Address = customer.Address; 
    _customer.City = customer.City;   
    _customer.Company_Name = customer.Company_Name; 
    _customer.Contact_Name = customer.Contact_Name; 
    _customer.Contact_Title = customer.Contact_Title; 
    _customer.Country = customer.Contact_Title; 
    _customer.Fax = customer.Fax; 
    _customer.Phone = customer.Phone; 
    _customer.Postal_Code = customer.Postal_Code; 
    _customer.Region = customer.Region; 
    // Haven't taken orders in update just for demo purpose only 
  } else { 
    context.Customers.AddObject(customer); 
  } 
} 
public void Delete( string id) { 
  var customer = Find(id); 
  context.Customers.DeleteObject(customer); 
} 
public void Save() { 
  context.SaveChanges(); 
} 

}

we add one line to application start to solve dependency. kernel.Bind

ICustomerRepository().ToCustomerRepository(); 

Now, we have basic methods in our customer's controller to do all the basic operations.

public class CustomersController : Controller { 
private readonly ICustomerRepository customerRepository; 
public CustomersController( ICustomerRepository customerRepository) { 
  this .customerRepository = customerRepository; 
} 

// GET: Customers 
public ViewResult Index() { 
  return View(customerRepository.AllIncluding(customer.Orders)); 
} 

// GET: /Customers/Details/5 
public ViewResult Details( string id) { 
  return View(customerRepository.Find(id)); } 

// GET: /Customers/Create 
public ActionResult Create() { 
  return View(); 
} 

// POST: /Customers/Create 
[ HttpPost ] 
public ActionResult Create( Customer customer) { 
  if (ModelState.IsValid) { 
    customerRepository.InsertOrUpdate(customer); customerRepository.Save(); 
    return RedirectToAction( "Index" ); 
    } else { 
      return View(); 
    } 
  } 

  // GET: /Customers/Edit/5 
public ActionResult Edit( string id) { 
  return View(customerRepository.Find(id)); 
} 

// POST: /Customers/Edit/5 
[ HttpPost ] 
public ActionResult Edit( Customer customer) { 
  if (ModelState.IsValid) { 
    customerRepository.InsertOrUpdate(customer); customerRepository.Save(); 
    return RedirectToAction( "Index" ); 
  } else { 
    return View(); 
  } 
} 

// GET: /Customers/Delete/5 
public ActionResult Delete( string id) { 
  return View(customerRepository.Find(id)); } 

// POST: /Customers/Delete/5  
[HttpPost ActionName ( "Delete" )] 
public ActionResult DeleteConfirmed( string id){ 
  customerRepository.Delete(id); 
  customerRepository.Save(); 
  return RedirectToAction( "Index" ); 
} 

}

Now, you just need views according to the Action Results.

I have shared a project on Github at my repository. So, do check it out for any missing code. You can check it out for a Demo Application there.

See, no connection, no command, just clean, beautiful code and the work is done. Entity Framework will take care of everything else.

We can use any other ORMs too. One of my favorites is Orchard, which is using NHibernate. It is far more mature than any other ORM in .NET. And there are lots and lots of features to work with. I will work on it in my next articles.

One more thing to add: it takes more time to copy-paste the code than to create it. So, you can imagine how fast it is!!!