Dapper.Contrib A Getting Started Tutorial for CRUD Operations

Overview

Dapper.Contrib is a small library that you can add to your project which will extend the IDbConnection interface with additional helper methods for CRUD operations, such as inserting, deleting, updating, getting records, and mapping database results to strongly typed objects.

• The key feature of Dapper.Contrib is its ability to automatically map an object to a table in your database.
• It means that you don't have to write any SQL code to CRUD your data.
• All you need is your model/entity and Dapper.Contrib will handle the rest.
• It adds basic CRUD operations (Create, Retrieve, Update, Delete) for your models/entities.

Why should you use Dapper Contrib?

Using Dapper.Contrib is a personal choice. It allows the developer to save time using the existing method that will generate SQL instead of writing it.

One of the biggest advantages of Dapper.Contrib is the ability to track your entities and identify if changes have been made if you retrieve entities with the Get or GetAll methods.

So, if there is any change and you call the Update method, it will generate the SQL and update only those properties that have been changed.

NuGet Installation

Dapper.Contrib is available through NuGet: https://www.nuget.org/packages/Dapper.Contrib/

You can easily install this library by running the following command:

PM> Install-Package Dapper.Contrib

More information and documentation can be found at: https://github.com/DapperLib/Dapper.Contrib

APIs

Once you install this library then, the following extension methods will automatically add to DbConnection:

• Get
• GetAll
• Insert
• Update
• Delete
• DeleteAll

You can use these extension methods easily in your code.

var invoice = connection.Get<InvoiceContrib>(1);
var invoices = connection.GetAll<InvoiceContrib>().ToList();
var identity = connection.Insert(new InvoiceContrib {Kind = InvoiceKind.WebInvoice, Code = "Insert_Single_1"});
var isSuccess = connection.Update(new InvoiceContrib {InvoiceID = 1, Code = "Update_Single_1"});
var isSuccess = connection.Delete(new InvoiceContrib {InvoiceID = 1});
var isSuccess = connection.DeleteAll<InvoiceContrib>();

Dapper.Contrib also allows mapping for special attributes using Data Annotations:

• Key: Specify the property as a key that is automatically generated by the database.
• ExplicitKey: Specify the property as a key explicitly that is not automatically generated by the database.
• Table: Specify the destination table name mapped to the entity.
• Write: Specify the property if it is writable or not.
• Computed: Specify the property that should be excluded from an update.

[Table("Invoice")]
public class InvoiceContrib
{
	[Key]
	public int InvoiceID { get; set; }
	public string Code { get; set; }
	public InvoiceKind Kind { get; set; }

	[Write(false)]
	[Computed]
	public string FakeProperty { get; set; }
}

using (var connection = My.ConnectionFactory())
{
	connection.Open();

	var invoices = connection.GetAll<InvoiceContrib>().ToList();

	// The FakeProperty is skipped
	invoices.ForEach(x => x.FakeProperty += "z");

	var isSuccess = connection.Update(invoices);
}

Unfortunately, there is no proper documentation available for this library, but you can get a little bit of help from: https://github.com/StackExchange/Dapper/tree/master/Dapper.Contrib

Limitations

There is no support for composite key mapping.

Dapper.Contrib has an Update extension method, and SQLiteConnection also exposes an Update event that clashes with each other. But you can easily resolve the clash by using any of the following ways:

• Pass a type parameter to the Update method

var isSuccess = connection.Update<InvoiceContrib>(new InvoiceContrib {InvoiceID = 1, Code = "Update_Single_1"});

• The other way is to call the Update method explicitly using SqlMapperExtensions.

var isSuccess = SqlMapperExtensions.Update(connection, new InvoiceContrib {InvoiceID = 1, Code = "Update_Single_2"});

Support

This library is supported regularly, and you will get your answers within the next few days. https://github.com/DapperLib/Dapper.Contrib/issues

Related Articles

• Dapper Insert
• Dapper Plus Bulk Insert