This is a c# library that provides a simple Generic Repository to fluently map model properties with database columns using Dapper to interact with the Database.

This Dapper wrapper allows you to fluently configure the mapping between model properties and database columns. This keeps your model clean of mapping attributes. Also you do not have to rewrite SQL query each time you want to interact with different table through Dapper. This guy handles all of that by providing functions that wrap specific dapper functions into generic functions. It does not only end there, this library allows us to also manipulate data in the database using stored procedures. Therefore, add that into what we call generic repository.

• Dapper
• System.Data.SqlClient
• Newtonsoft.Json

Install-Package GenericDapperRepo.Wrapper -Version 2.1.0

public interface IRepository<T>
{
  Task<IEnumerable<T>> GetAllAsync();
  Task<T> GetAsync(object id, string primaryKeyName);
  Task<IEnumerable<T>> GetAllAsync(object id, string primaryKeyName);
  Task<int> InsertAsync(T entity, params string[] namesOfPropertiesToBeExcluded);
  Task<int> UpdateAsync(string primaryKeyName, T entity, params string[] namesOfPropertiesToBeExcluded);
  Task<int> DeleteAsync(object id, string primaryKeyName);
}

• Update, Insert and Delete returns number of rows affected after executing the method
• This is how your specific repository declaration should look like

public class PersonRepository
{
  private readonly IRepository<Person> _personRepository;
  private const string TableName = "dbo.Persons";  //NB: prefix the table schema to your tableName
  private const string PrimaryKeyName = "Code";
  
  public PersonRepository(string connectionString)
  {
    _personRepository = new Repository<Person>(TableName, connectionString);
  }
}

• Your function implementation should look like this :

public async Task<IEnumerable<Person>> GetAllPeopleAsync()
{
  return await _personRepository.GetAllAsync();
}

• For other available functions check IRepository interface provided at the top

public interface IStoredProcProcessor
{
  Task<IEnumerable<T>> GetDataAsync<T>(string procName, object parameters = null);
  Task<int> ExecuteAsync(string procName, object parameters);
  Task<int> ExecuteInBulkAsync(string procName, object @object );
}

• Parameters are optional base on the created stored procedure
• This is the only way to create StoredProcProcessor instance :

  IStoredProcProcessor storedProcProcessor = new StoredProcProcessor(ConnectionString);

• GetDataAsync returns number of records in a list. This function is used to query data from Database
• ExecuteAsync() and ExecuteInBulkAsync() return number of rows affected after execution. These functions are to be used for data manipulation (Add, Delete and Update)

• NB : This function works with table-valued parameter approach.

async Task<int> ExecuteInBulkAsync(string procName, object @object );

• Constructing an object has been made easy by the use of a utility/helper class called DataTableProcessor. This utility maps data to DataTable but feel free to use your own helper if you want to.

public static class DataTableProcessor
{
  public static DataTable MapToDataTable<T>(List<T> list); //maps any model into DataTable
  public static DataTable MapToDataTable(string jsonObject); //maps json data into DataTable
}

  var listOfEmployees = ListOfEmployees();  // data type : List<RegisterEmployeeModel>
  var dataTable = DataTableProcessor.MapToDataTable(listOfEmployees);
            // OR
  var dataTable = DataTableProcessor.MapToDataTable(GetJsonData()); //takes json data
  
  var obj = new { employeeType = dataTable };
  var results = await sut.ExecuteInBulkAsync(storedProcName, obj);

• NB : employeeType is the variable name in the stored procedure. This name must match exactly the variable name in the store procedure. Variable name is case sensitive.
• You can add more than one property inside the object as long as your stored procedure takes those arguments(parameters)

If you have any questions, suggestions, bugs or want to contribute, please don't hesitate to contact :-

• spantshwa.lukho@gmail.com