This article is going to be all about CRUD operations in ASP.NET 3.0 and Dapper. We will start from the basics and walk you through the various intricacies of CRUD operations, and hopefully, you develop a clear understanding of it all at the end.
Also, it’s Visual Studio 2019 that we will be using to develop the test application. The version is the latest iterations of the .NET integrated development environment provided by Microsoft and is already available for download from the Microsoft site for free.
What is CRUD Operations?
CRUD stands for Create Replace Update and Delete. Each stand for a particular operation that you can accomplish to make corresponding changes to your database from right within the application itself.
So, the prerequisites you need to have on your system to get started here is:
- Visual Studio 2019( earlier version should be ok)
- .NET Core 3.0 Installed
- Microsoft SQL Server
The application we are going to build is a Customer-related application that we aim to build to demo CRUD concepts where you will be able to add new Customers, list existing Customers. or update an existing one.
We start off by creating the database using MS SQL Server. The database is named ‘Mydb’ while the table is named Customers
Create Customers Table
USE [Mydb] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Customers]( [CustomerId] [int] IDENTITY(1,1) NOT NULL, [CustomerName] [nvarchar](50) NULL, [CustomerEmail] [nvarchar](50) NULL ) ON [PRIMARY] GO
Now we are about to create the stored procedures that we will need for database operations. That includes adding new customers, searching the customer list, as well as updating an existing customer.
“Add Customer” Stored Procedure
Here is the “Add Customer” stored procedure. Named “SP_AddCustomer”, the stored procedure returns the customer Id of the customer created.
CREATE PROCEDURE [dbo].[SP_AddCustomer] @CustomerName NVARCHAR(250), @CustomerEmail NVARCHAR(250) AS BEGIN DECLARE @CustomerId as BIGINT INSERT INTO [Customers] ( CustomerName, CustomerEmail ) VALUES ( @CustomerName, @CustomerEmail ); SET @CustomerId = SCOPE_IDENTITY(); SELECT @CustomerId AS CustomerId; END;
“Update Customer” Stored Procedure
Similarly, here is the “Update Customer” stored procedure named “SP_UpdateCustomer”.
CREATE PROCEDURE [dbo].[SP_UpdateCustomer] @CustomerId INT, @CustomerName NVARCHAR(250), @CustomerEmail NVARCHAR(250) AS BEGIN UPDATE Customers SET CustomerName = @CustomerName, CustomerEmail = @CustomerEmail WHERE CustomerId = @CustomerId END;
“Get All Customers” Stored Procedure
Finally, here is the “Fetch Customer list” store procedure named “SP_GetAllCustomers”.
CREATE PROCEDURE [dbo].[SP_GetAllCustomers] AS BEGIN SET NOCOUNT ON; select * from Customers END
Create an ASP.NET Core solution
Next, open Visual Studio 2019 ( or an earlier version if you wish).
In the Visual Studio 2019 welcome screen that pops up, select ‘Create a new project’ from the options shown on the right.
In the next screen that shows, select ‘ASP.NET Core Web Application’. Click on Next.
In the New ASP.NET Core Web Application window that opens, select ‘Web Application (Model-View-Controller)’.
Make sure the Configure for HTTPS checkbox at the bottom is selected.
Click on OK.
In the Configure your new project window that opens, enter a project name.
Click on the Create button.
Adding NuGet packages
To proceed further, we need to add a few NuGet packages. Those include:
- Microsoft.AspNetCore.App
- Microsoft.NetCore.App
- Dapper
If you aren’t sure how to add NuGet packages to your project, here is the process.
- In the Solution Explorer window, right-click on References and select Manage NuGet Packages.
- In the NuGet Package Manager window that opens, select nuget.org as the package source.
- Under the Browse tab, enter the package name such dapper in the search box.
- Click on the Install button for the package to be installed.
- Consent to any confirmatory box that appears.
Follow the same process to add the remaining packages.
Creating Dapper Class and Interface
With these done, we will now shift our focus towards creating the Dapper Class and Interface. However, before we proceed with the actual steps, here is a short discourse on what Dapper is in the first place in case you aren’t in the knowing.
Dapper is essentially a Micro Object Relational Mapping or ORM for the .NET platform. It can also be considered a NuGet library which is added to a .NET Core project for execution of database operations. The reason we use Dapper is that its cuts down the database access codes in a big way. It also is very lightweight while providing for a performance boost as well.
Coming back to the project on hand, create two folders – Helper and Interface.
Now, in the Interface folder, add a new interface and name it ‘IDapperHelper’.
Here is the code that you need to add to the IDapperHelper class.
IDapperHelper Interface
public interface IDapperHelper
{
DbConnection GetConnection();
T Get<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
List<T> GetAll<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
int Execute(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
T Insert<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
T Update<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
}
DapperHelper Class
Similarly, in the Helper folder, add a new class named ‘DapperHelper’. Add the following code in that class. This will lead to the creation of the Dapper helper which will ensure communication with the database.
public class DapperHelper : IDapperHelper
{
private readonly IConfiguration _config;
public DapperHelper(IConfiguration config)
{
_config = config;
}
public DbConnection GetConnection()
{
return new SqlConnection(_config.GetConnectionString("dbConnection"));
}
public T Get<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure)
{
using (IDbConnection db = new SqlConnection(_config.GetConnectionString("dbConnection")))
{
return db.Query<T>(sp, parms, commandType: commandType).FirstOrDefault();
}
}
public List<T> GetAll<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure)
{
using (IDbConnection db = new SqlConnection(_config.GetConnectionString("dbConnection")))
{
return db.Query<T>(sp, parms, commandType: commandType).ToList();
}
}
public int Execute(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure)
{
using (IDbConnection db = new SqlConnection(_config.GetConnectionString("dbConnection")))
{
return db.Execute(sp, parms, commandType: commandType);
}
}
public T Insert<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure)
{
T result;
using (IDbConnection db = new SqlConnection(_config.GetConnectionString("dbConnection")))
{
try
{
if (db.State == ConnectionState.Closed)
db.Open();
using (var tran = db.BeginTransaction())
{
try
{
result = db.Query<T>(sp, parms, commandType: commandType, transaction: tran).FirstOrDefault();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (db.State == ConnectionState.Open)
db.Close();
}
return result;
}
}
public T Update<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure)
{
T result;
using (IDbConnection db = new SqlConnection(_config.GetConnectionString("dbConnection")))
{
try
{
if (db.State == ConnectionState.Closed)
db.Open();
using (var tran = db.BeginTransaction())
{
try
{
result = db.Query<T>(sp, parms, commandType: commandType, transaction: tran).FirstOrDefault();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (db.State == ConnectionState.Open)
db.Close();
}
return result;
}
}
}
Customer Entity
Create a folder and name it ‘Entities’. Add a class named ‘Customer’ to the folder.
Add the code below to the class.
public class Customer
{
public int CustomerId { get; set; }
public string CustomerName { get; set; }
public string CustomerEmail { get; set; }
}
ICustomerRepository Interface
Now, in the interface folder that we created before, add ICustomerRepository interface. Here is the code of the interface.
public interface ICustomerRepository
{
List<Customer> GetAllCustomers();
int Delete(int customerId);
Customer GetByCustomerId(int customerId);
string Update(Customer customer);
int Create(Customer customer);
}
CustomerRepository Class
Create a new folder and name it ‘Repository’. Add a new class inside that folder and name the class ‘CustomerRepository’. Here is the code for the CustomerRepository class.
public class CustomerRepository : ICustomerRepository
{
private readonly IDapperHelper _dapperHelper;
public CustomerRepository(IDapperHelper dapperHelper)
{
_dapperHelper = dapperHelper;
}
public int Create(Customer customer)
{
var parameters = new DynamicParameters();
parameters.Add("CustomerId", customer.CustomerId, DbType.Int16);
parameters.Add("CustomerName", customer.CustomerName, DbType.String);
parameters.Add("CustomerEmail", customer.CustomerEmail, DbType.String);
var data = _dapperHelper.Insert<int>("[dbo].[SP_AddCustomer]",
parameters,
commandType: CommandType.StoredProcedure);
return data;
}
public Customer GetByCustomerId(int customerId)
{
var data = _dapperHelper.Get<Customer>($"select * from Customers where CustomerId={customerId}", null,
commandType: CommandType.Text);
return data;
}
public int Delete(int customerId)
{
var data = _dapperHelper.Execute($"Delete Customers where CustomerId={customerId}", null,
commandType: CommandType.Text);
return data;
}
public List<Customer> GetAllCustomers()
{
var data = _dapperHelper.GetAll<Customer>("[dbo].[SP_GetAllCustomers]", null, commandType: CommandType.StoredProcedure);
return data.ToList();
}
public string Update(Customer customer)
{
var parameters = new DynamicParameters();
parameters.Add("CustomerName", customer.CustomerName, DbType.String);
parameters.Add("CustomerEmail", customer.CustomerEmail);
var data = _dapperHelper.Update<string>("[dbo].[SP_UpdateCustomer]",
parameters,
commandType: CommandType.StoredProcedure);
return data;
}
}
Add Connection String
Add a connection string to the appsettings.json file.
{
"ConnectionStrings": {
"dbConnection": "Data Source=localhost;Initial Catalog=Mydb;Integrated Security=True"
},
"Logging": {
"LogLevel": {
"Default": "Debug",
"System": "Information",
"Microsoft": "Information"
}
}
}
Dependency Injection
Also, add the code below for the dependency injection to work properly
services.AddScoped<ICustomerRepository, CustomerRepository>(); services.AddScoped<IDapperHelper, DapperHelper>();
Add Controller
You will now need to add Customer Controller inside the Controller folder. Here is the code of CustomerController.
public class CustomerController : Controller
{
private readonly ICustomerRepository _customerRepository;
public CustomerController(ICustomerRepository customerRepository)
{
_customerRepository = customerRepository;
}
// GET: /<controller>/
public IActionResult Index()
{
var data = _customerRepository.GetAllCustomers();
string baseUrl = $"{this.Request.Scheme}://{this.Request.Host}{this.Request.PathBase}";
return View(data);
}
public ActionResult Add()
{
return View("Add", new Customer());
}
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Add(Customer model)
{
if (ModelState.IsValid)
{
var customer = new Customer()
{
CustomerId = model.CustomerId,
CustomerName = model.CustomerName,
CustomerEmail = model.CustomerEmail
};
_customerRepository.Create(customer);
return RedirectToAction("Index", "Customer");
}
return View("Index", model);
}
public ActionResult Edit(int customerId)
{
var _customer = _customerRepository.GetByCustomerId(customerId);
var customer = new Customer
{
CustomerId = _customer.CustomerId,
CustomerName = _customer.CustomerName,
CustomerEmail = _customer.CustomerEmail
};
return View("Index", _customer);
}
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit(Customer model)
{
if (ModelState.IsValid)
{
var customer = new Customer()
{
CustomerId = model.CustomerId,
CustomerName = model.CustomerName,
CustomerEmail = model.CustomerEmail
};
_customerRepository.Update(customer);
return RedirectToAction("Index", "Customer");
}
return View("Index", model);
}
public ActionResult Delete(int CustomerId)
{
var customer = _customerRepository.Delete(CustomerId);
return RedirectToAction("Index", "Customer");
}
}
Add Views
Further, add a View inside the folder – Views > Customer. Here is the code for Index.cshtml view.
@model List<Customer>
@{
ViewData["Title"] = "Customers List";
}
<a href="/Customer/Add" class="btn btn-primary">Add Customer</a>
<br />
<table class="table compact table-striped table-bordered nowrap">
<thead>
<tr role="row">
<th class="sorting" role="columnheader" rowspan="1">ID</th>
<th class="sorting" role="columnheader" rowspan="1">Name</th>
<th class="sorting" role="columnheader" rowspan="1">Email</th>
<th class="sorting" role="columnheader" rowspan="1"> Action </th>
</tr>
</thead>
<tbody >
@foreach (var item in Model)
{
<tr class="even">
<td style="text-align:left">@item.CustomerId</td>
<td style="text-align:left">@item.CustomerName</td>
<td style="text-align:left">@item.CustomerEmail</td>
<td class="text-center ">
<a href="/Customer/Edit?CutomerId=@item.CustomerId" title="Edit">Edit <i class="fa fa-edit"></i></a><a href="/Customer/Delete?&CustomerID=@item.CustomerId" class="" onclick="return confirm('Are you sure you want to delete this Customer?');" title="Delete">Delete <i class="fa fa-times"></i></a>
</td>
</tr>
}
</tbody>
</table>
The above code will let you create a simple application where you have the option to add customer, search for customers or delete an existing customer.
Comments