WebVella.Npgsql.Extensions 1.0.2

dotnet add package WebVella.Npgsql.Extensions --version 1.0.2
                    
NuGet\Install-Package WebVella.Npgsql.Extensions -Version 1.0.2
                    
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="WebVella.Npgsql.Extensions" Version="1.0.2" />
                    
For projects that support PackageReference, copy this XML node into the project file to reference the package.
<PackageVersion Include="WebVella.Npgsql.Extensions" Version="1.0.2" />
                    
Directory.Packages.props
<PackageReference Include="WebVella.Npgsql.Extensions" />
                    
Project file
For projects that support Central Package Management (CPM), copy this XML node into the solution Directory.Packages.props file to version the package.
paket add WebVella.Npgsql.Extensions --version 1.0.2
                    
#r "nuget: WebVella.Npgsql.Extensions, 1.0.2"
                    
#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.
#addin nuget:?package=WebVella.Npgsql.Extensions&version=1.0.2
                    
Install WebVella.Npgsql.Extensions as a Cake Addin
#tool nuget:?package=WebVella.Npgsql.Extensions&version=1.0.2
                    
Install WebVella.Npgsql.Extensions as a Cake Tool

Project Homepage Dotnet GitHub Repo stars Nuget version Nuget download License

Checkout our other projects:
WebVella ERP
Data collaboration - Tefter.bg
Document template generation

What is WebVella.Npgsql.Extensions?

Open source library which extends npgsql with seamless and easy use of nested transactions and advisory locks

How to get it

You can either clone this repository or get the Nuget package

Please help by giving a star

GitHub stars guide developers toward great tools. If you find this project valuable, please give it a star – it helps the community and takes just a second!⭐

Getting started

The library provides 4 public interfaces:
IWvDbService - provides simple api for working with npgsql connections, transactions and advisory locks
IWvDbConnection - its a npgsql connection wrapper with attached context
IWvDbTransactionScope - transaction scope for npgsql connections, with support of nested transactions usage
IWvDbAdvisoryLockScope - advisory lock scope for executing sql commands with advisory locks

Without dependency injection

Create an instance of IWvDbService

You can provide sql connection string as argument

using Microsoft.Extensions.Configuration;
using WebVella.Npgsql.Extensions;

var conString = "Host=localhost;Username=username;Password=password;Database=testdb";
IWvDbService dbService = new WvDbService(conString);

or load service configuration from configuration file

var config = new ConfigurationBuilder()
	.SetBasePath(Directory.GetCurrentDirectory())
	.AddJsonFile("appsettings.json")
	.Build();

var dbServiceConfig = new WvDbServiceConfiguration();
config.Bind(dbServiceConfig);

IWvDbService dbService = new WvDbService(dbServiceConfig);

With dependency injection

var config = new ConfigurationBuilder()
	.SetBasePath(Directory.GetCurrentDirectory())
	.AddJsonFile("appsettings.json")
	.Build();

var dbServiceConfig = new WvDbServiceConfiguration();
config.Bind(dbServiceConfig);

//it will inject necessary dependencies to use dbService
services.AddWvDbService(dbServiceConfig);

Connection and Command

Note: no connection open call is needed, the connection is opened automatically during its creation.
Calling IWvDbConnection.CreateCommand() will create NpogsqlCommand and other npgsql classes can be used for getting or updating data.

//connection is open on its creation and closed on leaving the scope
using var connection = dbService.CreateConnection();

//do something with database
var command = connection.CreateCommand("SELECT 1;");
await command.ExecuteNonQueryAsync();
TransactionScope

Here is how simple transaction scope looks like. The connection is opened on its creation and closed on leaving the scope.

using( var scope = dbService.CreateTransactionScope() )
{
	var command = scope.Connection.CreateCommand("SELECT 1;");
	await command.ExecuteNonQueryAsync();

	//complete commits current transaction
	scope.Complete();
}

You can use nested TransactionScopes. I this case same connection will be used for all scopes, but new savepoint will be created for each nested transaction scope. If something fales in nested transaction scope or Complete() is not called, it will rollback to the savepoint and code can continue with upper transaction scope.

using( var scope = dbService.CreateTransactionScope() )
{
	//do something with database
	...

	using( var scope = dbService.CreateTransactionScope() )
	{
		//do something with database
		...
		scope.Complete();
	}

	scope.Complete();
}
AdvisoryLockScope

AdvisoryLockScopes have similar usage as TransactionScopes.

const long lockKey = 100;
using (var scope = dbService.CreateAdvisoryLockScope(lockKey))
{
	var command = scope.Connection.CreateCommand("SQL TO UPDATE SOMETHING");
	await command.ExecuteNonQueryAsync();
}

YOu can use nested AdvisoryLockScopes also, but be advised using multiple keys with advisory locks is not recommended, because it can lead to deadlocks.

const long lockKey = 100;
using (var scope = dbService.CreateAdvisoryLockScope(lockKey))
{
	var command = scope.Connection.CreateCommand("SQL TO UPDATE SOMETHING");
	await command.ExecuteNonQueryAsync();

	const long nestedLockKey = 101;
	using (var scope = dbService.CreateAdvisoryLockScope(nestedLockKey))
	{
		var command = scope.Connection.CreateCommand("SQL TO UPDATE SOMETHING ELSE");
		await command.ExecuteNonQueryAsync();
	}
}

License

Library license details

Product Compatible and additional computed target framework versions.
.NET net9.0 is compatible.  net9.0-android was computed.  net9.0-browser was computed.  net9.0-ios was computed.  net9.0-maccatalyst was computed.  net9.0-macos was computed.  net9.0-tvos was computed.  net9.0-windows was computed.  net10.0 was computed.  net10.0-android was computed.  net10.0-browser was computed.  net10.0-ios was computed.  net10.0-maccatalyst was computed.  net10.0-macos was computed.  net10.0-tvos was computed.  net10.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.0.2 187 4/14/2025
1.0.1 184 4/14/2025
1.0.0 170 4/13/2025

Initial release