使用Npgsql+PostgreSQL，求ADO.NET/MVC统一事务入口方案

Absolutely! You can absolutely build a unified repository to handle all your ADO.NET database operations (including transactions) with Npgsql, eliminating that repetitive boilerplate code you're dealing with. Let's walk through a practical implementation tailored to your PostgreSQL + MVC scenario.

1. 创建通用仓储基类 #

First, we'll encapsulate all repeated connection, command, parameter, and transaction logic into a single repository class. This will act as your single entry point for all database interactions.

using Npgsql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Configuration;

public class PostgreSqlRepository : IDisposable
{
    private readonly NpgsqlConnection _connection;
    private NpgsqlTransaction _transaction;

    public PostgreSqlRepository()
    {
        // Reuse your existing connection string logic
        string connectionString = ConfigurationManager.ConnectionStrings["Central"].ConnectionString;
        _connection = new NpgsqlConnection(connectionString);
    }

    // Safely open connection (avoid duplicate opens)
    public void OpenConnection()
    {
        if (_connection.State != ConnectionState.Open)
        {
            _connection.Open();
        }
    }

    // Safely close connection
    public void CloseConnection()
    {
        if (_connection.State != ConnectionState.Closed)
        {
            _connection.Close();
        }
    }

    // Start a transaction
    public void BeginTransaction()
    {
        OpenConnection();
        _transaction = _connection.BeginTransaction();
    }

    // Commit transaction and clean up
    public void CommitTransaction()
    {
        _transaction?.Commit();
        DisposeTransaction();
    }

    // Rollback transaction on failure
    public void RollbackTransaction()
    {
        _transaction?.Rollback();
        DisposeTransaction();
    }

    private void DisposeTransaction()
    {
        _transaction?.Dispose();
        _transaction = null;
    }

    // Generic query method: maps data reader results to your model type
    public List<T> ExecuteQuery<T>(string sql, NpgsqlParameter[] parameters, Func<IDataReader, T> mapper)
    {
        var results = new List<T>();
        
        OpenConnection();
        using (var cmd = new NpgsqlCommand(sql, _connection))
        {
            // Attach transaction if one is active
            if (_transaction != null)
            {
                cmd.Transaction = _transaction;
            }

            // Add parameters if provided
            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(parameters);
            }

            using (var rdr = cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    results.Add(mapper(rdr));
                }
            }
        }

        return results;
    }

    // Generic non-query method (for Insert/Update/Delete)
    public int ExecuteNonQuery(string sql, NpgsqlParameter[] parameters)
    {
        OpenConnection();
        using (var cmd = new NpgsqlCommand(sql, _connection))
        {
            if (_transaction != null)
            {
                cmd.Transaction = _transaction;
            }

            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(parameters);
            }

            return cmd.ExecuteNonQuery();
        }
    }

    // Auto-clean up resources
    public void Dispose()
    {
        // Rollback uncommitted transactions automatically
        RollbackTransaction();
        CloseConnection();
        _connection.Dispose();
    }
}

2. Simplify Controller Query Logic #

Now you can refactor your existing controller code to use this repository, eliminating all repetitive boilerplate. Here's how your fault stats query would look:

public ActionResult FaultStats(string reginalManagers, DateTime dtFrom, DateTime dtTo)
{
    // Use 'using' to ensure proper resource cleanup
    using (var repo = new PostgreSqlRepository())
    {
        string sql = @"SELECT xc.m_error_group_name, xc.m_error_group_id, count(*), error_duration FROM (
            SELECT extract(year from m_date) m_year, v1.m_error_group_name, v1.m_error_group_id
            -- Note: I fixed a small syntax issue in your original SQL here
            FROM your_table_name t1
            JOIN zd.t_users g ON(g.user_id = t1.pv_person_resp_id) 
            WHERE g.user_name IN(@rgnal)
            AND t1.m_date BETWEEN @fr AND @to 
            AND t1.crew_present = FALSE 
            AND t1.m_grid_loss = FALSE
            LEFT OUTER JOIN ssw_mdt.t_master_pc_alarm_pattern t3 ON (t1.m_inv_error_details = t3.pc_group_pattern)
        ) xc GROUP BY 1, 2 ORDER BY error_duration DESC";

        var parameters = new NpgsqlParameter[]
        {
            new NpgsqlParameter("@rgnal", reginalManagers),
            new NpgsqlParameter("@fr", dtFrom),
            new NpgsqlParameter("@to", dtTo)
        };

        // Map reader results to a helper class (cleaner than direct ViewModel population)
        var faultItems = repo.ExecuteQuery<FaultStatItem>(sql, parameters, rdr => new FaultStatItem
        {
            ErrorGroupName = rdr["m_error_group_name"].ToString(),
            ErrorGroupId = (int)rdr["m_error_group_id"],
            Count = (int)rdr["count"],
            ErrorDuration = (TimeSpan)rdr["error_duration"]
        });

        // Populate your ViewModel
        var flt = new FaultStatViewModel();
        flt.m_error_group_name.AddRange(faultItems.Select(item => item.ErrorGroupName));
        // Add other ViewModel properties from faultItems as needed

        return View(flt);
    }
}

// Helper class to hold single fault stat record
public class FaultStatItem
{
    public string ErrorGroupName { get; set; }
    public int ErrorGroupId { get; set; }
    public int Count { get; set; }
    public TimeSpan ErrorDuration { get; set; }
}

3. Transaction Handling Example #

For operations that need to run in a single transaction (e.g., insert + update), use the repository's transaction methods:

public ActionResult PerformTransactionalAction()
{
    using (var repo = new PostgreSqlRepository())
    {
        try
        {
            repo.BeginTransaction();

            // First operation: Insert
            string insertSql = "INSERT INTO audit_log (action, created_at) VALUES (@action, @now)";
            var insertParams = new NpgsqlParameter[]
            {
                new NpgsqlParameter("@action", "User created"),
                new NpgsqlParameter("@now", DateTime.UtcNow)
            };
            repo.ExecuteNonQuery(insertSql, insertParams);

            // Second operation: Update
            string updateSql = "UPDATE users SET last_login = @now WHERE id = @userId";
            var updateParams = new NpgsqlParameter[]
            {
                new NpgsqlParameter("@now", DateTime.UtcNow),
                new NpgsqlParameter("@userId", 123)
            };
            repo.ExecuteNonQuery(updateSql, updateParams);

            // Commit if all operations succeed
            repo.CommitTransaction();
            return RedirectToAction("Success");
        }
        catch (Exception ex)
        {
            // Rollback on any error
            repo.RollbackTransaction();
            // Log error here
            return RedirectToAction("Error");
        }
    }
}

Bonus Optimization Tips #

• Dependency Injection: Instead of instantiating the repository directly in controllers, inject it using a DI container (like Unity or Autofac) to follow MVC's dependency inversion principle and simplify testing.
• Error Handling: Add centralized exception handling in the repository to convert Npgsql-specific exceptions into application-friendly exceptions.
• Mapping: For complex ViewModels, consider using a library like AutoMapper to simplify DataReader-to-model mapping (optional, but reduces manual code).

内容的提问来源于stack exchange，提问作者papagallo