Plinth.Database.Dapper.MSSql 1.7.0

README

Plinth.Database.Dapper.MSSql

Extension for Plinth.Database.MSSql to support Dapper for object mapping

Extends Plinth.Database.MSSql to allow using Dapper to map objects to input parameters and result sets to output objects instead of manual mapping.

1. Follow the setup instructions from Plinth.Database.MSSql

No additional configuration changes are required to support Dapper. The only necessary action is to install this package.

2. Access Dapper specific methods via extension method

👉 To access Dapper methods, call the .Dapper() extension method on ISqlConnection

Below is an example controller that creates a transaction, executes a stored procedure, and returns the result.

[Route("api/[controller]")]
[ApiController]
public class MyThingController : Controller
{
    private readonly ISqlTransactionProvider _txnProvider;

    public MyThingController(ISqlTransactionProvider _txnProvider)
    {
        _txnProvider = txnProvider;
    }

    [HttpGet]
    [Route("{thingId}")]
    [ProducesResponseType(200)]
    public async Task<ActionResult<MyThing>> GetMyThing(Guid thingId, CancellationToken ct)
    {
        var myThing = await _txnProvider.ExecuteTxnAsync(connection =>
        {
            return await connection.Dapper().ExecuteQueryProcOneAsync<MyThing>(
                "usp_GetMyThingById",
                new { ThingID = thingId }).Value;
        }, ct);

        if (myThing is null)
            throw new LogicalNotFoundException($"MyThing {thingId} was not found");

        return Ok(myThing);        
    }
}

3. Executing Stored Procedures with no Result Set

To execute a stored procedure that does not return a result set, use one of these three options. Typically used with DML procedures that insert/update/delete. 👉 All forms accepts CancellationToken

1. ExecuteProcAsync(string procName, object? param, CancellationToken cancellationToken)
   • This will execute the procedure, 👉 and fail if no rows were modified
2. ExecuteProcAsync(string procName, int expectedRows, object? param, CancellationToken cancellationToken)
   • This will execute the procedure, and fail if the rows modified does not match expectedRows
3. ExecuteProcUncheckedAsync(string procName, object? param, CancellationToken cancellationToken)
   • This will execute the procedure, and return the number of rows modified

4. Executing Stored Procedures that return a Result Set

To execute a stored procedure returns a result set, use one of these three options. Typically used with SELECT queries. 👉 All forms accept a CancellationToken

1. ExecuteQueryProcListAsync<T>(string procName, object? param, CancellationToken cancellationToken)
   • Returns an IEnumerable<T> of <T> mapped by Dapper.
   • 👉 Always returns a non-null IEnumerable<T> that may be empty.
2. ExecuteQueryProcOneAsync<T>(string procName, object? param, CancellationToken cancellationToken)
   • Returns the first result or null if no row is found.

5. Multiple Result Sets

Some stored procedures can actually return multiple result sets in a single call.

To execute and process each result set, use this method: ExecuteQueryProcMultiResultSetAsync(string procName, Func<IDapperMultiResultSetAsync, Task> readerAction, object? param, CancellationToken cancellationToken)

Example

  await c.Dapper().ExecuteQueryProcMultiResultSetAsync(
      "usp_GetMultipleResults", 
      async (mrs) =>
      {
          await processSet1(mrs);
          await processSet2(mrs);
          await processSet3(mrs);
      },
      new { Int1 = 10 });

  public void processSet1(IDapperMultiResultSetAsync mrs)
  {
      var items = (await mrs.GetListAsync<MyThing1>()).ToList();
      // do something with items
  }

IDapperMutliResultSetAsync has these methods for processing each result set

1. .GetListAsync<T>(CancellationToken cancellationToken)
   • Returns an IEnumerable<T> of <T> mapped by Dapper.
   • 👉 Always returns a non-null IEnumerable<T> that may be empty.
2. GetOneAsync<T>(CancellationToken cancellationToken)
   • Returns the first result or null if no row is found.

6. Raw SQL Transactions

Normal transactions as shown above only allow for executing stored procedures. There are times and cases where executing a raw SQL statement is required. To do so, use ExecuteRawTxnAsync as shown in the below example:

        var myThing = await _txnProvider.ExecuteRawTxnAsync(connection =>
        {
            return await connection.Dapper().ExecuteRawQueryOneAsync<MyThing>(
                "SELECT Field1, Field2 FROM MyThings WHERE ThingID = @ThingID",
                new { ThingID = thingId }).Value;
        }, ct);

The methods are analogues of the methods in sections 3, 4 and 5.

• ExecuteRawAsync for DML
• ExecuteRawQueryListAsync for queries that return a list of results
• ExecuteRawQueryOneAsync for queries that return a single result
• ExecuteRawQueryMultiResultSetAsync for queries that return multiple result sets