Plinth.Database.Dapper.MSSql 1.4.3

Prefix Reserved
There is a newer version of this package available.
See the version list below for details.
dotnet add package Plinth.Database.Dapper.MSSql --version 1.4.3                
NuGet\Install-Package Plinth.Database.Dapper.MSSql -Version 1.4.3                
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="Plinth.Database.Dapper.MSSql" Version="1.4.3" />                
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Plinth.Database.Dapper.MSSql --version 1.4.3                
#r "nuget: Plinth.Database.Dapper.MSSql, 1.4.3"                
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install Plinth.Database.Dapper.MSSql as a Cake Addin
#addin nuget:?package=Plinth.Database.Dapper.MSSql&version=1.4.3

// Install Plinth.Database.Dapper.MSSql as a Cake Tool
#tool nuget:?package=Plinth.Database.Dapper.MSSql&version=1.4.3                

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
Product Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 is compatible.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
.NET Core netcoreapp3.1 is compatible. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
1.6.5 101 8/31/2024
1.6.4 44 8/2/2024
1.6.3 650 5/15/2024
1.6.2 131 2/16/2024
1.6.1 175 1/5/2024
1.6.0 184 11/30/2023
1.5.10-b186.aca976b4 82 11/30/2023
1.5.9 1,078 11/29/2023
1.5.9-b174.64153841 78 11/23/2023
1.5.9-b172.dfc6e7bd 67 11/17/2023
1.5.9-b171.4e2b92e2 78 11/4/2023
1.5.8 158 10/23/2023
1.5.7 169 7/31/2023
1.5.6 154 7/13/2023
1.5.5 157 6/29/2023
1.5.4 240 3/7/2023
1.5.3 249 3/3/2023
1.5.2 324 1/11/2023
1.5.2-b92.7c961f5f 117 1/11/2023
1.5.0 327 11/9/2022
1.5.0-b88.7a7c20cd 102 11/9/2022
1.4.7 397 10/20/2022
1.4.6 404 10/17/2022
1.4.5 388 10/1/2022
1.4.4 402 8/16/2022
1.4.3 410 8/2/2022
1.4.2 407 7/19/2022
1.4.2-b80.7fdbfd04 124 7/19/2022
1.4.2-b74.acaf86f5 112 6/15/2022
1.4.1 421 6/13/2022
1.4.0 425 6/6/2022
1.3.8 460 4/12/2022
1.3.7 440 3/21/2022
1.3.6 444 3/17/2022
1.3.6-b67.ca5053f3 120 3/16/2022
1.3.6-b66.4a9683e6 117 3/16/2022
1.3.5 455 2/23/2022
1.3.4 452 1/20/2022
1.3.3 303 12/29/2021
1.3.2 469 12/11/2021
1.3.1 305 11/12/2021
1.3.0 340 11/8/2021
1.2.3 349 9/22/2021
1.2.2 337 8/20/2021
1.2.1 373 8/5/2021
1.2.0 398 8/1/2021
1.2.0-b37.a54030b9 160 6/24/2021
1.1.6 420 3/22/2021
1.1.5 349 3/9/2021

added detailed readme