Plinth.Database.PgSql 1.5.0

The ID prefix of this package has been reserved for one of the owners of this package by NuGet.org. Prefix Reserved
.NET 6.0
dotnet add package Plinth.Database.PgSql --version 1.5.0
NuGet\Install-Package Plinth.Database.PgSql -Version 1.5.0
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.PgSql" Version="1.5.0" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Plinth.Database.PgSql --version 1.5.0
#r "nuget: Plinth.Database.PgSql, 1.5.0"
#r directive can be used in F# Interactive, C# scripting and .NET Interactive. Copy this into the interactive tool or source code of the script to reference the package.
// Install Plinth.Database.PgSql as a Cake Addin
#addin nuget:?package=Plinth.Database.PgSql&version=1.5.0

// Install Plinth.Database.PgSql as a Cake Tool
#tool nuget:?package=Plinth.Database.PgSql&version=1.5.0

README

Plinth.Database.PgSql

Stored Procedure based mini-framework for PostgreSQL

Provides Transaction management, stored procedure execution, rowset handling, and transient error detection

1. Register the transaction factory and provider with DI in Setup

  // IConfiguration configuration;

   var txnFactory = new SqlTransactionFactory(
       configuration,
       "MyDB",
       config.GetConnectionString("MyDB"));

   services.AddSingleton(txnFac);              // for injecting the SqlTransactionFactory
   services.AddSingleton(txnFac.GetDefault()); // for injecting the ISqlTransactionProvider

2. Settings in appsettings.json

Example appsettings.json 👉 All settings in PlinthPgSqlSettings are optional. The defaults are shown below.

{
  "ConnectionStrings": {
    "MyDB": "Host=...."
  },
  "PlinthPgSqlSettings": {
    "SqlCommandTimeout": "00:00:50",
    "SqlRetryCount": 3,
    "SqlRetryInterval": "00:00:00.200",
    "SqlRetryFastFirst": true,
    "DisableTransientRetry": false
  }
}
  • SqlCommandTimeout: A TimeSpan formatted time for the default time for each SQL operation. Default is 50 seconds.
  • SqlRetryCount: If a transient error is detected, maximum number of retries after the initial failure. Default is 3. This allows up to 4 attempts.
  • SqlRetryInterval: If a transient error is detected, this is how long between retry attempts. Default is 200 milliseconds.
  • SqlRetryFastFirst: If true, upon the first transient error, the first retry will happen immediately. Subsequent transient errors will wait the SqlRetryInterval. Default is true.
  • DisableTransientRetry: If true, transient errors will not trigger retries. Default is false.

3. Transient Errors

It is very common on cloud hosted databases to have the database return transient errors that will work perfectly if retried. These errors can be things like deadlocks, timeouts, throttling, and transport errors.

The framework accepts a function to execute the whole transaction. When a transient error occurs, the entire transaction is rolled back and the function is executed again.

⚠️ Your code inside a transaction should be re-entrant. Anything that is performed that cannot be rolled back (such as sending an email), should be performed outside the transaction or be checked to confirm that it won't execute more than once.

4. Creating a Transaction

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.ExecuteQueryProcOneAsync(
                "fn_get_mything_by_id",
                row => Task.FromResult(new MyThing
                {
                    Field1 = row.GetInt("i_field1"),
                    Filed2 = row.GetDateTimeNull("dt_field2")
                    ... etc
                }),
                new NpgsqlParameter("@i_thing_id", thingId)).Value;
        }, ct);

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

        return Ok(myThing);        
    }
}

5. 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 also have an overload that accepts a CancellationToken

  1. ExecuteProcAsync(string procName, params NpgsqlParameter[] parameters)
    • This will execute the procedure, 👉 and fail if no rows were modified
  2. ExecuteProcAsync(string procName, int expectedRows, params NpgsqlParameter[] parameters)
    • This will execute the procedure, and fail if the rows modified does not match expectedRows
  3. ExecuteProcUncheckedAsync(string procName, params NpgsqlParameter[] parameters)
    • This will execute the procedure, and return the number of rows modified

6. 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 also have an overload that accepts a CancellationToken

  1. ExecuteQueryProcAsync(string procName, params NpgsqlParameter[] parameters)
    • Returns an IAsyncEnumerable<IResult> which can be enumerated to extract objects from rows.
  2. ExecuteQueryProcListAsync<T>(string procName, Func<IResult, Task<T>> readerFunc, params NpgsqlParameter[] parameters)
    • Returns a List<T> of objects returned from the Func called on each row returned.
    • 👉 Always returns a non-null List<T> that may be empty.
  3. ExecuteQueryProcOneAsync(string procName, Func<IResult, Task> readerFunc, params NpgsqlParameter[] parameters)
    • Calls the Func with a single row result (if one found), returns true/false if row was found.
  4. ExecuteQueryProcOneAsync<T>(string procName, Func<IResult, Task<T>> readerFunc, params NpgsqlParameter[] parameters)
    • Calls the Func with a single row result and returns the output inside a SqlSingleResult<T> object.
    • Use .Value to get the result and .RowReturned to determine if a row was returned.

7. Special Connection Features

  • SetRollback(): Will mark this transaction for later rollback when the transaction function is complete
  • _WillBeRollingBack(): Determine if SetRollback() has been called on this transaction
  • IsAsync(): Determine if this transaction supports async operations
  • CommandTimeout {get; set;}: The default timeout for sql commands (in seconds)

8. Rollback and Post Commit Actions

These allow you to have code execute after a rollback or a commit occurs. Useful for cleaning up non-transaction items or taking actions after database operations are committed.

Post Rollback Actions:

  • AddRollbackAction(string? desc, Action onRollback) AddAsyncRollbackAction(string? desc, Func<Task> onRollbackAsync)
    • These will execute the action/func after a rollback has completed
    • Common use case: Undoing a non-transactional thing that should only exist if the transaction succeeded

Post Commit Actions:

  • AddPostCommitAction(string? desc, Action postCommit) AddAsyncPostCommitAction(string? desc, Func<Task> postCommitAsync)
    • These will execute the action/func after the transaction has been committed
    • Common use case: Performing some action that should only occur if the database operations are confirmed.

9. 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<IAsyncMultiResultSet, Task> readerFunc, params NpgsqlParameter[] parameters)

Example

  await c.ExecuteQueryProcMultiResultSetAsync(
      "fn_get_multiple_results", 
      async (mrs) =>
      {
          var rs = await mrs.NextResultSetAsync();
          await processSet1(rs);

          rs = await mrs.NextResultSetAsync();
          await processSet2(rs);

          rs = await mrs.NextResultSetAsync();
          await processSet3(rs);
      },
      new NpgsqlParameter("@i_int1", 10));

10. IDeferredSqlConnection

This allows for recording a sequence of stored procedure calls (without actually executing them) and then executing them all at one at a later time.

Example:

    var deferred = _txnProvider.GetDeferred();

    // no sql actions occur
    deferred.ExecuteProc("fn_insert_thing". new NpgsqlParameter("@i_id", 5));
    deferred.ExecuteProc("fn_insert_thing". new NpgsqlParameter("@i_id", 10));    

    await _txnProvider.ExecuteTxnAsync(connection =>
    {
        // now the sql actions are executed
        await connection.ExecuteDeferredAsync(deferred);
    });
    

11. 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.ExecuteRawQueryOneAsync(
                "SELECT i_field1, dt_field2 FROM my_things WHERE i_thing_id = @i_thing_id",
                row => Task.FromResult(new MyThing
                {
                    Field1 = row.GetInt("i_field1"),
                    Filed2 = row.GetDateTimeNull("dt_field2")
                    ... etc
                }),
                new NpgsqlParameter("@i_thing_id", thingId)).Value;
        }, ct);

The methods are analogues of the methods in sections 5, 6 and 9.

  • 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 Versions
.NET net6.0 net6.0-android net6.0-ios net6.0-maccatalyst net6.0-macos net6.0-tvos net6.0-windows net7.0 net7.0-android net7.0-ios net7.0-maccatalyst net7.0-macos net7.0-tvos net7.0-windows
Compatible target framework(s)
Additional computed target framework(s)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (3)

Showing the top 3 NuGet packages that depend on Plinth.Database.PgSql:

Package Downloads
Plinth.Hangfire.PgSql The ID prefix of this package has been reserved for one of the owners of this package by NuGet.org.

Plinth Hangfire Utilities

Plinth.Storage.PgSql The ID prefix of this package has been reserved for one of the owners of this package by NuGet.org.

PostgreSQL driver for Plinth.Storage

Plinth.Database.Dapper.PgSql The ID prefix of this package has been reserved for one of the owners of this package by NuGet.org.

Dapper extensions for plinth database framework for PosgreSQL

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
1.5.0 102 11/9/2022
1.5.0-b88.7a7c20cd 32 11/9/2022
1.4.7 308 10/20/2022
1.4.6 280 10/17/2022
1.4.5 336 10/1/2022
1.4.4 391 8/16/2022
1.4.3 410 8/2/2022
1.4.2 391 7/19/2022
1.4.2-b80.7fdbfd04 79 7/19/2022
1.4.2-b74.acaf86f5 63 6/15/2022
1.4.1 408 6/13/2022
1.4.0 564 6/6/2022
1.3.8 548 4/12/2022
1.3.7 388 3/21/2022
1.3.6 399 3/17/2022
1.3.6-b67.ca5053f3 66 3/16/2022
1.3.6-b66.4a9683e6 63 3/16/2022
1.3.5 411 2/23/2022
1.3.4 431 1/20/2022
1.3.3 291 12/29/2021
1.3.2 472 12/11/2021
1.3.1 371 11/12/2021
1.3.0 375 11/8/2021
1.2.3 430 9/22/2021
1.2.2 401 8/20/2021
1.2.1 441 8/5/2021
1.2.0 469 8/1/2021
1.2.0-b37.a54030b9 107 6/24/2021
1.1.6 435 3/22/2021
1.1.5 366 3/9/2021
1.1.4 387 2/27/2021
1.1.3 352 2/17/2021
1.1.2 357 2/12/2021
1.1.1 363 2/1/2021
1.1.0 451 12/16/2020
1.1.0-b27.b66c309b 233 11/15/2020
1.0.12 468 10/18/2020
1.0.11 484 10/6/2020
1.0.10 449 9/30/2020
1.0.9 447 9/29/2020
1.0.8 648 9/26/2020
1.0.7 590 9/19/2020
1.0.6 484 9/3/2020
1.0.5 497 9/2/2020
1.0.4 461 9/1/2020
1.0.3 436 9/1/2020
1.0.2 548 8/29/2020
1.0.1 503 8/29/2020
1.0.0 508 8/29/2020
1.0.0-b1.c22f563d 199 8/28/2020

net7.0 support and removed netcoreapp3.1