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.

  1. In the Solution Explorer window, right-click on References and select Manage NuGet Packages.
  2. In the NuGet Package Manager window that opens, select nuget.org as the package source.
  3. Under the Browse tab, enter the package name such dapper in the search box.
  4. Click on the Install button for the package to be installed.
  5. 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/[email protected]" title="Edit">Edit <i class="fa fa-edit"></i></a><a href="/Customer/Delete?&[email protected]" 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.

Related Articles

Last modified: October 28, 2019

Comments

Write a Reply or Comment

Your email address will not be published.