Using Dapper ORM In ASP.NET Web Forms

Hello,

This is a simple tutorial of using Dapper Micro ORM in an ASP.NET Webform application. According to Wikipedia, Dapper is an object-relational mapping (ORM) product for the Microsoft .NET platform: it provides a framework for mapping an object-oriented domain model to a traditional relational database. Its purpose is to relieve the developer from a significant portion of relational data persistence-related programming tasks.
The key feature of Dapper is performance as presented in Dapper website (github). It is second to Hand coded SQLDataReader class when querying specific number of records.
To get started with, create an Empty ASP.NET WebForm project and then add the Dapper to our project via NuGet. Make sure to alter the connection string in Web.Config to point to your database. Then we need to add one interface and three classes in our Models folder which applies the idea of Repository.
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 (This class references System.Configuration, Dapper, System.Data and System.Data.SqlClient namespaces. Note that this class also includes passing SqlParameters to DynamicParameters of Dapper given that using SQLParameters is the recommended practice for querying the database.

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("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 This class will be used by the WebForm page. So instead of calling the repository class directly, the service class serves as middle tier between the ASPX page and Repository class.

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);
 }
}

Next, we add a WebForm page to the project that will demonstrate Create/Update/Delete on dummy records using the CustomerService.cs class. This page does not apply any validation so it's up to you to modify the code by applying server or client side validations.
ASPX Code

<table id="tblEntryForm">
 <tr>
  <td colspan="3">
   <div>Customer Information Entry Form</div>
  </td>
 </tr>
 <tr>
  <td>
   CompanyName:
  </td>
  <td>                    
   <asp:TextBox ID="txtCompanyName"  runat="server" Width="150" />
  </td>
 </tr>
 <tr>                
  <td>
   Address:                    
  </td>
  <td>
   <asp:TextBox ID="txtAddress"  runat="server" Width="150" />
  </td>
 </tr>
 <tr>                
  <td>
   City:                    
  </td>
  <td>
   <asp:TextBox ID="txtCity"  runat="server" Width="150" />
  </td>
 </tr>
  <tr>                
  <td>
   State:                    
  </td>
  <td>
   <asp:TextBox ID="txtState"  runat="server" Width="150" />
  </td>
 </tr>
  <tr>                
  <td>
   Intro Date:                    
  </td>
  <td>
   <asp:TextBox ID="txtIntroDate"  type="date" runat="server" Width="150" />
  </td> 
 </tr>
  <tr>                
  <td>
   Credit Limit:                    
  </td>
  <td>
   <asp:TextBox ID="txtCreditLimit"  runat="server" Width="150" />
  </td>                  
 </tr>
 <tr>                
  <td colspan="2">
   <asp:Button ID="btnSubmit" runat="server" Text="Save" OnClick="btnAdd_Click"/>
  </td>
 </tr>
</table>
<div>
<br />
<asp:GridView ID="gvCustomer" ValidateRequestMode="Enabled" runat="server" AllowPaging="True" AutoGenerateColumns="False" DataKeyNames="CustomerID" 
 BackColor="White" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4" OnPageIndexChanging="gvCustomer_PageIndexChanging"
 OnRowDataBound="OnRowDataBound" OnRowEditing="OnRowEditing" OnRowCancelingEdit="OnRowCancelingEdit"
 OnRowUpdating="OnRowUpdating" OnRowDeleting="OnRowDeleting" EmptyDataText="Empty Customer Database">
 <Columns>                
  <asp:TemplateField HeaderText="Company Name" ItemStyle-Width="130">
   <ItemTemplate>
    <asp:Label ID="lblCompanyName" runat="server" Text='<%# Eval("CompanyName") %>'></asp:Label>
   </ItemTemplate>
   <EditItemTemplate>
    <asp:TextBox ID="txtCompanyName1"  runat="server" Text='<%# Eval("CompanyName") %>'></asp:TextBox>
   </EditItemTemplate>
  </asp:TemplateField>
  <asp:TemplateField HeaderText="Address" ItemStyle-Width="100">
   <ItemTemplate>
    <asp:Label ID="lblAddress" runat="server" Text='<%# Eval("Address") %>'></asp:Label>
   </ItemTemplate>
   <EditItemTemplate>
    <asp:TextBox ID="txtAddress1"  runat="server" Text='<%# Eval("Address") %>'></asp:TextBox>
   </EditItemTemplate>
  </asp:TemplateField>
  <asp:TemplateField HeaderText="City" ItemStyle-Width="100">
   <ItemTemplate>
    <asp:Label ID="lblCity" runat="server" Text='<%# Eval("City") %>'></asp:Label>
   </ItemTemplate>
   <EditItemTemplate>
    <asp:TextBox ID="txtCity1"  runat="server" Text='<%# Eval("City") %>'></asp:TextBox>
   </EditItemTemplate>
  </asp:TemplateField>
  <asp:TemplateField HeaderText="State" ItemStyle-Width="100">
   <ItemTemplate>
    <asp:Label ID="lblState" runat="server" Text='<%# Eval("State") %>'></asp:Label>
   </ItemTemplate>
   <EditItemTemplate>
    <asp:TextBox ID="txtState1"  runat="server" Text='<%# Eval("State") %>'></asp:TextBox>
   </EditItemTemplate>
  </asp:TemplateField>
  <asp:TemplateField HeaderText="Intro Date" ItemStyle-Width="100">
   <ItemTemplate>
    <asp:Label ID="lblIntroDate" runat="server" Text='<%# Eval("IntroDate", "{0:yyyy-MM-dd}") %>'></asp:Label>
   </ItemTemplate>
   <EditItemTemplate>
    <asp:TextBox ID="txtIntroDate1"  runat="server" Text='<%# Eval("IntroDate") %>'></asp:TextBox>
   </EditItemTemplate>
  </asp:TemplateField>
  <asp:TemplateField HeaderText="Credit Limit" ItemStyle-Width="100">
   <ItemTemplate>
    <asp:Label ID="lblCreditLimit" runat="server" Text='<%# Eval("CreditLimit", "{0:N2}") %>'></asp:Label>
   </ItemTemplate>
   <EditItemTemplate>
    <asp:TextBox ID="txtCreditLimit1"  runat="server" Text='<%# Eval("CreditLimit") %>'></asp:TextBox>
   </EditItemTemplate>
  </asp:TemplateField>
  <asp:CommandField ShowEditButton="True" ShowDeleteButton="True" ButtonType="Button"/>       
 </Columns>
 <FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
 <HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
 <PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
 <RowStyle BackColor="White" ForeColor="#003399" />
 <SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
 <SortedAscendingCellStyle BackColor="#EDF6F6" />
 <SortedAscendingHeaderStyle BackColor="#0D4AC4" />
 <SortedDescendingCellStyle BackColor="#D6DFDF" />
 <SortedDescendingHeaderStyle BackColor="#002876" />
</asp:GridView>
<asp:Label ID="lblMessage" runat="server"></asp:Label>

Code Behind

public partial class CRUDSampleNoValidation : System.Web.UI.Page
{
 private Customer customer;
 private bool result = false;
 private CustomerService customerService = new CustomerService();

 protected void Page_Load(object sender, EventArgs e)
 {
  if (!Page.IsPostBack)
  {
   BindGrid();
  }
 }

 protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
 {
  if (e.Row.RowType == DataControlRowType.DataRow)
  {
   string item = e.Row.Cells[0].Text;
   foreach (Button button in e.Row.Cells[6].Controls.OfType<Button>())
   {
    if (button.CommandName == "Delete")
    {
     button.Attributes["onclick"] = "if(!confirm('Do you want to delete record?')){ return false; };";
    }
   }
  }
 }

 protected void OnRowDeleting(object sender, GridViewDeleteEventArgs e)
 {
  int CustomerId = Convert.ToInt32(gvCustomer.DataKeys[e.RowIndex].Values[0]);
  result = customerService.DeleteCustomer(CustomerId);
  if (result)
  {
   lblMessage.Text = string.Empty;
   lblMessage.Text = "Successfully deleted the reccord!";
  }

  BindGrid();
 }

 protected void OnRowEditing(object sender, GridViewEditEventArgs e)
 {
  gvCustomer.EditIndex = e.NewEditIndex;
  lblMessage.Text = string.Empty;
  this.BindGrid();
 }

 protected void OnRowUpdating(object sender, GridViewUpdateEventArgs e)
 {
  customer = new Customer();
  GridViewRow row = gvCustomer.Rows[e.RowIndex];
  customer.CustomerID = Convert.ToInt32(gvCustomer.DataKeys[e.RowIndex].Values[0]);
  customer.Address = (row.FindControl("txtAddress1") as TextBox).Text;
  customer.City = (row.FindControl("txtCity1") as TextBox).Text;
  customer.State = (row.FindControl("txtState1") as TextBox).Text;
  customer.CompanyName = (row.FindControl("txtCompanyName1") as TextBox).Text;
  customer.IntroDate = Convert.ToDateTime((row.FindControl("txtIntroDate1") as TextBox).Text);
  customer.CreditLimit = Convert.ToDecimal((row.FindControl("txtCreditLimit1") as TextBox).Text);
  result = customerService.UpdateCustomer(customer);

  if (result)
  {
   lblMessage.Text = string.Empty;
   lblMessage.Text = "Successfully updated the reccord";
  }

  gvCustomer.EditIndex = -1;
  BindGrid();
 }

 protected void gvCustomer_PageIndexChanging(object sender, GridViewPageEventArgs e)
 {
  gvCustomer.PageIndex = e.NewPageIndex;
  lblMessage.Text = string.Empty;
  BindGrid();
 }

 protected void OnRowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
 {
  gvCustomer.EditIndex = -1;
  lblMessage.Text = string.Empty;
  this.BindGrid();
 }

 protected void btnAdd_Click(object sender, EventArgs e)
 {
  customer = new Customer();
  customer.Address = txtAddress.Text;
  customer.City = txtCity.Text;
  customer.CompanyName = txtCompanyName.Text;
  customer.State = txtState.Text;
  customer.CreditLimit = Convert.ToDecimal(txtCreditLimit.Text);
  customer.IntroDate = Convert.ToDateTime(txtIntroDate.Text);
  result = customerService.AddCustomer(customer);

  if (result)
  {
   lblMessage.Text = string.Empty;
   lblMessage.Text = "Successfully added a new reccord";
  }

  BindGrid();
 }

 private void BindGrid()
 {
  gvCustomer.DataSource = customerService.GetAll();
  gvCustomer.DataBind();
 }           
}

Screenshots



The entire project and create table script are available for download in Github Repository.