In this article I will explain with an example, how to select data from database with
Stored Procedure using
Dapper library in ASP.Net MVC.
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.
Stored Procedure
CREATE PROCEDURE [Customers_GetCustomers]
AS
BEGIN
SET NOCOUNT ON;
SELECT [CustomerId]
,[Name]
,[Country]
FROM [Customers]
END
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.Data;
using System.Data.SqlClient;
using System.Configuration;
Controller
The Controller consists of following Action method.
Action Method for handling GET operation
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string spName = "Customers_GetCustomers";
using (SqlConnection con = new SqlConnection(constr))
{
return View(con.Query<Customer>(spName, commandType: CommandType.StoredProcedure));
}
}
}
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_SP_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