ASP.NET Web Forms Project Using Dapper ORM

[KGComputers]

Hi,

This post demonstrates an ASP.NET Web Forms project using Dapper ORM (added via Nuget) that will query (Create/Update/Delete) data from the database. The files inside the Models folder specifically the CustomerRepository.cs class is in-charge of communicating the database using T-SQL queries executed through Dapper functions. This class also passing of SQLParameter objects to Dapper's Dynamic Parameters in Add() and Update() methods. The CustomerService.cs will access the methods in CustomerRepository and acts as business logic layer that will be used in Web Forms page code behind.

Customer.cs

public class Customer
    {
        public int CustomerID { get; set; }

        public string CompanyName { get; set; }

        public string Address { get; set; }

        public string City { get; set; }

        public string State { get; set; }

        public DateTime IntroDate { get; set; }

        public decimal CreditLimit { get; set; }
    }

ICustomerRepository.cs

public interface ICustomerRepository
    {
        List<Customer> GetAll();
        Customer FindById(int Id);
        bool AddCustomer(Customer customer);
        bool UpdateCustomer(Customer customer);
        bool DeleteCustomer(int Id);
    }

CustomerRepository.cs

public class CustomerRepository : ICustomerRepository
    {
        private IDbConnection _db;

        public CustomerRepository()
        {
            _db = new SqlConnection(ConfigurationManager.ConnectionStrings["CustomerInformation"].ConnectionString);
        }
        
        public List<Customer> GetAll()
        {
            return this._db.Query<Customer>("SELECT * From Customer;").ToList();
        }

        public Customer FindById(int Id)
        {
	     return this._db.Query<Customer>("SELECT * FROM Customer WHERE CustomerID=@Id", new { Id = Id }).FirstOrDefault();
        }

        public bool AddCustomer(Customer customer)
        {
            SqlParameter[] parameters = {
                            new SqlParameter("@CompanyName",customer.CompanyName),      
                            new SqlParameter("@Address",customer.Address),
                            new SqlParameter("@City",customer.City),
                            new SqlParameter("@State",customer.State),
                            new SqlParameter("@IntroDate",customer.IntroDate),
                            new SqlParameter("@CreditLimit",customer.CreditLimit)};

            string query = "INSERT INTO Customer(CompanyName,Address,City,State,IntroDate,CreditLimit)" 
                          + " Values(@CompanyName,@Address,@City,@State,@IntroDate,@CreditLimit)";

            var args = new DynamicParameters(new { });
            parameters.ToList().ForEach(p => args.Add(p.ParameterName, p.Value));
            try
            {
                this._db.Query<Customer>(query, args).SingleOrDefault();
            }
            catch (Exception)
            {
                return false;
            }

            return true;
        }

        public bool UpdateCustomer(Customer customer)
        {
            SqlParameter[] parameters = {
                            new SqlParameter("@CustomerID",customer.CustomerID),
                            new SqlParameter("@CompanyName",customer.CompanyName),      
                            new SqlParameter("@Address",customer.Address),
                            new SqlParameter("@City",customer.City),
                            new SqlParameter("@State",customer.State),
                            new SqlParameter("@IntroDate",customer.IntroDate),
                            new SqlParameter("@CreditLimit",customer.CreditLimit)};

            string query = " UPDATE Customer SET CompanyName = @CompanyName,Address = @Address, "
                         + " City = @City,State = @State,IntroDate = @IntroDate,CreditLimit = @CreditLimit"
                         + " WHERE CustomerID = @CustomerID";

            var args = new DynamicParameters(new { });
            parameters.ToList().ForEach(p => args.Add(p.ParameterName, p.Value));
            try
            {
                this._db.Execute(query, args);
            }
            catch (Exception)
            {
                return false;
            }

            return true;
        }

        public bool DeleteCustomer(int Id)
        {
            int deletedCustomer = this._db.Execute("DELETE FROM Customer WHERE CustomerID = @Id", new { Id = Id });
            return deletedCustomer > 0;
        }
    }

CustomerService.cs

public class CustomerService
    {
    
        ICustomerRepository _repository;

        public CustomerService()
        {
            _repository = new CustomerRepository();
        }

        public List<Customer> GetAll()
        {
            return _repository.GetAll();
        }

        public Customer FindById(int Id)
        {
            return _repository.FindById(Id);
        }

        public bool AddCustomer(Customer customer)
        {
            return _repository.AddCustomer(customer);
        }

        public bool UpdateCustomer(Customer customer)
        {
            return _repository.UpdateCustomer(customer);
        }

        public bool DeleteCustomer(int Id)
        {
            return _repository.DeleteCustomer(Id);
        }
    }

The source code is available for download in Github Repository ASP.NET WebForm Dapper

Before running the program, execute first the create table script (CreateTableScript.txt) using SQL Server Query Window and
make sure to change the target database in the script. This project does not include client or server validation since this is
more focused on the CRUD functionality.



Cheers!