Click here to Skip to main content
14,601,203 members

Dapper Generic Repository

Rate this:
4.84 (12 votes)
Please Sign up or sign in to vote.
4.84 (12 votes)
10 May 2017CPOL
Generic Repository based in Dapper, approaching its philosophy to Entity Framework, removing the string queries far as possible, primarily for delete, update, insert and for All methods queries.

Introduction

Dapper is a micro ORM product for Microsoft .NET Framework. It provides a set of actions for mapping POCO objects to Relational Database.

It was developed by StackExchange team for your web sites (Stack Overflow, Mathematics, etc) because Linq To Sql did not provide them adequate performance.

Dapper is open source and has Apache License 2.0 or the MIT License and is easily installable by Nuget:

Dapper GitHub project.

Dapper Nuget.

Index

  • Dapper features
  • Database for the Test Project
  • DPGenericRepository class
  • Creating a DPGenericRepository object
  • Methods Description
    • All / AllAsync
    • GetData(object parameters) / GetDataAsync
    • GetData(string qry, object parameters) / GetDataAsync
    • Find(object pks) / FindAsync
    • Add(TEntity entity) / AddAsync
    • Remove(object pk) / RemoveAsync
    • Update(TEntity entity, object pk) / RemoveAsync
    • InsertOrUpdate(TEntity entity, object pk) / RemoveAsync

Dapper features

Its main advantages as compared with other ORMs are:

  • Performance, it’s a faster ORM in .NET.
  • Fewer lines of code.
  • Object mapper.
  • Choice of static/dynamic object binding.
  • Easy handling of SQL query.
  • Multiple query support.
  • Support and easy handling of stored procedures.
  • Operating directly on IDBConnection class.
  • Bulk data insert functionality.

 

Data from Wikipedia.

The biggest disadvantage of this wonderful ORM is the return to queries in strings, because is less useful and we lost the syntactic errors in compilation time.

 

My Work

I have created a Generic Repository based in Dapper, approaching its philosophy to Entity Framework, removing the string queries far as possible, primarily for delete, update, insert and for All methods queries.

I tried to create a custom library with comfort of Entity Framework and with performance of Dapper.

I have tested DPGenericRepository with Sql Server and Oracle, but it must compatible with all databases Dapper supported.

I have other genericRepositories libraries of Entity Framework and EntityFramework+Dapper, and if my times allowed, I explain in others articles. This libraries are compatible with each, and they will be easily replaced.

This project is open source and it is available in Git Hub.

We can to install through Nuget:

Image 1

 

Database for the Test Project

The DataBase Tests is inside of project and must run automatically in the test project:

Image 2

It has two tables:

Image 3

Image 4

 

DPGenericRepository Class

DPGenericRepository is a principal class of MoralesLarios.Data.Dapper namespace. It has a functionality for create and transform our classes in GenericRepositories for Dapper.

The next image shows the principal class for this article DPGenericRepository and the implements interfaces. We can see the other generics repositories EFGenericRepository and MLGenericRepository that we will see in the following deliveries with your compatibilities.

Image 5IGenericRepository Interface:

public interface IGenericRepository<TEntity> : IDisposable where TEntity : class
{
    IEnumerable<TEntity>       All();
    Task<IEnumerable<TEntity>> AllAsync();
    IEnumerable<TEntity>       GetData(string qry, object parameters);
    Task<IEnumerable<TEntity>> GetDataAsync(string qry, object parameters);
    TEntity                    Find(object pksFields);
    Task<TEntity>              FindAsync(object pksFields);
    int                        Add(TEntity entity);
    Task<int>                  AddAsync(TEntity entity);
    int                        Add(IEnumerable<TEntity> entities);
    Task<int>                  AddAsync(IEnumerable<TEntity> entities);
    void                       Remove(object key);
    Task                       RemoveAsync(object key);
    int                        Update(TEntity entity, object pks);
    Task<int>                  UpdateAsync(TEntity entity, object pks);
    int                        InstertOrUpdate(TEntity entity, object pks);
    Task<int>                  InstertOrUpdateAsync(TEntity entity, object pks);
}

IDPGenericRepository Interface:

public interface IDPGenericRepository<TEntity> : IGenericRepository<TEntity> where TEntity : class
{
    IEnumerable<TEntity>       GetData(object filter);
    Task<IEnumerable<TEntity>> GetDataAsync(object filter);
}

 

DPGenericRepository implements IDPGenericRepository which, in turn implments IGenericRepository for compatibility with others Generics Repositories of the library.

DPGenericRepository contains a set of regular use methods in database, but we can extend your functionality through inherits.

 

Creating a DPGenericRepository Object

Dapper is based in an extension class for the IDbConnection interface, therefore DPGenericRepository need an object IDbConnection injected in your constructor.

We can create a DPGenericRepository of two different ways:

1.- Directly in code:

using (var conn = new SqlConnection(cs))
{

    var departmentRepository = new DPGenericRepository<Departments>(conn);

// inject IDbConnection

}

We can use a second constructor with a new char parameter, this new parameter ‘parameterIdentified’ show the char SQL parameter indicator. ‘@’ for default, give compatibility with Sql Server, for Oracle we use ‘:’.

Example for Oracle:

using (var conn = new SqlConnection(cs))
{

    var departmentRepository = new DPGenericRepository<Departments>(conn, parameterIdentified:':'
);

// inject IDbConnection

}

2.- For Inheritance:

public class DepartmentRepository : DPGenericRepository<Departments>
{
    public DepartmentRepository(IDbConnection conn, char parameterIdentified = '@') : base(conn, parameterIdentified)
    {

    }

}

 

Methods Descriptions

Let’s try to explain all DPGenericRepository methods, with an example for each one.

 

Note:

It’s important to point that any methods take an object parameter with ‘pk’ or ‘parameters’ definitions. These methods create with an anonymous type with an alias type equals to relational type in the POCO object.

Image 6

 

Let’s see your methods:

 

All / AllAsync

The All method obtains all data of the table:

var departmentRepository = new DPGenericRepository<Departments>(conn);

var allDepartments = departmentRepository.All();

 

GetData(object parameters) / GetDataAsync

GetData method with one parameter built a query for Dapper with an equals sequences of ‘ands’ for parameter value.

var employeesRepository = new DPGenericRepository<Employees>(conn);

object parameters = new { Name = "Peter", Age = 30, Incomes = 35000 };

var employeesPeter30years35000Incomes = employeesRepository.GetData(parameters);

//  ** This is the automatic query value **
//  "SELECT * FROM EMPLOYEES " + 
//  " WHERE NAME    = @Name " +
//  " AND   AGE     = @Age " + 
//  " AND   INCOMES = @Incomes";

 

GetData(string qry, object parameters) / GetDataAsync

GetData method with two parameters, is a method with less automation, because it isn’t possible infer data and your execution is practically equal with a normal query dapper execute.

var employeesRepository = new DPGenericRepository<Employees>(conn);

string qry = "SELECT * FROM EMPLOYEES WHERE AGE > @Age AND INCOMES > @Incomes";

object parameters = new { Age = 30, Incomes = 35000 };

var employeesMore30yearsMore35000 = employeesRepository.GetData(qry, parameters);

 

Find(object pks) / FindAsync

Find method is very similar to GetData(object paramaters). This signature exists for down compatibility with Entity Framework GenericRepository, in which it has sense for its architecture. The parameter pks, would be the pks properties in the table ordered.

var employeesRepository = new DPGenericRepository<Employees>(conn);

object pk = new { EmployeeID = 3 };

var employee3 = employeesRepository.Find(pk);

 

Add(TEntity entity) / AddAsync

Add an entity in Database.

var employeesRepository = new DPGenericRepository<Employees>(conn);

var newEmployee = new Employees
{
    Name         = "Lucas",
    Age          = 19,
    Incomes      = 15000,
    DepartmentID = 3,
    EntryDate    = DateTime.Today
};

var rowsInserted = employeesRepository.Add(newEmployee);

 

 

Add(IEnumerable<TEntity> entities) / AddAsync

Insert a set of entities in Database. For the Dapper versatility, this insertion is carried through bulk copy. Is a very faster process.

var employeesRepository = new DPGenericRepository<Employees>(conn);

var newEmployees = new List<Employees>()
{
    new Employees
    {
        Name         = "Lucas",
        Age          = 19,
        Incomes      = 15000,
        DepartmentID = 3,
        EntryDate    = DateTime.Today
    },
    new Employees
    {
        Name         = "Edgar",
        Age          = 64,
        Incomes      = 100000,
        DepartmentID = 3,
        EntryDate    = DateTime.Today
    }
};

var rowsInserted = employeesRepository.Add(newEmployees);

 

Remove(object pk) / RemoveAsync

Remove row of database from pk.

var employeesRepository = new DPGenericRepository<Employees>(conn);

object pk = new { EmployeeID = 5 };

employeesRepository.Remove(pk);

 

Update(TEntity entity, object pk) / RemoveAsync

Update the row in database from pk.

object pk = new { EmployeeID = 1 };

var employeeOne = employeesRepository.Find(pk);

employeeOne.DepartmentID = 2;
employeeOne.Incomes      = 300000;

employeesRepository.Update(employeeOne, pk);

 

InsertOrUpdate(TEntity entity, object pk) / RemoveAsync

This method checks if entity exists in database through its pk. If row exists, update its value and if it doesn’t exist insert the entitiy in database.

It is practical with a tipical screen for insert/update values, because we can reuse the code.

var employeesRepository = new DPGenericRepository<Employees>(conn);

var employee = myFile.GetEmployee();

var pk = new { employee.EmployeeID };

/// we don't know if employee exists
/// InsertOrUpdate method be responsible for INSERT OR UPDATE
employeesRepository.InstertOrUpdate(employee, pk);

 

 

 

You can test all methods y the Test Project.

 

 

 

 

 

 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Juan Francisco Morales Larios
Software Developer (Senior) Cecabank
Spain Spain
MVP C# Corner 2017

MAP Microsoft Active Professional 2014

MCPD - Designing and Developing Windows Applications .NET Framework 4
MCTS - Windows Applications Development .NET Framework 4
MCTS - Accessing Data Development .NET Framework 4
MCTS - WCF Development .NET Framework 4

Comments and Discussions

 
QuestionUsing inner join Pin
yucel_cakir31-Dec-19 0:55
Memberyucel_cakir31-Dec-19 0:55 
QuestionEF6 vs Dapper vs YourLibrary Pin
Ersin Kecis29-Mar-18 23:28
professionalErsin Kecis29-Mar-18 23:28 
QuestionDapper Package Installation Pin
Member 1354288026-Nov-17 20:23
MemberMember 1354288026-Nov-17 20:23 
AnswerRe: Dapper Package Installation Pin
Juan Francisco Morales Larios26-Nov-17 21:00
MemberJuan Francisco Morales Larios26-Nov-17 21:00 
QuestionTestDB.mdf Pin
mittjas26-Jun-17 3:59
Membermittjas26-Jun-17 3:59 
AnswerRe: TestDB.mdf Pin
Juan Francisco Morales Larios26-Jun-17 7:57
MemberJuan Francisco Morales Larios26-Jun-17 7:57 
GeneralRe: TestDB.mdf Pin
mittjas26-Jun-17 8:18
Membermittjas26-Jun-17 8:18 
QuestionLINQ Expresion Pin
Surimans14-May-17 23:27
professionalSurimans14-May-17 23:27 
AnswerRe: LINQ Expresion Pin
Juan Francisco Morales Larios16-May-17 9:40
MemberJuan Francisco Morales Larios16-May-17 9:40 
AnswerRe: LINQ Expresion Pin
zen3230-Nov-17 4:04
Memberzen3230-Nov-17 4:04 
QuestionHow to create the DAL Layer Pin
melnac11-May-17 22:38
Membermelnac11-May-17 22:38 
AnswerRe: How to create the DAL Layer Pin
Juan Francisco Morales Larios14-May-17 0:21
MemberJuan Francisco Morales Larios14-May-17 0:21 
GeneralMy vote of 5 Pin
RosalieGuen10-May-17 21:52
MemberRosalieGuen10-May-17 21:52 
GeneralRe: My vote of 5 Pin
Juan Francisco Morales Larios11-May-17 2:48
MemberJuan Francisco Morales Larios11-May-17 2:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Article
Posted 10 May 2017

Tagged as

Stats

47.8K views
4.2K downloads
27 bookmarked