EonaCat.Wrapper.Dapper 1.0.0

EonaCat.Wrapper.Dapper

Generic Repository using Dapper for .Net Core, .Net Framework, .Net Standard and many more. This library is a wrapper around Dapper that provides a generic repository for CRUD operations. It also provides a way to work with stored procedures.

It supports MicrosoftSQL, MySql, Oracle, PostGreSql and SqLite databases.

Usage

public interface IRepository<T>
{
	Task<IEnumerable<T>> GetAllAsync();
	Task<IEnumerable<T>> GetAllAsync(Dictionary<string, object> parameters);
	Task<T> GetAsync(Dictionary<string, object> parameters);
	Task<int> InsertAsync(T entity, params string[] excludeColumns);
	Task<int> UpdateAsync(Dictionary<string, object> parameters, T entity, params string[] excludeColumns);
	Task<int> InsertOrUpdateAsync(Dictionary<string, object> parameters, T entity, params string[] excludeColumns);
	Task<int> DeleteAsync(Dictionary<string, object> parameters);
}

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

public class ProductRepository
{
  private readonly IRepository<Product> _productRepository;
  private readonly string _tableName = "dbo.Products";

  public ProductRepository(string connectionString, database database)
  {
    _productRepository = new Repository<Product>(_tableName, connectionString, database);
  }
}

• Database names

public enum Database
{
  MicrosoftSQL = 1,
  MySql = 2,
  Oracle = 3,
  SqLite = 4,
  PostGreSql = 5
}

• Your method implementation could look like this :

public async Task<IEnumerable<Product>> GetAllProductsAsync()
{
  return await _productRepository.GetAllAsync();
}

public async Task<Product> GetProductAsync(Dictionary<string, object> parameters)
{
	return await _productRepository.GetAsync(parameters);
}

public async Task<int> UpdateProductAsync(Dictionary<string, object> parameters, Product product, params string[] excludeColumns)
{
	return await _productRepository.UpdateAsync(parameters, product, excludeColumns);
}

• Example on how to call the method to insert a new product:

var product = new Product
{
	Id = "52a55d78-fe76-49f0-83f0-378038d2d9a6",
	Name = "Product 1",
	Description = "Product 1 Description",
	Manufacturer = "Manufacturer 1",
	Color = "Red",
	EntryDate = DateTime.Now,
	Ingredients = "Ingredients 1",
	Type = "Collection",
};

var affectedRows = await UpdateProductAsync(parameters, product, "Id");

• • Example on how to call the method to update a product:

var parameters = new Dictionary<string, object>
{
    {"Id","52a55d78-fe76-49f0-83f0-378038d2d9a6"} //"Id" is the column name in the database and the value is the unique identifier of the record you want to fetch
                                                  // Here you can add more parameters if you want
}

var currentProduct = await GetProductAsync(parameters);
if (currentProduct != null)
{
	currentProduct.Name = "Product 2";
	currentProduct.Description = "Product 2 Description";
	currentProduct.Manufacturer = "Manufacturer 2";
	currentProduct.Color = "Blue";
	currentProduct.EntryDate = DateTime.Now;
	currentProduct.Ingredients = "Ingredients 2";
	currentProduct.Type = "Collection";

	// Update the product
	var affectedRows = await UpdateProductAsync(parameters, currentProduct, "Id");
}
else
{
	// Product not found
}

Stored Procedures

Usage

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

• Parameters are optional on the created stored procedure

  IStoredProcedure storedProcedure = new StoredProcedure(ConnectionString, database.PostGreSql);

• GetDataAsync returns a list of data from the stored procedure

  var results = await storedProcedure.GetDataAsync<Product>("GetProducts");

• ExecuteAsync() and ExecuteInBulkAsync() returns the number of rows affected after executing the stored procedure.

  var results = await storedProcedure.ExecuteAsync("InsertProduct", new { Name = "Product 1", Description = "Product 1 Description", Manufacturer = "Manufacturer 1", Color = "Red", EntryDate = DateTime.Now, Ingredients = "Ingredients 1", Type = "Collection" });

  var productList = ProductList();  // data type : List<ProductModel>
  var dataTable = DataTableHelper.MapToDataTable(productList);
			// OR
  var dataTable = DataTableHelper.MapToDataTable(GetJsonData()); //takes json data
  var model = new { productType = dataTable };
  var results = await storedProcedure.ExecuteInBulkAsync(storedProcedureName, model);

ExecuteInBulkAsync example

async Task<int> ExecuteInBulkAsync(string storedProcedureName, object model);

• Construct a model that will be used to map the data into a DataTable.
• The model should have the same properties as the stored procedure variables.

public static class DataTableHelper
{
  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 productList = productList();  // data type : List<ProductModel>
  var dataTable = DataTableHelper.MapToDataTable(productList);
            // OR
  var dataTable = DataTableHelper.MapToDataTable(GetJsonData()); //takes json data

  var model = new { productType = dataTable };
  var results = await storedProcedure.ExecuteInBulkAsync(storedProcedureName, model);

• NOTICE :
• productType is the variable name in the stored procedure.
• This name should match the variable name in the stored procedure. (Variable name is case sensitive).