Skip to content
A Crud library based on Dapper
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Type Name Latest commit message Commit time
Failed to load latest commit information.

Build status

Nuget package Dapper.CX.SqlServer makes it easy to do CRUD operations on pure POCO model classes. 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.

Wiki links: Why Dapper.CX?, Reference

Here's a simple example using GetAsync and SaveAsync methods assuming a fictional Appointment model class and fictional GetConnection method:

using (var cn = GetConnection())
    var appt = await cn.GetAsync<Appointment>(id);
    // make your changes
    await cn.SaveAsync(appt);

The SaveAsync method performs an insert or update depending on whether the model object IsNew or not. Here's a more sophisticated example showing the GetWhereAsync method and ChangeTracker object.

using (var cn = GetConnection())
    var appt = await cn.GetWhereAsync<Appointment>(new { clientId = 4343, date = new DateTime(2020, 3, 1) });
    var ct = new ChangeTracker<Appointment>(appt);
    // make your changes
    // with a change tracker object, only modified properties are included in update statement 
    await cn.SaveAsync(appt, ct);  

If you need to update time and user audit tracking at the model level, you can use the onSave optional callback like this:

using (var cn = GetConnection())
    var model = await cn.GetAsync<Employee>(id);
    // do some stuff with model
    await cn.SaveAsync(model, onSave: (row, action) =>
        switch (action)
            case SaveAction.Insert;                
                row.CreatedBy = User.Identity.Name;
                row.DateCreated = DateTime.UtcNow;
            case SaveAction.Update:
                row.ModifiedBy = User.Identity.Name;
                row.DateModified = DateTime.UtcNow;

In a real app, you'd likely extract the anonymous method to an actual method, and make it work as a convention across your application.

Customizing behaviors with interfaces

There are some interfaces you can use on model classes to implement validation and custom Get behavior. To use these, your model class project must add package AO.DbSchema.Attributes as a dependency:

  • ICustomGet lets you inject portions of the SQL statements that Dapper.CX generates. This is so you can add model properties that are populated from a custom query, but not part of your base table proper. See the test for an example, along with the related model class. Here's where ICustomGet is invoked here and here. In that example, the properties Balance and Whatever aren't columns in the base table, but are queried during Get operations as if they are. (Note also that such properties use the NotMapped attribute to prevent Dapper.CX from attempting to bind them.)

  • IGetRelated lets you implement navigation properties by injecting a delegate in which you can perform additional gets every time a model is queried. See the test for an example. This uses the same sample model class above. Here's where IGetRelated is invoked internally.

  • IValidate lets you perform validation on a model class prior to an insert or update. See test and related model class for example. Note that IValidate has two methods Validate and ValidateAsync. The async version passes a connection argument so you can perform that requires looking up something in the database. The sync version is for validating properties of the model that don't require any database lookup. Here's where IValidate is invoked internally.

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.

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

You can’t perform that action at this time.