how to use only single connection to execute multiple queries in Npgsql?

Question

I have created multiple connections in npgsql to execute multiple queries as shown below code.

    class TransactionAccess
{
    private const string connString = "Host=localhost;Username=postgres;Password=1234;Database=ExpenseManagerDB";
    public static void GetTransactions()
    {
        using (var connection = new NpgsqlConnection(connString))
        {
            var transactions = connection.Query<TransactionView>(@"SELECT t.transaction_id,t.account_id,a.account_name, a.type,t.note, t.amount, t.date
                                                               FROM account AS a
                                                               INNER JOIN transaction AS t ON a.account_id = t.account_id");
            transactions.Dump();
        }
    }

    public static void GetTransactionInfo(int id)
    {
        using (var connection = new NpgsqlConnection(connString))
        {
            var transactionInfo = connection.Query<TransactionView>(@"SELECT a.account_name, a.type, DATE(t.date), t.amount, t.note, t.transaction_id 
                                                                  FROM transaction AS t 
                                                                  INNER JOIN account AS a ON t.account_id = a.account_id 
                                                                  WHERE t.transaction_id = @id", new { id });
            transactionInfo.Dump();
        }
    }

    public static void MakeTransaction(Transaction transaction, Account account)
    {
        using (var connection = new NpgsqlConnection(connString))
        {
            connection.Execute(@"INSERT INTO transaction(account_id,amount,date,note)
                                         SELECT a.account_id,@amount, @date, @note
                                         FROM account AS a
                                         WHERE a.account_name=@account_name", new { transaction.Amount, transaction.Date, transaction.Note, account.Account_Name });
        }
    }
}

I wanted to execute all queries with a single connection. How can I do that?

why do you want it ? are these methods being called at once in sequence or are totally independent ? — kuldeep
– kuldeep, Commented Feb 7, 2022 at 5:27
these methods are independent, but I want to do only with a single connection only — mangalam
– mangalam, Commented Feb 7, 2022 at 5:33
@mangalam: Obvious answer is "open connection elsewhere, pass it as a parameter to TransactionAccess methods, dispose connection outside TransactionAccess methods after use". Detailed answer depends on why do you want to do this: these methods look unrelated to each other. — Dennis
– Dennis, Commented Feb 7, 2022 at 6:15
@mangalam. Define Connection as an Instance variable - > Initialize it in a constructor -> Use it in all the Methods -> Ensure you close the connection when the last method in the sequence it called. Effectively you will use one connection throughout. — rajorshi
– rajorshi, Commented Feb 7, 2022 at 6:30

TheSoftwareJedi · Accepted Answer · 2023-12-28 00:35:31Z

4

Why cannot you use Batching as mentioned in Npgsql documentation.

await using var batch = new NpgsqlBatch(conn)
{
    BatchCommands =
    {
        new("INSERT INTO table (col1) VALUES ('foo')"),
        new("SELECT * FROM table")
    }
};

await using var reader = await batch.ExecuteReaderAsync();

Source : https://www.npgsql.org/doc/basic-usage.html

PS : Thought of commenting first, but cannot do it because of less reputation points :D

edited Dec 28, 2023 at 0:35

TheSoftwareJedi

35.3k22 gold badges112 silver badges151 bronze badges

answered Sep 28, 2022 at 12:24

ManojRawat

811 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

shashwat Over a year ago

what is cmd here in await cmd.ExecuteReaderAsync()? you mean batch right?

TheSoftwareJedi Over a year ago

@shashwat yeah thats what they meant - i edited.

Collectives™ on Stack Overflow

how to use only single connection to execute multiple queries in Npgsql?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related