Subscribe Us

LightBlog

Friday, 29 May 2020

Easy CRUD for your Web API with Dapper

Easy CRUD for your Web API with Dapper

by on in



  • Create a new project in Visual Studio
  • File -> New Project -> Templates -> Visual C# -> Windows -> ASP.NET Web Application -> Save as proper name
  • We want to create a new ASP.Net Web API project. Select “Empty” then check the “Web API” box
  • Now, let’s create a MS-SQL database to connect to. Create a database, and make a note of the dbname, user and password for this.
          Let’s create a quick table on the database:

         CREATE TABLE [Customer](
                 [CustomerID] [int] IDENTITY(1,1) NOT NULL,
                 [CustomerFirstName] [varchar](50) NULL,
                 [CustomerLastName] [varchar](50) NULL,
                 [IsActive] [bit] NOT NULL,
         )
          Now let’s add a few records into the table.
  • Back in Visual Studio, open up your App.config file and add an SQL connection string:
              <connectionStrings>
                     <add name="DefaultConnection" providerName="System.Data.SqlClient"
                      connectionString="Server=server.com;Database=DapperDemo;
                      User Id=userid; Password=mypassword;" />
           </connectionStrings>
           We will be using this for our Web API connection.
  • Install Dapper from your Package Manager Console:
  • Install-Package Dapper
  • Next, right click on the “Models” folder and select “Add -> New Item”. We want to add a plain class (Visual C# -> Code -> Class. Name it “Customer.cs” and save it.
           public class Customer

          {
                 public int CustomerID { get; set; }

                 public string CustomerFirstName { get; set; }

                 public string CustomerLastName { get; set; }

                 public bool IsActive { get; set; }

           }

           This will be our model for this application.
  • Next, create a folder named “DAL” (Data Access Layer) and let’s create a repository.
  • Add an interface (add -> New Item -> Visual C# -> Code -> Interface) and call it “ICustomerRepository”.
In here we’ll add the methods for our interface:

 using System.Collections.Generic;
using DapperDemoAPI.Models;
namespace DapperDemoAPI.DAL
{
    internal interface ICustomerRespository 
   {
               List<Customer> GetCustomers(int amount, string sort);
               Customer GetSingleCustomer(int customerId);
               bool InsertCustomer(Customer ourCustomer);
               bool DeleteCustomer(int customerId);
               bool UpdateCustomer(Customer ourCustomer);
    }
}
These will cover our CRUD (Create, Read, Update, Delete) operations, plus a list operation.
  • Now we’ll create a CustomerRepository class that implements this interface: (add -> New Item -> Visual C# -> Code -> Class) and call it CustomerRespository.cs.
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Dapper;
using DapperDemoAPI.Models;
namespace DapperDemoAPI.DAL
{
            public class CustomerRespository : ICustomerRespository
            {
                        private readonly IDbConnection _db;
                        public CustomerRespository()
                        {
                                      _db = new SqlConnection
                                              (ConfigurationManager.ConnectionStrings
                                              ["DefaultConnection"].ConnectionString);
                        }

                        public List<Customer> GetCustomers(int amount, string sort)
                        {
                                return this._db.Query<Customer>("SELECT TOP " + amount + 
                                          "[CustomerID], [CustomerFirstName], 
                                            [CustomerLastName],
                                            [IsActive] FROM [Customer] 
                                            ORDER BY CustomerID " + sort).ToList();
                        }

                        public Customer GetSingleCustomer(int customerId)
                        {
                                    return _db.Query<Customer>("SELECT[CustomerID],
                                              [CustomerFirstName],[CustomerLastName],
                                              [IsActive] FROM [Customer] WHERE
                                              CustomerID =@CustomerID", 
                                               new { CustomerID = customerId }).SingleOrDefault();
                        }

                        public bool InsertCustomer(Customer ourCustomer)
                        {
                                   int rowsAffected = this._db.Execute(
                                      @"INSERT Customer(
                                        [CustomerFirstName],
                                        [CustomerLastName], 
                                        [IsActive])  values (
                                       @CustomerFirstName,
                                       @CustomerLastName, 
                                       @IsActive)",   new 
                                       { CustomerFirstName =         
                                        ourCustomer.CustomerFirstName,
                                        CustomerLastName = ourCustomer.
                                        CustomerLastName, IsActive = true });
                                    if (rowsAffected > 0)
                                    {
                                                return true;
                                    }
                                    return false;
                        }
                        public bool DeleteCustomer(int customerId)
                        {
                                    int rowsAffected = this._db.Execute(@"DELETE FROM
                                                                   [Customer] 
                                                                   WHERE CustomerID = @CustomerID",
                                                                    new { CustomerID = customerId });
                                    if (rowsAffected > 0)
                                    {
                                                return true;
                                    }
                                    return false;
                        }
                        public bool UpdateCustomer(Customer ourCustomer)
                        {
                                    int rowsAffected = this._db.Execute(
                                                                   "UPDATE [Customer] SET 
                                                                   [CustomerFirstName]
                                                                   = @CustomerFirstName,
                                                                   [CustomerLastName] =
                                                                   @CustomerLastName, [IsActive] = 
                                                                   @IsActive 
                                                                   WHERE CustomerID = " +
                                                                   ourCustomer.CustomerID, ourCustomer);
                                    if (rowsAffected > 0)
                                    {
                                                return true;
                                    }
                                    return false;
                        }
            }
}
This class should implement the interface and it’s members, to start out let’s make it look like this:

CREATE Customer entries with InsertCustomer

In this method  we’ll insert a new customer:
public bool InsertCustomer(Customer ourCustomer)
{
          int rowsAffected = this._db.Execute(@"INSERT 
           Customer([CustomerFirstName],
           [CustomerLastName], [IsActive]) 
           values (@CustomerFirstName, 
           @CustomerLastName, @IsActive)",
           new { CustomerFirstName = 
           ourCustomer.CustomerFirstName, 
           CustomerLastName = 
           ourCustomer.CustomerLastName, 
           IsActive = true});
         if (rowsAffected > 0)
        {
                   return true;
        }
        return false;
}
If you look closely, we are using a parameterized query here. Then you pass in an object containing those parameters, taken from the object we passed in. This is pretty straightforward.

READ Customer entries with GetSingleCustomer

With this method we’ll retrieve a single customer by CustomerID:

public Customer GetSingleCustomer(int customerId)
{
`         return  _db.Query<Customer>("SELECT[CustomerID],
                     [CustomerFirstName], [CustomerLastName],[IsActive] 
                    FROM [Customer] WHERE CustomerID = @CustomerID",  
                    new { CustomerID = customerId }).SingleOrDefault();}

UPDATE Customer entries with UpdateCustomer

To update a customer we’ll need to pass in a Customer object, and we’ll update the record based on what’s in that object.
public bool UpdateCustomer(Customer ourCustomer)
{
               int rowsAffected = this._db.Execute("UPDATE [Customer] 
                                             SET [CustomerFirstName] = 
                                            @CustomerFirstName ,
                                             [CustomerLastName]
                                             = @CustomerLastName, 
                                             [IsActive] = @IsActive 
                                             WHERE CustomerID 
                                             = " + ourCustomer.CustomerID, 
                                             ourCustomer);
                if (rowsAffected > 0)
               {
                               return true;
               }
               return false;
}

 DELETE Item with DeleteCustomer Method
In this method we’ll send a simple parameterized delete command based on the integer 
we pass in:
public bool DeleteCustomer(int customerId)
{
               int rowsAffected = this._db.Execute(@"DELETE FROM [jeremy].[Customer] 
                                              WHERE CustomerID = @CustomerID",
                                               new{ CustomerID = customerId });
               if (rowsAffected > 0)
               {
                               return true;
               }
               return false;
}
Like similar methods we pass a true/false based on the number of records affected, if it’s not zero
we’ll assume it was successful.

LIST Items with GetCustomers Method
In this method we are going to get a list of items. You can select how many items, and how you want to sort them.
public List<Customer> GetCustomers(int amount, string sort)
{               return this._db.Query<Customer>("SELECT TOP "+ amount +"
                          [CustomerID],[CustomerFirstName],
                           [CustomerLastName],[IsActive]
                           FROM [Customer]
                          ORDER BY CustomerID " + sort).ToList();}

Create a Web API Controller
Next, right click on the “Controlle” folder and select “Add ->Controller”. Name it “CustomerController” and save it.

CustomerController

using System.Collections.Generic;
using System.Web.Http;
using DapperDemoAPI.DAL;
using DapperDemoAPI.Models;

namespace DapperDemoAPI.Controllers
{
    public class CustomerController : ApiController
    {
                private CustomerRespository _ourCustomerRespository = new CustomerRespository();
              
                        // GET: /Customer
                        [Route("Customers")]
                        [HttpGet]
                        public List<Customer> Get()
                        {
                                    return _ourCustomerRespository.GetCustomers(10, "ASC");
                        }

                        // GET: /Customer/10/ASC
                        [Route("Customers/{amount}/{sort}")]
                        [HttpGet]
                        public List<Customer> Get(int amount, string sort)
                        {
                                    return _ourCustomerRespository.GetCustomers(amount, sort);
                        }

                        // GET: /Customer/5
                        [Route("Customers/{id}")]
                        [HttpGet]
                        public Customer Get(int id)
                        {
                                    return _ourCustomerRespository.GetSingleCustomer(id);
                        }

                        // POST: /Customer
                        [Route("Customers")]
                        [HttpPost]
                        public bool Post([FromBody]Customer ourCustomer)
                        {
                                    //return true;
                                    return _ourCustomerRespository.InsertCustomer(ourCustomer);
                        }

                        // PUT: api/Customer/5
                        [Route("Customers")]
                        [HttpPut]
                        public bool Put([FromBody]Customer ourCustomer)
                        {
                                    return _ourCustomerRespository.UpdateCustomer(ourCustomer);
                        }

                        // DELETE: api/Customer/5
                        [Route("Customers/{id}")]
                        [HttpDelete]
                        public bool Delete(int id)
                        {
                                    return _ourCustomerRespository.DeleteCustomer(id);
                        }
    }
}

By at
Tags