How to Use dotConnect for PostgreSQL with .NET: Quick Start and Best Practices
Overview
dotConnect for PostgreSQL is an ADO.NET provider offering enhanced data access features, ORM support, and performance optimizations for PostgreSQL in .NET applications. This quick-start guide shows how to install, configure, and use dotConnect in a .NET project and shares practical best practices for reliability, performance, and maintainability.
Prerequisites
- .NET SDK (6.0 or later recommended)
- PostgreSQL server reachable from your development environment
- dotConnect for PostgreSQL NuGet package (or installer) and a valid license if required
- Basic familiarity with ADO.NET and .NET project structure
1. Install dotConnect for PostgreSQL
Using the NuGet package is the simplest approach:
bash
dotnet add package Devart.Data.PostgreSql
Alternatively, install via Visual Studio’s NuGet Package Manager or use the vendor installer for design-time tools and additional components.
2. Add a Connection String
Store connection strings securely (appsettings.json, environment variables, or a secrets manager). Example appsettings.json entry:
json
{ “ConnectionStrings”: { “PgConn”: “Host=localhost;Port=5432;Database=mydb;User> } }
Load it in .NET via IConfiguration:
csharp
var connectionString = configuration.GetConnectionString(“PgConn”);
3. Basic ADO.NET Usage (Quick Start)
Example demonstrating connection, command execution, parameterized query, and reading results:
csharp
using Devart.Data.PostgreSql; using Microsoft.Extensions.Configuration; var connString = configuration.GetConnectionString(“PgConn”); using (var conn = new PgSqlConnection(connString)) { conn.Open(); using (var cmd = conn.CreateCommand()) { cmd.CommandText = “SELECT id, name, created_at FROM users WHERE isactive = @active”; cmd.Parameters.Add(new PgSqlParameter(”@active”, true)); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { var id = reader.GetInt32(0); var name = reader.GetString(1); var created = reader.GetDateTime(2); Console.WriteLine($”{id}: {name} ({created:yyyy-MM-dd})”); } } } }
4. Using dotConnect ORM Features (Optional)
dotConnect supports Entity Framework and Devart’s own ORM enhancements. To use EF Core provider:
bash
dotnet add package Devart.Data.PostgreSql.EFCore
Example DbContext configuration:
csharp
using Microsoft.EntityFrameworkCore; services.AddDbContext<MyDbContext>(options => options.UseNpgsql(connectionString)); // If using Devart EF Core provider, use Devart-specific UseDevartPostgreSql if provided
Define entities and run migrations as usual. Consult the provider docs for provider-specific configuration and conventions.
5. Transactions and Error Handling
Always use transactions for multi-step operations and ensure proper disposal:
csharp
using (var conn = new PgSqlConnection(connString)) { conn.Open(); using (var tran = conn.BeginTransaction()) { try { // commands using the same connection and transaction tran.Commit(); } catch { tran.Rollback(); throw; } } }
Catch provider-specific exceptions (PgSqlException) to inspect error codes and messages.
6. Connection Pooling
dotConnect supports connection pooling. Use sensible connection-string settings:
- Max Pool Size: controls maximum pooled connections (default often 100)
- Min Pool Size: keep some connections ready for warm-up
- Connection Lifetime and Timeout: tune for your environment
Example snippet:
Host=…;Port=…;Database=…;User Pool Size=1;Max Pool Size=50;Connection Lifetime=300;
Open connections as late as possible, close promptly (use using blocks) to return connections to the pool.
7. Performance Tips
- Use prepared statements and parameterized queries to avoid parsing overhead.
- Use PgSqlDataReader for forward-only reads; avoid loading entire result sets unless needed.
- Prefer bulk loading APIs (COPY) for large inserts when supported by the provider.
- Use appropriate indexes and monitor slow queries in PostgreSQL (EXPLAIN ANALYZE).
- Enable appropriate command timeout and keep transactions short.
8. Security Best Practices
- Never hard-code credentials; use environment variables or secrets storage.
- Use SSL/TLS (SSL Mode=Require
Leave a Reply