In this article I will explain with an example, how to use
Dapper library in ASP.Net MVC.
What is Dapper?
Dapper is a simple micro-ORM used to simplify working with
ADO.Net.
Dapper is an open-source object-relational mapping (ORM) library for .NET and .NET Core applications. This library allows developers quickly and easily access data from databases.
Dapper allows you to execute raw SQL queries, map the results to objects and execute stored procedures.
Dapper is lightweight and fast, making it an ideal choice for applications that require low latency and high performance.
It has support for both asynchronous and synchronous database queries and batching multiple queries together into a single call.
Additionally, dapper supports parameterized queries to help protect against SQL injection attacks.
Installing Dapper package using Nuget
Database
I have made use of the following table Customers with the schema as follows.
I have already inserted few records in the table.
Note: You can download the database table SQL by clicking the download link below.
Model
The Model class consists of following properties.
public class Customer
{
public int CustomerId { get; set; }
public string Name { get; set; }
public string Country { get; set; }
}
Namespaces
You will need to import the following namespaces.
using Dapper;
using System.Configuration;
using System.Data.SqlClient;
Controller
The Controller consists of following Action method.
Action Method for handling GET operation
Inside this Action method, the records are fetched from the
Customers Table of the
SQL Server database using
Query method of
Dapper library and returned to the View.
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string sql = "SELECT CustomerId, Name, Country FROM Customers";
using (SqlConnection con = new SqlConnection(constr))
{
return View(con.Query<Customer>(sql));
}
}
}
View
Inside the View, in the very first line the Customer Model is declared as IEnumerable which specifies that it will be available as a Collection.
For displaying the records, an HTML Table is used. A FOR EACH loop will be executed over the Model which will generate the HTML Table rows with the Customer records.
@model IEnumerable<Customer>
@using Dapper_Select_MVC.Models
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
<table cellpadding="0" cellspacing="0">
<tr>
<th>Customer Id</th>
<th>Name</th>
<th>Country</th>
</tr>
@foreach (Customer customer in Model)
{
<tr>
<td>@customer.CustomerId</td>
<td>@customer.Name</td>
<td>@customer.Country</td>
</tr>
}
</table>
</body>
</html>
Screenshot
Downloads