For comparisons between different ORM kindly refer the previous post of this blog.
Dapper's primary feature is mapping from .NET classes to database tables (and from CLR data types to SQL data types). Dapper also provides data query and retrieval facilities.
Dapper is a micro-ORM: it does not offer the full range of features of a full ORM such as NHibernate or Entity Framework. This is by design. It focuses on simplicity and performance rather than full automation. Dapper does not generate the SQL queries, but only maps the result to Plain Old CLR Objects (POCOs). Third party extensions are available for mapping objects to INSERT and UPDATE queries. It is optimized for use with .NET 4.0 or higher.
The single class ORM is also available on NuGet.
Let us now see how to create a web application in ASP.NET MVC using Dapper ORM.
Open Visual Studio (used by me is :2013) -> New Project -> Choose MVC Project however you want.
Now as you use to give the reference to Entity Framework using Nuget Packet Manager, similarly, go to Manager and search for Dapper & Dapper.SimpleCRUD and install that packages as shown in fig.
I have created a database in Sql Server and given connection string in web.config as we use to do normally. My Connection Name is EmployeeDBContext(* Not necessary to use as DBContext)
Table : Emp_Personal. Column of my tables are as E_Id, E_Name, E_Address, E_Contact, E_DOB.
Now create a model in model folder. I have created Model named Employee and defined the attributes as shown.
Namespace:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Dapper;
using System.Data;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
Class:
Now after creating a model we'll create another model in which we would write the methods to get the data from database.
Let us call that model as MainModel.cs
NameSpace:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using Dapper;
In class first thing we'll give a connection to database and yes welcome back to old format of code again in Dapper.
Now go to controller folder and add a controller.
Display All Employees:
In controller I've created a method to get all employee details as GetDetails().
For details we'll call the MainModel class and access the method EmployeeDetails() which is as shown :
Now accessing through controller:
Now to view the records add the view by rightclick on Action Method and Add View -> List & select a model as shown:
Note while running it may ask to map the SQLMAPPER.cs file for that you can goto Git or Google and find out sqlmapper.cs file for dapper or you may comment down.
Insert Records:
Similarly we've to go for all Insert, Edit and delete.
MainModel Method:
Make sure here values are passed as "@Columnname" and connection.Execute(query,new {attributes}); we've to pass.
Controller Method:
In controller first we'll use HTTP GET for redirecting to Create View and from there we'll use HTTP POST on submission.
View : (rightclick and details , select model)
Your view should be something like this.
@model DapperORM.Models.Employee
Edit Records:
For edit and delete we'll create a method which will get the data by selected id from details page and display the data to view and there after perform the HTTP POST(submission).
MainModel Method:
public Employee GetEmployeeList(string E_Id)
{
string query = "select E_Id,E_Name,E_Address,E_Contact,E_DOB from Emp_Personal where E_Id=@E_Id";
var result = con.Query(query, new { E_Id }).Single();
return result;
}
public string Updateemployee(Employee objemp)
{
string query = "update Emp_Personal set E_Name=@E_Name,E_Address=@E_Address,E_Contact=@E_Contact,E_DOB=@E_DOB where E_Id=@E_Id";
con.Execute(query, new { objemp.E_Name, objemp.E_Address, objemp.E_Contact, objemp.E_DOB, objemp.E_Id });
}
string result = "updated";
return result;
Controller Method:
View Method: (Some thing like this your view should be)
Note: be sure that while edit and delete GetEmployeeList ()method should be called during httpget. and then perform operation.
That's it. And yes for details of particular employee.
Details of particular employee
Same GetEmployeeList as for delete and edit.
[HttpGet]
public ActionResult Details(string id)
{
return View(mm.GetEmployeeList(id));
MainModel mm = new MainModel();
}
View :
And you are done.
