Post
Ask Question

Introduction To Dapper Generic Repository

Dapper is a micro ORM product for Microsoft .NET Framework. It provides a set of actions for mapping POCO objects to Relational databases.with adequate performance.
Dapper is open-source and has Apache License 2.0 or the MIT License and is easily installable by NuGet.
Index
  • Dapper features
  • Database for the Test Project
  • La clase DPGenericRepository
  • Create DPGenericRepository object
  • Methods descriptions
     
    • 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 given below.
  • Performance is faster ORM in .NET.
  • Few lines of code.
  • Object Mapper.
  • Choice of static/dynamic object binding.
  • Easy handling of SQL query.
  • Multiple query support.
  • Support and easy handling of the 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 it is less useful and we lose the syntactic errors in compilation time.
 
I have created a Generic Repository based in Dapper, approaching its philosophy to Entity Framework, removing the string queries as far as possible, primarily for delete, update, insert, and for all method's queries.
 
I tried to create a custom library with the comfort of Entity Framework and with the performance of Dapper.
 
I have tested DPGenericRepository with Sql Server and Oracle, but it must be compatible with all databases that are Dapper supported.
 
I have other genericRepositories libraries of Entity Framework and EntityFramework+Dapper, and if my time allows, I will explain it in other articles. These libraries are compatible with each, and they will be easily replaced.
 
This project is open source and it is available in GitHub.
 
We can install through NuGet,
 
.NET
 
Database for the Test Project
 
Proceed, as shown below.
 
.NET
 
It has two tables given below.
 
.NET
 
.NET
 

DPGenericRepository Class

 
DPGenericRepository is a principal class of MoralesLarios.Data.Dapper namespace. It has the functionality to create and transform our classes in GenericRepositories for Dapper.
 
The next image shows the principal class for this article DPGenericRepository and then implements the interfaces. We can see the other generics repositories EFGenericRepository and MLGenericRepository, which will be visible in the deliveries given below with your compatibilities.
 
.NET
 
IGenericRepository Interface
  1. public interface IGenericRepository<TEntity> : IDisposable where TEntity : class  
  2. {  
  3.     IEnumerable<TEntity>       All();  
  4.     Task<IEnumerable<TEntity>> AllAsync();  
  5.     IEnumerable<TEntity>       GetData(string qry, object parameters);  
  6.     Task<IEnumerable<TEntity>> GetDataAsync(string qry, object parameters);  
  7.     TEntity                    Find(object pksFields);  
  8.     Task<TEntity>              FindAsync(object pksFields);  
  9.     int                        Add(TEntity entity);  
  10.     Task<int>                  AddAsync(TEntity entity);  
  11.     int                        Add(IEnumerable<TEntity> entities);  
  12.     Task<int>                  AddAsync(IEnumerable<TEntity> entities);  
  13.     void                       Remove(object key);  
  14.     Task                       RemoveAsync(object key);  
  15.     int                        Update(TEntity entity, object pks);  
  16.     Task<int>                  UpdateAsync(TEntity entity, object pks);  
  17.     int                        InstertOrUpdate(TEntity entity, object pks);  
  18.     Task<int>                  InstertOrUpdateAsync(TEntity entity, object pks);  
  19. }   
IDPGenericRepository Interface
  1. public interface IDPGenericRepository<TEntity> : IGenericRepository<TEntity> where TEntity : class  
  2. {  
  3.     IEnumerable<TEntity>       GetData(object filter);  
  4.     Task<IEnumerable<TEntity>> GetDataAsync(object filter);  
  5. }   
DPGenericRepository implements the IDPGenericRepository, which in turn implements IGenericRepository for compatibility with other Generic Repositories of the library.
DPGenericRepository contains a set of the regular use of methods in the database, but we can extend your functionality through inheritance.
 
Creating a DPGenericRepository Object
 
Dapper is based on an extension class for the IDbConnection interface, therefore DPGenericRepository needs an object IDbConnection injected in your constructor.
We can create a DPGenericRepository in two different ways, which are given below.
 
Directly in code 
  1. using (var conn = new SqlConnection(cs))  
  2. {   
  3.     var departmentRepository = new DPGenericRepository<Departments>(conn);  
  4. // inject IDbConnection  
  5.    
  6. }   
We can use a second constructor with a new char parameter. This new parameter ‘parameterIdentified’ shows the char SQL parameter indicator. ‘@’ for default, gives compatibility with SQL Server, for Oracle we use ‘:’.
 
Example for Oracle 
  1. using (var conn = new SqlConnection(cs))  
  2. {     
  3.     var departmentRepository = new DPGenericRepository<Departments>(conn, parameterIdentified:':'  
  4. );  
  5. // inject IDbConnection  
  6.    
  7. }   
For Inheritance 
  1. public class DepartmentRepository : DPGenericRepository<Departments>  
  2. {  
  3.     public DepartmentRepository(IDbConnection conn, char parameterIdentified = '@') : base(conn, parameterIdentified)  
  4.     {  
  5.    
  6.     }  
  7.    
  8. }   
Change the default value parameterIdentified to ‘:’ for Oracle. 
  1. public class DepartmentRepository : DPGenericRepository<Departments>  
  2. {  
  3.     public DepartmentRepository(IDbConnection conn, char parameterIdentified = ':') : base(conn, parameterIdentified)  
  4.     {  
  5.    
  6.     }  
  7.    
  8. }   
Methods Descriptions
 
Let’s try to explain all DPGenericRepository methods with an example for each one.
 
Note
 
It’s important to point out that any method takes an object parameter with ‘pk’ or ‘parameters’ definitions. These methods create an anonymous type with an alias type equals to the relational type in the POCO object.
 
.NET
 
Let’s see your methods:
 
All / AllAsync
 
The All method obtains all the data of the table. 
  1. var departmentRepository = new DPGenericRepository<Departments>(conn);  
  2. var allDepartments = departmentRepository.All();   
GetData(object parameters) / GetDataAsync
 
GetData method with one parameter has built a query for Dapper with an equals sequences of ‘ands’ for parameter value. 
  1. var employeesRepository = new DPGenericRepository<Employees>(conn);   
  2. object parameters = new { Name = "Peter", Age = 30, Incomes = 35000 };   
  3. var employeesPeter30years35000Incomes = employeesRepository.GetData(parameters);  
  4.    
  5. //  ** This is the automatic query value **  
  6. //  "SELECT * FROM EMPLOYEES " +   
  7. //  " WHERE NAME    = @Name " +  
  8. //  " AND   AGE     = @Age " +   
  9. //  " 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 to infer data and your execution is practically equal with a normal query Dapper execute. 
  1. var employeesRepository = new DPGenericRepository<Employees>(conn);  
  2. string qry = "SELECT * FROM EMPLOYEES WHERE AGE > @Age AND INCOMES > @Incomes";  
  3. object parameters = new { Age = 30, Incomes = 35000 };  
  4. 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 a sense for its architecture. The parameter pks, would be the pks properties in the table ordered. 
  1. var employeesRepository = new DPGenericRepository<Employees>(conn);   
  2. object pk = new { EmployeeID = 3 };   
  3. var employee3 = employeesRepository.Find(pk);   
Add(TEntity entity) / AddAsync
 
Add an entity in the Database. 
  1. var employeesRepository = new DPGenericRepository<Employees>(conn);  
  2.    
  3. var newEmployee = new Employees  
  4. {  
  5.     Name         = "Lucas",  
  6.     Age          = 19,  
  7.     Incomes      = 15000,  
  8.     DepartmentID = 3,  
  9.     EntryDate    = DateTime.Today  
  10. };  
  11.    
  12. var rowsInserted = employeesRepository.Add(newEmployee);   
Add(IEnumerable<TEntity> entities) / AddAsync
 
Insert a set of entities in the Database. For the Dapper versatility, this insertion is carried through bulk copy. Is a much faster process. 
  1. var employeesRepository = new DPGenericRepository<Employees>(conn);  
  2.    
  3. var newEmployees = new List<Employees>()  
  4. {  
  5.     new Employees  
  6.     {  
  7.         Name         = "Lucas",  
  8.         Age          = 19,  
  9.         Incomes      = 15000,  
  10.         DepartmentID = 3,  
  11.         EntryDate    = DateTime.Today  
  12.     },  
  13.     new Employees  
  14.     {  
  15.         Name         = "Edgar",  
  16.         Age          = 64,  
  17.         Incomes      = 100000,  
  18.         DepartmentID = 3,  
  19.         EntryDate    = DateTime.Today  
  20.     }  
  21. };  
  22.    
  23.    
  24. var rowsInserted = employeesRepository.Add(newEmployees);   
Remove(object pk) / RemoveAsync
 
Remove a row of the database from pk. 
  1. var employeesRepository = new DPGenericRepository<Employees>(conn);  
  2.    
  3. object pk = new { EmployeeID = 5 };  
  4.    
  5. employeesRepository.Remove(pk);  
Update(TEntity entity, object pk) / RemoveAsync
 
Update the row in the database from pk. 
  1. var employeesRepository = new DPGenericRepository<Employees>(conn);  
  2.    
  3. object pk = new { EmployeeID = 1 };  
  4.    
  5. var employeeOne = employeesRepository.Find(pk);  
  6.    
  7. employeeOne.DepartmentID = 2;  
  8. employeeOne.Incomes = 300000;  
  9.    
  10. employeesRepository.Update(employeeOne, pk);   
InsertOrUpdate(TEntity entity, object pk) / RemoveAsync
 
This method checks if an entity exists in the database through its pk. If a row exists, update its value, and if it doesn’t exist insert the entity in the database.
 
It is practical with a typical screen for insert/update values because we can reuse the code. 
  1. var employeesRepository = new DPGenericRepository<Employees>(conn);  
  2.    
  3. var employee = myFile.GetEmployee();  
  4.    
  5. var pk = new { employee.EmployeeID };  
  6.    
  7. /// we don't know if employee exists  
  8. /// InsertOrUpdate method be responsible for INSERT OR UPDATE  
  9. employeesRepository.InstertOrUpdate(employee, pk);   
You can test all methods in the Test Project.


Similar Articles