Dapper Getting Started With Querying Data
Dapper provides the Query extension method that enables you to retrieve data from the database and populate data in your object model.
The model is a collection of classes to interact with the database. Now let's add an entity class Customer in the Model folder.
public class Customer { public int CustomerID { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public string Email { get; set; } }
MVC controllers are responsible for responding to requests made against an ASP.NET MVC website. Let's create a 'CustomerController' by right-clicking on the Controller folder in Solution Explorer.
Select Add > Controller... option.
On Add Scaffold dialog, choose MVC 5 Controller with read/write actions, click on Add button and call it CustomerController.
In the Controller Index Action, let's add the following code to retrieve all the data from the Customers table.
// GET: Customer public ActionResult Index() { List<Customer> customers = new List<Customer>(); using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["CustomerConnection"].ConnectionString)) { customers = db.Query<Customer>("Select * From Customers").ToList(); } return View(customers); }
The above code retrieves with the Dapper Query method all the records from the Customers table, stores them in memory, and returns the collection.
Generally, we create View with the same name as an Action method, so let's create an Index view by right-clicking on the Index Action method in CustomerController and select the Add View.
On Add Scaffold dialog, choose MVC 5 View and click on the Add button.
- On the Add View dialog, the View name will appear by default.
- Select the template from the Template dropdown list for which you want to create a view, e.g. List.
- Choose your model class, e.g.
Customer
Click the Add button and it will add the following code for you.
@model IEnumerable<DapperDemoApp.Models.Customer>
@{
ViewBag.Title = "Index";
}
<h2>Index</h2>
<p>
@Html.ActionLink("Create New", "Create")
</p>
<table class="table">
<tr>
<th>
@Html.DisplayNameFor(model => model.FirstName)
</th>
<th>
@Html.DisplayNameFor(model => model.LastName)
</th>
<th>
@Html.DisplayNameFor(model => model.Email)
</th>
<th></th>
</tr>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.FirstName)
</td>
<td>
@Html.DisplayFor(modelItem => item.LastName)
</td>
<td>
@Html.DisplayFor(modelItem => item.Email)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id=item.CustomerID }) |
@Html.ActionLink("Details", "Details", new { id=item.CustomerID }) |
@Html.ActionLink("Delete", "Delete", new { id=item.CustomerID })
</td>
</tr>
}
</table>
Similarly, you can add the following code in the Details action method to retrieve with the Dapper Query method specific customer from the Customers table by passing an Anonymous Type Parameter.
// GET: Customer/Details/5 public ActionResult Details(int id) { Customer customer = new Customer(); using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["CustomerConnection"].ConnectionString)) { customer = db.Query<Customer>("Select * From Customers WHERE CustomerID =" + id, new { id }).SingleOrDefault(); } return View(customer); }
Again, we will create a Detail view by right-clicking on the Details action method in CustomerController and select Add View. On Add Scaffold dialog, choose MVC 5 View and click on the Add button.
- On the Add View dialog, the View name will appear by default.
- Select the template from the Template dropdown list for which you want to create a view, e.g.
Details. - Choose your model class, e.g.
Customer
Click the Add button and it will add the following code for you.
@model DapperDemoApp.Models.Customer
@{
ViewBag.Title = "Details";
}
<h2>Details</h2>
<div>
<h4>Customer</h4>
<hr />
<dl class="dl-horizontal">
<dt>
@Html.DisplayNameFor(model => model.FirstName)
</dt>
<dd>
@Html.DisplayFor(model => model.FirstName)
</dd>
<dt>
@Html.DisplayNameFor(model => model.LastName)
</dt>
<dd>
@Html.DisplayFor(model => model.LastName)
</dd>
<dt>
@Html.DisplayNameFor(model => model.Email)
</dt>
<dd>
@Html.DisplayFor(model => model.Email)
</dd>
</dl>
</div>
<p>
@Html.ActionLink("Edit", "Edit", new { id = Model.CustomerID }) |
@Html.ActionLink("Back to List", "Index")
</p>
Let's run your application and you will see all the customers.
Now click on the Details link of any customer and you will see all the details for that customer.
ZZZ Projects