The Dapper alternative that nobody asked for.
- Wraps ADO.NET with an async-first and fluent API.
- Maps DataReader results to a strongly typed list of objects.
- CRUD operations don't require boilerplate of creating a new connection, opening, and disposing it.
- Transaction state can be carried between different repositories.
var stockItems = await context
.Select("stock_item_id", "stock_item_name")
.From("warehouse.stock_items")
.Where("supplier_id").EqualTo(2)
.OrderBy("stock_item_name")
.ToListAsync<StockItem>();
dotnet add package Toadstool
--OR--
PM> Install-Package Toadstool
The entrypoint into the Toadstool API is the DbContext
class. Typically, only one of these should be created per database in your application lifetime (or HTTP Request lifetime.)
var context = new DbContext();
The context must be able to create new instances of IDbConnection
, so we pass it a CreateConnection
delegate, which is just a function that returns a new connection with the driver of our choosing.
// Use with SQL Server
var context = new DbContext(() => new SqlConnection("Server=..."));
// Use with PostgreSQL
var context = new DbContext(() => new NpgsqlConnection("Host=..."));
class Customer {
public string FirstName { get; set; }
public string LastName { get; set;}
public int Age { get; set;}
}
// Do a SELECT then map the results to a list.
IList<Customer> customers = await context
.Select("first_name", "last_name", "age")
.From("customer")
.Where("last_name").EqualTo("Cuervo")
.ToListAsync<Customer>();
// Do a SELECT then grab the first result.
Customer customer = await context
.Select("first_name", "last_name", "age")
.From("customer")
.Where("customer_id").EqualTo(777)
.FirstOrDefaultAsync<Customer>();
// Execute an INSERT
int rowsAffected = await context
.InsertInto("customer")
.Columns("first_name", "last_name", "age")
.Values("Peter", "Rabbit", 100)
.Values("Santa", "Clause", 1000)
.Execute();
// Execute an UPDATE
int rowsAffected = await context
.Update("customer")
.Set("first_name").EqualTo("Jerry")
.Set("last_name").EqualTo("Seinfeld")
.Where("last_name").EqualTo("Cuervo")
.Execute();
// Execute a DELETE
int rowsAffected = await context
.DeleteFrom("customer")
.Where("last_name").EqualTo("Cuervo")
.Execute();
The statement builder shown above is optional. Statements can also be passed in as plain strings.
class Customer {
public string FirstName { get; set; }
public string LastName { get; set;}
}
// Do a SELECT then map the results to a list.
IList<Customer> customers = await context
.CreateCommand(@"SELECT first_name, last_name FROM customer WHERE last_name = @lastName")
.WithParameter("lastName", "Cuervo")
.ToListAsync<Customer>();
// Execute an INSERT
int rowsAffected = await context
.CreateCommand(@"INSERT INTO customer(fist_name, last_name) VALUES (@firstName, @lastName)")
.WithParameters(new {
firstName = "Jose",
lastName = "Cuervo"
})
.Execute();
// Execute an UPDATE
int rowsAffected = await context
.CreateCommand(@"UPDATE customer SET first_name = @firstName where last_name = @lastName")
.WithParameter("firstName", "Jerry")
.WithParameter("lastName", "Cuervo")
.Execute();
// Execute a DELETE
int rowsAffected = await context
.CreateCommand(@"DELETE FROM customer where last_name = @lastName")
.WithParameter("lastName", "Cuervo")
.Execute();
// Execute a scalar
string firstName = await context
.CreateCommand(@"SELECT first_name FROM customer WHERE id = 42")
.ExecuteAsync<string>();
// Execute a stored procedure
List<Customers> customers = await context
.CreateCommand(@"spGetCustomers")
.WithParameter("lastName", "Cuervo")
.WithCommandType(CommandType.StoredProcedure)
.ToListAsync<Customer>();
To start a new database transaction, call BeginTransaction
on DbContext
. Once a transaction is begun on an instance of DbContext
, all statements executed against that context automatically join the transaction on the same connection. Once the transaction is disposed, the context returns to normal connection pooling behavior.
using (var transaction = context.BeginTransaction())
{
// Automatically joins the transaction
var results1 = await context.Select("1").ExecuteAsync<int>();
// Automatically joins the transaction
var results2 = await context.Select("2").ExecuteAsync<int>();
transaction.Commit();
}
// Transaction is disposed, context returns to normal connection pool.
var results3 = await context.Select("3").ExecuteAsync<int>(); // Normal "anonymous" call (not in transaction)
This is useful because different repositories sharing the same DbContext
instance can also share transactions. Say you have a service class with several repositories. Because you're using dependency injection, each of those repositories shares the same DbContext
instance....
public async Task PlaceCustomerOrder(CustomerDetails customerDetails, OrderDetails orderDetails)
{
using (var transaction = await _context.BeginTransactionAsync())
{
// Automatically joins the transaction
var userId = await _userRepository.CreateCustomer(customerDetails);
// Automatically joins the transaction
var orderId = await _orderRepository.CreateOrder(orderDetails);
// Automatically joins the transaction
var fulfillmentTicket = await _fulfillmentRepository.CreateFulfillmentTicket(userId, orderId);
transaction.Commit();
}
}
Traditionally (with ADO or Dapper) you would have to pass around instances of your IDbConnection
and IDbTransaction
as method parameters, which is messy, rewrite a boilerplate connection provider class every time, or resort to using TransactionScope, which some developers believe is Dark Magic.
If you're using a IoC container, like the one in AspNetCore, it's best to have DbContext
be registered as "Scoped".
services.AddScoped<IDbContext>(
(sp) => new DbContext(() => new SqlConnection("Server=..."))
);
This way, everything in the same request scope can share transactions.
Prerequisites:
- .NET Core SDK 2.1
- Mono or .NET Framework
- Gnu Make
To build the NuGet package.
make pack
This project targets both .NET Standard 2.0 and .NET Framework 4.5.1. Because of this, you must have .NET Framework or Mono installed (in addition to .NET Core).
On macOS and Linux build environments, to build from .NET Core you must set the FrameworkPathOverride
environment variable.
export FrameworkPathOverride=$(dirname $(which mono))/../lib/mono/4.5/
See dotnet/sdk#335
Prerequisites:
- .NET Core SDK 2.1
- Gnu Make
- Docker
- Bash
Running the integration tests requires having a recent version of Docker installed. Two database servers (PostgreSQL and SQL Server) will be brought up with
make databases
After the servers are up and the databases are restored, the tests can be run.
make test
To bring down the servers and clean up the backup files,
make clean-databases
Built with ♥ by Calvin.
© Calvin Furano