DbController.SqlServer 5.0.0

DbController

An easy to use database wrapper which uses Dapper.

What does this package do?

This package has been created to minify the effort to write database queries in C#.

For example take a look on this block of code:

DataTable result = new DataTable();
MySqlConnection connection = new MySqlConnection("YOUR_CONNECTION_STRING");
connection.Open();
MySqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM customers";
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
adapter.Fill(result);
connection.Close();
command.Dispose();
connection.Dispose();

With this package this code can be reduced to:

using MySqlController dbController = await MySqlController.CreateAsync("YOUR_CONNECTION_STRING");
List<Customer> customer = await dbController.SelectDataAsync<Customer>("SELECT * FROM customers");

It can even be more reduced when passing the IDbController around.

public Task<List<Customer>> GetCustomers(IDbController dbController)
{
	return dbController.SelectDataAsync<Customer>("SELECT * FROM customers");
}

Once initialized, the connection to the database will be opened automatically and it will kept open until the DbController gets disposed. In addition it keeps track of the active transaction with no addition work.

Installation

We offer different packages for this library.

For MySql use:

Install-Package DbController.MySql

For SqlServer use:

Install-Package DbController.SqlServer

If you want to implement your own DbController then you should use:

Install-Package DbController

Usage

Start by creating an instance of the DbController based on the package you want to use:

When using the DbController.MySql package a new controller can be created like this:

using MySqlController dbController = await MySqlController.CreateAsync("YOUR_CONNECTION_STRING");

For the DbController.SqlServer package:

using SqlController dbController = await SqlController.CreateAsync("YOUR_CONNECTION_STRING");

You can also use the interface to change the used DbController at runtime with dependency injection.

using IDbController dbController = await MySqlController.CreateAsync("YOUR_CONNECTION_STRING");

DbController is using Dapper under the hood for object mapping.

Extension to Dappers mapping

This package provides you with an attribute to map columns in the database to the corresponding property from your object. For example your customers table has a column with the name customer_id�. Your C# class uses a slightly different name to match the C# naming conventions.

Now you can add an attribute to your property like this:

public class Customer
{
	[CompareField("customer_id")]
	public int CustomerId { get; set; }
}

Both packages will create a TypeAttributeCache mapping all CustomFieldAttributes to the corresponding properties.

This will be done in the static constructor for the IDbController implementation. If you want to add this functionality to your own implementation of IDbController you'll should add a static constructor like this:

/// <summary>
/// Static constructor to initialize the TypeAttributeCache
/// </summary>
static MySqlController()
{
    // INIT Dapper for CompareField
    foreach (Type type in SingletonTypeAttributeCache.CacheAll<CompareFieldAttribute>((att) => att.FieldNames))
    {
        SqlMapper.SetTypeMap(type, new CustomPropertyTypeMap(
            type,
            (type, columnName) =>
            {
                PropertyInfo? prop = SingletonTypeAttributeCache.Get(type, columnName);

                return prop is null ? type.GetProperty(columnName, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance) : prop;

            }
        ));
    }
}

Methods

Every IDbController provides the following methods to work with:

Best practices

It's generally recommended to pass an instance of IDbController to each function which needs to execute some SQL. For example:

public static async Task Main(string[] args)
{
    using IDbController dbController = await MySqlController.CreateAsync("YOUR_CONNECTIONSTRING");

    List<Customer> customers = await GetCustomers(dbController);
    List<Employee> employees = await GetEmployees(dbController);
}

public Task<List<Customer>> GetCustomers(IDbController dbController)
{
	return dbController.SelectDataAsync<Customer>("SELECT * FROM customers");
}

public Task<List<Employee>> GetEmployees(IDbController dbController)
{
	return dbController.SelectDataAsync<Employee>("SELECT * FROM employees");
}

The main benefit is that every function will be able to read from the current transaction state.