How to get up and running with Dapper ORM on Asp .NET

Ndamulelo Nemakhavhani

Feb 12, 2019·2 min read

Example: Create a basic CRUD web app to manage events.

First things first

1. Create a new Empty ASP .NET Core Web Project
2. Create a new database with a table called <anything-really>
3. Install dapper using NuGet PM or use any other method you like

PM> Install-Package Dapper

A. Define your data model(s)

• In the root of the project, create a file inside ./Models/Event.cs
• Define your model as follows:

namespace Models.<Project-Name>{public class Event{public int Id { get; set; }public string Venue{ get; set; }public DateTime Date{ get; set; }  }}
# This assumes that you have created a table called Events with 3 columns (Id, Venue and Date) respectively

• Note that you can add any properties you like, provided that they match with the column names in your table(s)

B. Create a service or class to access your data

• In this case, we will create a service called EventService.cs inside a folder called Services

# ./Services/EventService.csusing Dapper;using Entities.IDap;using System.Collections.Generic;using System.Data;using System.Data.SqlClient;namespace Services.<project-name>{  public class EventsDBService{     public List<Event> GetAllEvents(){       using (IDbConnection db = new SqlConnection(
              "your-connection- string"))       { return db.Query<Event>("Select * From Events").AsList();}
    }  }}

• Example of a connection string:

"Server=(localdb)\\mssqllocaldb;Database=EventsDB;Trusted_Connection=True;MultipleActiveResultSets=true"
# This will connect to your local MSQL Db named EventsDB

C. Check if everything works as expected :)

using System;using System.Collections.Generic;using Entities.<Project-name>;
....using Services.<Project-name>;namespace <Project-Name>{public class Startup{
................// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.public void Configure(IApplicationBuilder app, IHostingEnvironment env){
..............app.Run(async (context) =>{var play = new EventsService();List<Event> events= play.GetEvents();await context.Response.WriteAsync($"First Event Is Happening At: {events[0].Venue}");   });  }}

• So far we have only implemented the R in our desired CRUD functionality, but it shouldn't be too hard to implement the remaining portions i.e. CUD
• You can refer to the official Dapper documentation if you get stuck.