Plinth.Database.Dapper.MSSql
1.4.3
Prefix Reserved
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
<PackageReference Include="Plinth.Database.Dapper.MSSql" Version="1.4.3" />
paket add Plinth.Database.Dapper.MSSql --version 1.4.3
#r "nuget: Plinth.Database.Dapper.MSSql, 1.4.3"
// 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
ExecuteProcAsync(string procName, object? param, CancellationToken cancellationToken)
- This will execute the procedure, 👉 and fail if no rows were modified
ExecuteProcAsync(string procName, int expectedRows, object? param, CancellationToken cancellationToken)
- This will execute the procedure, and fail if the rows modified does not match
expectedRows
- This will execute the procedure, and fail if the rows modified does not match
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
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.
- Returns an
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
.GetListAsync<T>(CancellationToken cancellationToken)
- Returns an
IEnumerable<T>
of<T>
mapped by Dapper. - 👉 Always returns a non-null
IEnumerable<T>
that may be empty.
- Returns an
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 DMLExecuteRawQueryListAsync
for queries that return a list of resultsExecuteRawQueryOneAsync
for queries that return a single resultExecuteRawQueryMultiResultSetAsync
for queries that return multiple result sets
Product | Versions 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. |
-
.NETCoreApp 3.1
- Dapper (>= 2.0.123)
- Plinth.Database.MSSql (>= 1.4.3)
-
net6.0
- Dapper (>= 2.0.123)
- Plinth.Database.MSSql (>= 1.4.3)
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