In this post, we will learn how to use Use Dapper.NET ORM in ASP.NET MVC.
What is a Dapper?
Dapper
is a simple object mapper for .NET.
Dapper is a single file you can drop into your project that will extend your IDbConnection interface
.
It provides 4 helpers:
- Execute a query and map the results to a strongly typed
List
- Execute a query and map it to a list of dynamic objects
- Execute a Command that returns no results
- Execute a Command multiple times
Performance
A key feature of Dapper
is performance. The following metrics show how long it takes to execute 500 SELECT
statements against a DB and map the data returned to objects.
Implementation
Install Dapper
using Nuget Package Manager:
PM> Install-Package Dapper
- Create a project in ASP.NET MVC
- Add a folder named Dapper inside it.
Create User
and Address
classes:
public class Address
{
public int AddressID { get; set; }
public int UserID { get; set; }
public string AddressType { get; set; }
public string StreetAddress { get; set; }
public string City { get; set; }
public string State { get; set; }
public string ZipCode { get; set; }
}
public class User
{
public User()
{
this.Address = new List<Address>();
}
public int UserID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public List<Address> Address { get; set; }
}
Now, create IUserRepository.cs interface and UserRepository.cs classes for data access.
public interface IUserRepository
{
List GetAll();
User Find(int id);
User Add(User user);
User Update(User user);
void Remove(int id);
User GetUserInformatiom(int id);
}
public class UserRepository : IUserRepository
{
private IDbConnection _db = new SqlConnection
(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
public List<User> GetAll()
{
return this._db.Query<User>("SELECT * FROM Users").ToList();
}
public User Find(int id)
{
return this._db.Query<User>
("SELECT * FROM Users WHERE UserID = @UserID", new { id }).SingleOrDefault();
}
public User Add(User user)
{
var sqlQuery = "INSERT INTO Users
(FirstName, LastName, Email) VALUES(@FirstName, @LastName, @Email); " +
"SELECT CAST(SCOPE_IDENTITY() as int)";
var userId = this._db.Query<int>(sqlQuery, user).Single();
user.UserID = userId;
return user;
}
public User Update(User user)
{
var sqlQuery =
"UPDATE Users " +
"SET FirstName = @FirstName, " +
" LastName = @LastName, " +
" Email = @Email " +
"WHERE UserID = @UserID";
this._db.Execute(sqlQuery, user);
return user;
}
public void Remove(int id)
{
throw new NotImplementedException();
}
public User GetUserInformatiom(int id)
{
using (var multipleResults = this._db.QueryMultiple("GetUserByID",
new { Id = id }, commandType: CommandType.StoredProcedure))
{
var user = multipleResults.Read<User>().SingleOrDefault();
var addresses = multipleResults.Read<Address>().ToList();
if (user != null && addresses != null)
{
user.Address.AddRange(addresses);
}
return user;
}
}
}
Now use the above repository in the HomeController.cs.
Create an instance for UserRepository
class.
private IUserRepository _repository = new UserRepository();
Get All User
public ActionResult Index()
{
return View(_repository.GetAll());
}
Output
dapper .NET
Source Code
You can find the source code in Github.
The post Use Dapper.NET ORM in ASP.NET MVC appeared first on Venkat Baggu Blog.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.