Dapper Examples

Configure Unit of Work into ServiceCollection

This following line will register Unit of work pattern into service collection. It'll also configure database provider like Sqlite or Sql Server. As unit of work is registered into service collection as Transient, the repositories and the dBConnection will automatically be disposed

//***
//*** Using Sqlite with Dapper
//***
serviceCollection.ConfigureUnitOfWork<SqliteConnection>($"{ConnectionString}");

//***
//*** Using Sql Server with Dapper
//***
serviceCollection.ConfigureUnitOfWork<SqlConnection>($"{ConnectionString}");

Usages

IBaseUowRepository can be injected into class where database operations are needed

For eg.

private readonly IBaseUowRepository _uowRepository;

public DapperController(IBaseUowRepository uowRepository)
{
       _uowRepository= uowRepository;
}

Lets say that we have an entity Product in the database with an identity column

public class Product
    {
        [Identity]
        [Key]
        public int ProductId { get; set; }

        public string Name { get; set; }

        public int Value { get; set; }
    }

Get

To get all records from the database

            //***
            //*** Get all records from the database
            //***
            var products = await _uowRepository.Repository<Product>().GetAsync();

Get all records from the database based on filters/expression

            //***
            //*** Get all records from the database based on filters/expression
            //***
            var products = await _uowRepository.Repository<Product>().GetAsync(x => x.Name == "Sample 1");

Get first or default record based on filters/expression

            //***
            //*** Get first or default record based on filters/expression
            //***
            var products = await _uowRepository.Repository<Product>().GetFirstOrDefaultAsync(x => x.Name == "Sample 1");

Find

Find the record based on primary identifier of the table

            //***
            //*** Find the record based on primary identifier of the table
            //***
            var product = await _uowRepository.Repository<Product>().FindAsync(1);

Any

Get true/false based on filters/expression

            //***
            //*** When Check if any records exists
            //***
            var result = await _uowRepository.Repository<Product>().AnyAsync(x=>x.ProductId != 0);

Insert

Insert a record in the database

            var newRecord = new Product()
            {
                Value = 70,
                Name = "Sample 1"
            };
            //***
            //*** Insert a record in the database
            //***
            await _uowRepository.Repository<Product>().InsertAsync(newRecord);

Insert multiple records in the database

            var records = new List<Product>()
            {
                new Product()
                {
                    Value = 70,
                    Name = "Sample 1"
                },
                new Product()
                {
                    Value = 70,
                    Name = "Sample 2"
                },
            };
            //***
            //*** Insert multiple records in the database
            //***
            await _uowRepository.Repository<Product>().InsertAsync(records);

Transaction

Insert using transaction and commit

Data will not be saved to the dB until Commit method is called

            var newRecord = new Product()
            {
                Value = 70,
                Name = "Sample 1"
            };

            await _uowRepository.BeginTransactionAsync();
            //***
            //*** Insert using transaction and commit
            //***
            await _uowRepository.Repository<Product>().InsertAsync(newRecord);
            await _uowRepository.CommitAsync();

Inserting using transaction and then rollback

Upon rollback, no changes will be saved to the dB

            var newRecord = new Product()
            {
                Value = 70,
                Name = "Sample 1"
            };

            await _uowRepository.BeginTransactionAsync();
            //***
            //*** Inserting using transaction and then rollback
            //***
            await _uowRepository.Repository<Product>().InsertAsync(newRecord);
            await _uowRepository.RollbackAsync();

Bulk insert

            var records = new List<Product>()
            {
                new Product()
                {
                    Value = 70,
                    Name = "Sample 1"
                },
                new Product()
                {
                    Value = 70,
                    Name = "Sample 2"
                },
            };
            //***
            //*** When insert method is called
            //***
            await _uowRepository.Repository<Product>().BulkInsertAsync(records);

Bulk insert can also be used with Transactions

Update

Update single record in the database

            //***
            //*** Update single record in the database
            //***
            await _uowRepository.Repository<Product>().UpdateAsync(_sampleProduct);

Update multiple records in the database

            //***
            //*** Update multiple records in the database
            //***
            await _uowRepository.Repository<Product>().UpdateAsync(_sampleProducts);

Bulk update multiple records in the database

            //***
            //*** Bulk update multiple records in the database
            //***
            await _uowRepository.Repository<Product>().BulkUpdateAsync(_sampleProducts);

All update methods can be wrapped around the transaction

Delete

Delete single record based on primary identifier

            //***
            //*** Delete single record based on primary identifier
            //***
            await UowRepository.Repository<Product>().DeleteAsync(1);

Delete single record based on entity

            //***
            //*** Delete single record based on entity
            //***
            await UowRepository.Repository<Product>().DeleteAsync(record);

Delete multiple records based on filters/expression

            //***
            //*** Delete multiple records based on filters/expression
            //***
            await UowRepository.Repository<Product>().DeleteAsync(x => x.ProductId == _sampleProduct.ProductId);

Delete all records

            //***
            //*** Delete all records
            //***
            await UowRepository.Repository<Product>().DeleteAllAsync();

Delete with transaction

All delete methods/operations can be wrapped around transaction