Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Stored Procedure
The
Stored Procedure accepts
ContactName parameter which is used to perform a search in
Customers Table.
CREATE PROCEDURE Customers_SearchCustomers
@ContactName NVARCHAR(30)
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP 10 *
FROM Customers
WHERE ContactName LIKE'%'+ @ContactName +'%'
END
Model
The Model class consist of following properties
public class CustomerModel
{
public string CustomerID { get; set; }
public string ContactName { get; set; }
public string City { 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 methods.
Action Method for handling GET operation
Inside this Action method, simply the View is returned.
Action Method for handling POST operation
Inside this Action method, first the connection is read from the ConnectionStrings section of the AppSettings.json file.
After that, a connection to the database is established using the SqlConnection class.
Then, DynamicParameter class object is created and the TextBox value is added as parameter.
Finally, the name of the
Stored Procedure and an object of
DynamicParameter class with
CommandType as
Stored Procedure are passed to
Query method of
Dapper library and fetched 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()
{
return View();
}
[HttpPost]
public IActionResult Index(string customerName)
{
string spName = "Customers_SearchCustomers";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
DynamicParameters dynamicParameters = new DynamicParameters();
// Adding parameter.
dynamicParameters.Add("@ContactName", customerName);
return View(con.Query<CustomerModel>(spName, dynamicParameters, commandType: CommandType.StoredProcedure));
}
}
}
View
HTML Markup
Inside the View, in the very first line the CustomerModel class is declared as Generic List collection.
The View consists of an HTML Form which has been created using the following TagHelpers attributes.
asp-action – Name of the Action. In this case the name is Index.
asp-controller – Name of the Controller. In this case the name is Home.
method – It specifies the Form Method i.e. GET or POST. In this case it will be set to POST.
The View also consists of an HTML INPUT TextBox and a Submit Button.
Then, the Generic List collection of CustomerModel class is checked for NULL and if it is not NULL then a FOR EACH loop will be executed over the Model which will generate the HTML Table rows with the Customer records.
@using Dapper_Like_Parameter_SP_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>
<form method="post" asp-controller="Home" asp-action="Index">
<span>Customer Name:</span><input type="text" name="customerName" />
<input type="submit" value="Search" />
</form>
@if (Model != null)
{
<hr/>
<table cellpadding="0" cellspacing="0">
<tr>
<th>Customer ID</th>
<th>Contact Name</th>
<th>City</th>
<th>Country</th>
</tr>
@foreach (CustomerModel customer in Model)
{
<tr>
<td>@customer.CustomerID</td>
<td>@customer.ContactName</td>
<td>@customer.City</td>
<td>@customer.Country</td>
</tr>
}
</table>
}
</body>
</html>
Screenshots
![ASP.Net Core: Perform Like Search using Dapper in SQL Server]()
Demo
Downloads
