Executing SQL queries in a Controller Action

Question

I have 5 separate SQL queries that I am executing in order in a controller action. This is the method I am using to execute them:

var entity = new TestEntities();

entity.Database.ExecuteSqlCommand("//SQL Query");

So, basically I have five ExecuteSqlCommand in a row with different queries that must execute in order and the code must continue to execute below them. Is there a better way to execute queries from inside a controller action? I am not sure the best way to do error handling with this current method.

Thanks!

Answer 1

To start with look at creating a abstraction layer in between your controller and your database. An example of this is a repository, which will help when you come to testing. You can create mock repositories that you can use to in your unit tests rather than testing against your actual database.

You mention that you have 5 different database commands to fire. Look at the Unit of Work pattern which you can use to help track what you have changed and apply all of these changes to the database.

There is a good article on Microsoft's asp.net website about implementing the repository and unit of work.