Dapper.CX

A Crud library based on Dapper

Description

Nuget package Dapper.CX.SqlServer makes it easy to do CRUD operations on pure POCO model classes via IDbConnection extension methods. The only model class requirement is that they have a property called Id or the class has an Identity attribute that indicates what its identity property is. int and long identity types are supported.

Programming language: C#

License: MIT License

Tags: ORM     SqlServer     Dapper     POCO     ORM-Micro    

README

Dapper.CX is a CRUD library for SQL Server made with Dapper. It works with POCO classes, where the only model class requirement is that they have a property called Id or an Identity attribute on the class that indicates what its identity property is. int and long identity types are supported. You can use Dapper.CX in two ways:

• as an injected service, learn more. This is intended for .NET Core apps to use dependency injection along with user profile integration.
• as IDbConnection extension methods, learn more. This is simpler to use than the service, but is not as elegant from a dependency standpoint.

Wiki links: Why Dapper.CX?, Reference. Note that Dapper.CX doesn't create tables. Please see my ModelSync project for info on that.

In a Nutshell

When using the injected service, you'd write CRUD code that looks like this. This example assumes a fictional Employee model class. There are several advantages of using the injected service. One, it integrates nicely with the authenticated user to check permissions or perform audit and change tracking. Two, you can omit the using block that you otherwise need when interacting with a connection. Three, there are some handy overloads that bundle exception handling and more. Here's how to implement the injected service along with a CRUD method reference.

public Employee ViewRecord { get; set; }

public async Task OnGetAsync(int id)
{
    ViewRecord = await Data.GetAsync<Employee>(id);
}

public async Task<IActionResult> OnPostSaveAsync(Employee employee)
{
    await Data.SaveAsync(employee);
    return Redirect("/Employees");
}

public async Task<IActionResult> OnPostDeleteAsync(int id)
{
    await Data.DeleteAsync<Employee>(id);
    return Redirect("/Employees");
}

When using the extension methods, it's almost the same thing, but you must open a database connection first. This example assumes a fictional GetConnection method that opens a SQL Server connection.

public Employee ViewRecord { get; set; }

public async Task OnGetAsync(int id)
{
    using (var cn = GetConnection())
    {
        ViewRecord = await cn.GetAsync<Employee>(id);
    }    
}

public async Task<IActionResult> OnPostSaveAsync(Employee employee)
{
    using (var cn = GetConnection())
    {
        await cn.SaveAsync(employee);
        return Redirect("/Employees");
    }
}

public async Task<IActionResult> OnPostDeleteAsync(int id)
{
    using (var cn = GetConnection())
    {
        await cn.DeleteAsync<Employee>(id);
        return Redirect("/Employees");
    }
}

Customizing behaviors with interfaces

There's a lot of functionality you can opt into by implementing interfaces on your model classes from the AO.Models project. See Extending Dapper.CX with Interfaces. Available interfaces are here.

And one other thing...

In addition to the more common strong-typed CRUD operations, Dapper.CX also offers a SqlCmdDictionary feature that gives you a clean way to build INSERT and UPDATE statements dynamically.

One other thing...

If you need a Dictionary-like object to persist in a database, you can implement the abstract class DbDictionary. Use this to store any object with a key, using these supported key types. This can save you the effort of adding a single-use dedicated model class for a more generic storage need. Note that, unlike a Dictinoary<TKey, TValue>, you can use different TValues with different keys, so this is very flexible and still type-safe. The abstract methods Serialize and Deserialize let you provide your Json serialization. See the integration test to see in action, along with the sample implementation.

---

Please see also Dapper.QX, Dapper.CX's companion library.