Playing with dapper Micro ORM and ASP.NET MVC 3.0

Some time ago Sam Saffron a lead developer from stackoverflow.com has made dapper micro ORM open source. This micro orm is specially developed for stackovewflow.com for keeping performance in mind. It’s very good single file which contains some cool functions which you can directly use in your browser. So I have decided to have a look into it. You can download dapper code from the following location it’s a single static class file called SQLMapper.

http://code.google.com/p/dapper-dot-net/

So once you download that file you can use that file into your project. So I have decided to create a sample application with asp.net mvc3. So I have created a simple asp.net mvc 3 project called DapperMVC. Now let’s first add that SQLMapper class file into our project at Model Folder like following.

SQLMapper

Now let’s first create sample table from which we will fetch the data with the help of dapper file. I have created sample customer table with following script.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer](
 [CustomerId] [int] NOT NULL,
 [FirstName] [nvarchar](50) NULL,
 [LastName] [nvarchar](50) NULL,
 [Address] [nvarchar](256) NULL,
 [City] [nvarchar](50) NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
 [CustomerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Once I have created table I have populated some test data like following.

TestData

Now we are ready with database table Now its time to add a customer entity class. So I have created sample customer class with properties same as Database columns like following.

public class Customer
{
 public int CustomerId { get; set; }
 public string FirstName { get; set; }
 public string LastName { get; set; }
 public string Address { get; set; }
 public string City { get; set; }
}

Now we are done with the Customer Entity class then I have created a new class called CustomerDB and a created a GetCustomer Method where I have used Query Method of Dapper to select all customers with ‘select * from customer’ simple query. I know it’s not a best practice to write ‘select * from customer’ but this is just for demo purpose so I have written like this. Query method accepts query as parameter and returns IEnumerable<T> where T is any valid class. In our case it will be Customer which we have just created before. Following is the code for CustomerDB Class.

using System.Collections.Generic;

public class CustomerDB
{
 public string Connectionstring=@"Data Source=DotNetJalps\SQLExpress;Initial Catalog=CodeBase;Integrated Security=True";

 public  IEnumerable<Customer> GetCustomers()
 {
     using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
     {
         sqlConnection.Open();
         var customer = sqlConnection.Query<Customer>("Select * from Customer");
         return customer;

     }
 }

}

Now we are ready with our model classes now It’s time to Create a Controller so I have created a Customer Controller like following.

CustomerController

It will create customer controller in controller folder with by default ActionResult Index. I have modified Action Result just like to following to return customerEntities with IndexView.

public class CustomerController : Controller
{
 //
 // GET: /Customer/

 public ActionResult Index()
 {
     var customerEntities = new CustomerDB();
     return View(customerEntities.GetCustomers());

 }

}

Now we are ready with our Customer Controller and now it’s time to create a view from the customer entities. So I have just right clicked customer entities and Create a View like following.

AddingView

It will popup the following dialogbox where I have selected Razor View with Strongly Typed View. Also I have selected Customer Model class customer and selected list template like following.

RazorView

That’s it now we are done with all the coding and It’s now time to run the project and result is as accepted as following.

Browser

That’s it. Isn’t that cool? Hope you liked this. Stay tuned for more.. Happy programming

Shout it

kick it on DotNetKicks.com

5 comments:

  1. Why do you need to write sql query if you are using ORM?

    ReplyDelete
  2. Dapper is micro ORM which convert your reader into entities

    ReplyDelete
  3. Thanks Jalpesh, great example. Just started playing with Dapper.

    Could you please enlighten me on this problem...
    I am getting a blank record inserted in my view. I think the problem is in my controller's Add action method.

    In my repository I have an Add method:

    public Contact Add(Contact contact)
    {
    var sql = "INSERT INTO Contacts (FirstName, LastName, Email) VALUES (@FirstName, @LastName, @Email); " +
    "SELECT CAST(SCOPE_IDENTITY() as int)";
    var id = this.db.Query(sql, contact).Single();
    contact.Id = id;
    return contact;
    }

    In the controller's CREATE (POST) action, I have:

    // POST: /Contacts/Create
    [HttpPost]
    public ActionResult Create(FormCollection collection)
    {
    try
    {
    // TODO: Add insert logic here
    ContactRepository _contactrepository = new ContactRepository();
    Contact contact = new Contact();
    contact = _contactrepository.Add(contact);
    _contactrepository.Save(contact);
    return RedirectToAction("Index");
    }
    catch
    {
    return View();
    }
    }

    Why am I getting a blank?

    ReplyDelete
  4. Sorry please ignore last post, I figured it out. I had a default FormsCollection in my argument.

    I changed the controller to:

    //
    // POST: /Contacts/Create
    [HttpPost]
    public ActionResult Create(Contact newContact)
    {
    try
    {
    // TODO: Add insert logic here
    ContactRepository _contactrepository = new ContactRepository();
    _contactrepository.Add(newContact);
    return RedirectToAction("Index");
    }
    catch
    {
    return View();
    }
    }

    Everything inserting fine.

    ReplyDelete

Your feedback are very important to me. Please provide your feedback via putting comments.