Here is Complete code link

I wanted to use only npgsql connection to execute multiple queries, instead of using multiple connections as shown below:

class TransactionAccess
{
    private const string connString = "Host=localhost;Username=postgres;Password=1234;Database=ExpenseManagerDB";
        public static void GetExpense(DateTime startDate, DateTime endDate)
    {
        using (var connection = new NpgsqlConnection(connString))
        {
            var Expense = connection.Query<TransactionView>(@"SELECT SUM(t.amount) as Expense 
                                                        FROM transaction AS t 
                                                        INNER JOIN account AS a ON t.account_id = a.account_id 
                                                        WHERE t.date BETWEEN @startDate AND @endDate AND a.type = 'expense'", new { startDate, endDate });
            Expense.Dump();
        }
    }

    public static void GetFilteredTransactionsList(DateTime startDate, DateTime endDate)
    {
        using (var connection = new NpgsqlConnection(connString))
        {
            var filteredTransactions = connection.Query<TransactionView>(@"SELECT a.account_name, a.account_id, a.type, DATE(t.date), t.transaction_id, t.amount, t.note 
                                                                       FROM transaction AS t 
                                                                       INNER JOIN account AS a ON t.account_id = a.account_id 
                                                                       WHERE t.date BETWEEN @startDate AND @endDate
                                                                       ORDER BY t.date", new { startDate, endDate });
            filteredTransactions.Dump();
        }
    }
}

Instead, I want to do something like this:

    class TransactionAccess
{
    private const string connString = "Host=localhost;Username=postgres;Password=1234;Database=ExpenseManagerDB";
    using (var connection = new NpgsqlConnection(connString))
{
public static void GetExpense(DateTime startDate, DateTime endDate)
    {
            var Expense = connection.Query<TransactionView>(@"SELECT SUM(t.amount) as Expense 
                                                        FROM transaction AS t 
                                                        INNER JOIN account AS a ON t.account_id = a.account_id 
                                                        WHERE t.date BETWEEN @startDate AND @endDate AND a.type = 'expense'", new { startDate, endDate });
            Expense.Dump();
        
    }

    public static void GetFilteredTransactionsList(DateTime startDate, DateTime endDate)
    {
            var filteredTransactions = connection.Query<TransactionView>(@"SELECT a.account_name, a.account_id, a.type, DATE(t.date), t.transaction_id, t.amount, t.note 
                                                                       FROM transaction AS t 
                                                                       INNER JOIN account AS a ON t.account_id = a.account_id 
                                                                       WHERE t.date BETWEEN @startDate AND @endDate
                                                                       ORDER BY t.date", new { startDate, endDate });
            filteredTransactions.Dump();
    
    }
}

like this, using a single connection to execute multiple queries and methods/ How can I do that?

If anyone can help a bit more please edit the code accordingly in here

CodePudding user response：

You can only run a single statement on one database connection at the same time (unless you are using cursors, but then the same holds for the FETCH statements). So if you want several threads to issue database statements independently, you either have to serialize access to the database connection or you have to use multiple connections.

CodePudding user response：

Create a member variable for your TransactionAccess class, named as conn of type NpgsqlConnection. And also make your class implement the IDisposable interface.

You should open the connection only in the constructor of this class and initialize it like below,

  conn = new NpgsqlConnection(connString);

The other member functions like GetExpense() etc, can use the conn member to execute the queries as per your business logic.

And in your class' Dispose method, remember to close the connection, like so,

 public void Dispose()
 {
    conn.Close();
 }