I love working with MVC but I do have a hard time working with complex data with EntityFramework (EF). I heard of using inline SQL queries in MVC but don’t know how to use it and will it have any performance issues? Few month’s back I downloaded the StackOverflow (SO) Data Explorer which is an open-source project built on MVC to explore data dump provided by SO. The thing that took my attention in that code was that they have been SQL queries to get the data from the database. This looks promising to me as the guys at SO will never compromise with performance. SO Data Explorer uses the micro-ORM called Dapper which is authored by Sam Saffron of Stack Overlfow.
Getting Dapper
Dapper is also available on NuGet or you can also drop this single file in your project which is hosted at Google Code. You can use the below NuGet command to install Dapper.
Usage
Performing CRUD operations in EF is simple for simple scenarios, but when the required data is complex I prefer using SQL queries with joins and other related stuff which make my work far much easier. To demonstrate how to use Dapper to perform CRUD operations, I am building a simple application to manage contacts. I started up with creating a model named ContactModel. You can copy the below code or you can have one for yourself.
public class Contacts
{
[Key]
public int ContactID { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public string Email { get; set; }
public string Skype { get; set; }
}
The model is ready but we are not using EF Code First which created the database for us on the fly and therefore I have a bit more work to do. I’ll now create a database with a table which is same like my model. After the table has been created now I will add a new class to my project where I will write functions which will help me in performing CRUD operations. I will talk about view and controllers later when I am done with my DB access class. For this sample application I have used SQL CE 4.0 DB, but if you wish you can use SQL Server DB.
At the first, I just want to list down the contacts I have in the database. Practically this would have been the last thing to show in the example but I am showing this at first so you get an idea how Dapper works. As we have fresh database with no records, write down and insert query or just simply punch in the data in. Now in my data access class I will create a simple function which returns me the collection of contacts.
try
{
using (SqlCeConnection con = new SqlCeConnection(ConStr))
{
con.Open();
var contacts = con.Query("Select Name, Address, Email, Skype from Contacts");
return contacts;
}
}
catch (Exception x)
{
throw x;
}
The above functions returns the collection of the contacts which I will use in my Index view where I am going to list down all the contacts I have with me (See the SQL query :) ). Add a new controller or use the existing Home controller and add the below ActionResult method ListContacts.
public ActionResult ListContacts()
{
var db = new DB();
return View(db.GetContacts());
}
I now must have a view which will display the list of all the contacts. Right-click on ListContacts() method and choose Add View. A pretty familiar dialog pops up like the one below.
Nothing fancy here, just select the model class as the Contacts and Scaffold template as List. Hit F5 and change the URL to {View}/ListContacts and you will be able to see the list of contacts.
Performing INSERT/UPDATE and DELETE operations
To insert the data in the database create the below method in the DB access class:
public bool CreateContact(Contacts contact)
{
try
{
using (SqlCeConnection con = new SqlCeConnection(ConStr))
{
con.Open();
string sqlQuery = "INSERT INTO Contacts([Name],[Address],[Email],[Skype]) VALUES (@Name,@Address,@Email,@Skype)";
con.Execute(sqlQuery,
new
{
contact.Name,
contact.Address,
contact.Email,
contact.Skype
});
}
return true;
}
catch (Exception x)
{
return false;
}
}
I have a normal INSERT statement which will insert the records for me. The point that you need to note is that it is executed by the Execute extension method of the SqlCeConnection class. If you use SQL Server then the class will be SqlConnection class. Returning to controller and just to remind you that if we were working with EF which would have done the CRUD part for us then you must be aware that there are 2 ActionResult methods which were required for this purpose. One will just return the view and the other one with [HttpPost] attribute will create the record. I require a create view so I can add contacts. Follow the same steps you have followed above while creating the ListContacts view. For your further help here are the 2 ActionResult methods to create a new record (I am sure you can create a view!! Just right-click Create() method and click AddView and the scaffold is Create).
public ActionResult Create()
{
return View();
}
[HttpPost]
public ActionResult Create(Contacts contact)
{
var db = new DB();
bool isCreated = db.CreateContact(contact);
if (isCreated)
{
return RedirectToAction("ListContacts");
}
else
{
return View();
}
}
The process of updating a contact will be similar like we have done for the insert. Before updating a contact I need to get the details of the contact when the user clicks on the Edit link on the page. When the user clicks the Edit hyperlink on the list page, the contact ID will be paased as a parameter.
public Contacts GetContactByID(int ContactId)
{
using (SqlCeConnection con = new SqlCeConnection(ConStr))
{
con.Open();
string strQuery = string.Format("Select ContactID, Name, Address, Email, Skype from Contacts where " +
"ContactID={0}", ContactId);
var customer = con.Query<Contacts>(strQuery).Single<Contacts>();
return customer;
}
}
public bool UpdateContact(Contacts contact)
{
try
{
using (SqlCeConnection con = new SqlCeConnection(ConStr))
{
con.Open();
string sqlQuery = "UPDATE Contacts SET Name =@Name, Address =@Address, Address = @Address, " +
"Email = Email, Skype = @Skype WHERE ContactID=@ContactID";
con.Execute(sqlQuery, new
{
contact.Name,
contact.Address,
contact.Email,
contact.Skype,
contact.ContactID
});
}
return true;
}
catch (Exception)
{
return false;
}
}
The GetContactByID method will accept the contactId which is passed as a parameter to the Edit ActionResult method. I am calling this method from the Edit ActionResult. The main purpose of this method is to fetch the contact details on the basis of the ID passed to it. The returned Contacts serves as a model to the Edit view which then populates the details of the contact I want to edit. I assume here you know how to create a View. Here is my Edit and Edit with HttpPost which does the actual update work. The UpdateContact method will get called from the Edit ActionResult method which is attributed with HttpPost.
public ActionResult Edit(int ContactID)
{
var db = new DB();
return View(db.GetContactByID(ContactID));
}
[HttpPost]
public ActionResult Edit(Contacts contact)
{
var db = new DB();
db.UpdateContact(contact);
return RedirectToAction("ListContacts");
}
Following the same procedure that I followed for insert and update I am going to implement the delete functionality. The below code will be used to delete the contact details.
public bool DeleteContact(int ContactID)
{
try
{
using (SqlCeConnection con = new SqlCeConnection(ConStr))
{
con.Open();
string sqlQuery = "DELETE FROM Contacts WHERE ContactID=@ContactID";
con.Execute(sqlQuery, new { ContactID });
}
return true;
}
catch (Exception)
{
return false;
}
}
And for your ease, here are the 2 ActionResult methods.
public ActionResult Delete(int ContactID)
{
var db = new DB();
return View(db.GetContactByID(ContactID));
}
[HttpPost]
public ActionResult Delete(int ContactID, FormCollection frm)
{
var db = new DB();
bool IsDeleted = db.DeleteContact(ContactID);
if (IsDeleted)
{
return RedirectToAction("ListContacts");
}
else
{
return View();
}
}
This is it. A simple application where you can perform CRUD operations using SQL queries with micro-ORM Dapper.
Performance
Another reason for using a micro-ORM, Dapper in my case, but if you check the documentation you can see the table which clearly state the difference between the different ORMs.
There are other ORMs that you can try like the Massive ORM and PetaPOCO.