How to use only one Connection to execute multiple commands and queries?

Question

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

with async function calls, check out this link https://stackoverflow.com/a/41760048/125032 — hazimdikenli, Feb 07 '22 at 07:45
@hazimdikenli: In that solution also he is using multiple connections, but I wanted to use only one connection — venkatesh, Feb 07 '22 at 07:48
Any reason why you want to use one connection? Are you aware of .Net's connection pool? — Gert Arnold, Feb 07 '22 at 07:55
How did you come with the conclusion that he is using multiple connections, I see a single context, and a context works with a single connection, as result it is using a single connection. I think what you are asking and what you need are completely different? — hazimdikenli, Feb 07 '22 at 08:20
you should store the `connection` object in your class level member variable after opening the connection once. You cannot span the using block across methods like you've shown. this is more like understand of using member variables of C# class, than to do with postgres. — Anand Sowmithiran, Feb 07 '22 at 10:28

score 0 · Answer 1 · answered Feb 07 '22 at 07:52

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.

score 0 · Answer 2 · answered Feb 07 '22 at 10:34

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();
 }

How to use only one Connection to execute multiple commands and queries?

2 Answers2

Linked