Results 1 to 1 of 1

Thread: ASP.NET Web Forms Project Using Dapper ORM

  1. #1

    Thread Starter
    Frenzied Member KGComputers's Avatar
    Join Date
    Dec 2005
    Location
    Cebu, PH
    Posts
    2,020

    ASP.NET Web Forms Project Using Dapper ORM

    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
    Code:
    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
    Code:
    public interface ICustomerRepository
        {
            List<Customer> GetAll();
            Customer FindById(int Id);
            bool AddCustomer(Customer customer);
            bool UpdateCustomer(Customer customer);
            bool DeleteCustomer(int Id);
        }
    CustomerRepository.cs
    Code:
    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
    Code:
    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.

    Name:  Customer Information.jpg
Views: 2257
Size:  23.6 KB

    Cheers!
    Last edited by KGComputers; Feb 22nd, 2017 at 10:21 PM. Reason: updated FindById()
    CodeBank: VB.NET & C#.NET | ASP.NET
    Programming: C# | VB.NET
    Blogs: Personal | Programming
    Projects: GitHub | jsFiddle
    ___________________________________________________________________________________

    Rating someone's post is a way of saying Thanks...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width