Data Access with PostgreSQL using ADO.NET

This document explores how to effectively use ADO.NET to connect to and interact with PostgreSQL databases. We will cover essential concepts, best practices, and provide practical code examples.

1. Installing the Npgsql Provider

The Npgsql Data Provider is the official .NET data provider for PostgreSQL. You can install it using the NuGet Package Manager:

dotnet add package Npgsql
            

Alternatively, you can use the Package Manager Console:

Install-Package Npgsql
            

2. Establishing a Connection

The core of ADO.NET data access is establishing a connection to the database. With Npgsql, this is done using the NpgsqlConnection class.

Connection String

A connection string contains all the information needed to connect to your PostgreSQL server. A typical connection string looks like this:

Server=my_host;Port=5432;Database=my_database;User Id=my_user;Password=my_password;
            

Code Example: Opening a Connection

using Npgsql;
using System;

public class DatabaseConnector
{
    public void ConnectToPostgres()
    {
        string connectionString = "Server=localhost;Port=5432;Database=mydatabase;User Id=postgres;Password=mypassword;";

        using (var conn = new NpgsqlConnection(connectionString))
        {
            try
            {
                conn.Open();
                Console.WriteLine("Successfully connected to PostgreSQL!");
                // You can now perform database operations
            }
            catch (NpgsqlException ex)
            {
                Console.WriteLine($"Error connecting to PostgreSQL: {ex.Message}");
            }
        }
    }
}
            

Using the using statement ensures that the connection is properly closed and disposed of, even if errors occur.

3. Executing Commands

Once connected, you can execute SQL commands using NpgsqlCommand objects. This includes queries, inserts, updates, and deletes.

Executing Queries (SELECT)

To retrieve data, you'll typically use a SELECT statement. The results are usually returned as a NpgsqlDataReader.

using Npgsql;
using System;
using System.Collections.Generic;

public class DataReaderExample
{
    public List<string> GetUsers()
    {
        var users = new List<string>();
        string connectionString = "Server=localhost;Port=5432;Database=mydatabase;User Id=postgres;Password=mypassword;";

        using (var conn = new NpgsqlConnection(connectionString))
        {
            conn.Open();
            string sql = "SELECT username FROM users WHERE is_active = @isActive;";

            using (var cmd = new NpgsqlCommand(sql, conn))
            {
                cmd.Parameters.AddWithValue("@isActive", true); // Parameterized query

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        users.Add(reader.GetString(0)); // Get the first column (index 0) as a string
                    }
                }
            }
        }
        return users;
    }
}
            

Always use parameterized queries to prevent SQL injection vulnerabilities.

Executing Non-Query Commands (INSERT, UPDATE, DELETE)

For commands that do not return a result set (like INSERT, UPDATE, DELETE), use ExecuteNonQuery(). This method returns the number of rows affected.

using Npgsql;
using System;

public class DataModifier
{
    public int AddUser(string username)
    {
        int rowsAffected = 0;
        string connectionString = "Server=localhost;Port=5432;Database=mydatabase;User Id=postgres;Password=mypassword;";

        using (var conn = new NpgsqlConnection(connectionString))
        {
            conn.Open();
            string sql = "INSERT INTO users (username, created_at) VALUES (@username, NOW());";

            using (var cmd = new NpgsqlCommand(sql, conn))
            {
                cmd.Parameters.AddWithValue("@username", username);
                rowsAffected = cmd.ExecuteNonQuery();
            }
        }
        return rowsAffected;
    }
}
            

4. Working with DataAdapters and DataSets

NpgsqlDataAdapter and DataSet (or DataTable) provide a way to fill a disconnected cache of data and work with it without keeping a constant connection open. This is useful for scenarios like displaying data in a grid.

using Npgsql;
using System;
using System.Data;

public class DataAdapterExample
{
    public DataTable GetProducts()
    {
        var dataTable = new DataTable();
        string connectionString = "Server=localhost;Port=5432;Database=mydatabase;User Id=postgres;Password=mypassword;";

        using (var conn = new NpgsqlConnection(connectionString))
        {
            conn.Open();
            string sql = "SELECT product_id, product_name, price FROM products;";

            using (var da = new NpgsqlDataAdapter(sql, conn))
            {
                da.Fill(dataTable); // Fills the DataTable with data
            }
        }
        return dataTable;
    }
}
            

5. Transactions

Transactions are crucial for ensuring data integrity when performing multiple database operations that must succeed or fail as a unit.

using Npgsql;
using System;

public class TransactionExample
{
    public bool TransferFunds(int fromAccountId, int toAccountId, decimal amount)
    {
        bool success = false;
        string connectionString = "Server=localhost;Port=5432;Database=mydatabase;User Id=postgres;Password=mypassword;";

        using (var conn = new NpgsqlConnection(connectionString))
        {
            conn.Open();
            using (var transaction = conn.BeginTransaction())
            {
                try
                {
                    // Withdraw from source account
                    string withdrawSql = "UPDATE accounts SET balance = balance - @amount WHERE account_id = @accountId;";
                    using (var cmd = new NpgsqlCommand(withdrawSql, conn, transaction))
                    {
                        cmd.Parameters.AddWithValue("@amount", amount);
                        cmd.Parameters.AddWithValue("@accountId", fromAccountId);
                        cmd.ExecuteNonQuery();
                    }

                    // Deposit to destination account
                    string depositSql = "UPDATE accounts SET balance = balance + @amount WHERE account_id = @accountId;";
                    using (var cmd = new NpgsqlCommand(depositSql, conn, transaction))
                    {
                        cmd.Parameters.AddWithValue("@amount", amount);
                        cmd.Parameters.AddWithValue("@accountId", toAccountId);
                        cmd.ExecuteNonQuery();
                    }

                    transaction.Commit(); // Commit the transaction
                    success = true;
                }
                catch (Exception)
                {
                    transaction.Rollback(); // Rollback if any error occurs
                    success = false;
                    // Log the exception here
                }
            }
        }
        return success;
    }
}
            

6. Best Practices

• Use Parameterized Queries: Always use parameters to pass values to SQL commands to prevent SQL injection.
• Connection Pooling: ADO.NET providers, including Npgsql, typically implement connection pooling automatically. This reuses database connections, improving performance.
• Dispose Resources: Ensure that IDisposable objects like NpgsqlConnection, NpgsqlCommand, and NpgsqlDataReader are properly disposed of, usually by using the using statement.
• Error Handling: Implement robust error handling to gracefully manage database connection and query errors.
• Keep Connections Open Only When Necessary: Open connections just before you need them and close them as soon as you're done.
• Optimize SQL Queries: Write efficient SQL queries and leverage PostgreSQL's indexing capabilities.

Conclusion

ADO.NET, with the Npgsql provider, offers a powerful and flexible way to integrate PostgreSQL databases into your .NET applications. By following best practices and understanding the core components, you can build robust and performant data-driven solutions.