In this article I will explain with an example, how to select data from database with
Stored Procedure using
Dapper library in ASP.Net Core 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 CustomerModel
{
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;
Controller
The Controller consists of following Action method.
Action Method for handling GET operation
Inside this Action method, first the connection is read from the ConnectionStrings section of the AppSettings.json file.
Then, the name of the
Stored Procedure is passed to the
Query method of the
Dapper library which then returns Generic List collection of
CustomerModel class objects.
Finally, the Customer records are returned to the View.
public class HomeController : Controller
{
public IConfiguration Configuration { get; set; }
public HomeController(IConfiguration _configuration)
{
this.Configuration = _configuration;
}
public IActionResult Index()
{
string spName = "Customers_GetCustomers";
string constr = this.Configuration.GetSection("ConnectionStrings")["MyConn"];
using (SqlConnection con = new SqlConnection(constr))
{
return View(con.Query<CustomerModel>(spName, commandType: CommandType.StoredProcedure));
}
}
}
View
HTML Markup
Inside the View, in the very first line the Generic List collection of CustomerModel class is declared as Model for the View.
For displaying the records, an HTML Table is used. A FOREACH loop will be executed over the Model which will generate the HTML Table rows with the Customer records.
@using Dapper_Select_SP_Core_MVC.Models
@model List<CustomerModel>
@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers
@{
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 (CustomerModel customer in Model)
{
<tr>
<td>@customer.CustomerId</td>
<td>@customer.Name</td>
<td>@customer.Country</td>
</tr>
}
</table>
</body>
</html>
Screenshot
Downloads