Plinth.Database.Dapper.MSSql 1.5.2-b92.7c961f5f

Prefix Reserved
This is a prerelease version of Plinth.Database.Dapper.MSSql.
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.5.2-b92.7c961f5f                
NuGet\Install-Package Plinth.Database.Dapper.MSSql -Version 1.5.2-b92.7c961f5f                
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.5.2-b92.7c961f5f" />                
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.5.2-b92.7c961f5f                
#r "nuget: Plinth.Database.Dapper.MSSql, 1.5.2-b92.7c961f5f"                
#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.5.2-b92.7c961f5f&prerelease

// Install Plinth.Database.Dapper.MSSql as a Cake Tool
#tool nuget:?package=Plinth.Database.Dapper.MSSql&version=1.5.2-b92.7c961f5f&prerelease                

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 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 is compatible.  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. 
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.7.1 110 12/12/2024
1.7.0 94 11/12/2024
1.6.6 91 11/8/2024
1.6.5 109 8/31/2024
1.6.4 57 8/2/2024
1.6.3 1,056 5/15/2024
1.6.2 139 2/16/2024
1.6.1 181 1/5/2024
1.6.0 190 11/30/2023
1.5.10-b186.aca976b4 86 11/30/2023
1.5.9 1,702 11/29/2023
1.5.9-b174.64153841 83 11/23/2023
1.5.9-b172.dfc6e7bd 71 11/17/2023
1.5.9-b171.4e2b92e2 82 11/4/2023
1.5.8 166 10/23/2023
1.5.7 175 7/31/2023
1.5.6 160 7/13/2023
1.5.5 163 6/29/2023
1.5.4 248 3/7/2023
1.5.3 255 3/3/2023
1.5.2 330 1/11/2023
1.5.2-b92.7c961f5f 121 1/11/2023
1.5.0 335 11/9/2022
1.5.0-b88.7a7c20cd 108 11/9/2022
1.4.7 403 10/20/2022
1.4.6 410 10/17/2022
1.4.5 394 10/1/2022
1.4.4 408 8/16/2022
1.4.3 416 8/2/2022
1.4.2 413 7/19/2022
1.4.2-b80.7fdbfd04 130 7/19/2022
1.4.2-b74.acaf86f5 116 6/15/2022
1.4.1 427 6/13/2022
1.4.0 435 6/6/2022
1.3.8 466 4/12/2022
1.3.7 446 3/21/2022
1.3.6 452 3/17/2022
1.3.6-b67.ca5053f3 126 3/16/2022
1.3.6-b66.4a9683e6 121 3/16/2022
1.3.5 461 2/23/2022
1.3.4 460 1/20/2022
1.3.3 309 12/29/2021
1.3.2 475 12/11/2021
1.3.1 311 11/12/2021
1.3.0 346 11/8/2021
1.2.3 355 9/22/2021
1.2.2 345 8/20/2021
1.2.1 379 8/5/2021
1.2.0 404 8/1/2021
1.2.0-b37.a54030b9 164 6/24/2021
1.1.6 426 3/22/2021
1.1.5 355 3/9/2021

net7.0 support and removed netcoreapp3.1