0

I have to run different sql statement in one connection may be 5-6. Example:

  1. Checking some condition in a table.
  2. Updating another table on the basis of value received from first command.
  3. Inserting new data to some other table on the basis of result of first command.

All of the command contains parameterized query.

This is how I am approaching to it. Is is good approach? Does this create multiple round trips to database? Or am I wrong on round trips. Please suggest me on this.

using (NpgsqlConnection connection = new NpgsqlConnection (connectionString))
{
    connection.Open();
    using (NpgsqlCommand command1 = new NpgsqlCommand (commandText1, connection))
    {
      //select
    }
    using (NpgsqlCommand command2 = new NpgsqlCommand (commandText2, connection))
    {
      //update
    }
    // etc
}
7
  • 1
    NpgsqlConnection connection = new NpgsqlConnection(connectionString) Commented Mar 18, 2020 at 18:33
  • 1
    Alternatively you can put some of the logic into a [stored procedure[(postgresqltutorial.com/postgresql-stored-procedures) which would allow you to make one round trip to/from the database. If that is worth it or not depends on both the complexity of the logic as well as the frequency this method is called. Commented Mar 18, 2020 at 18:36
  • 1
    Assuming NpgsqlConnection is well-behaved, your example code will, in fact, run n commands using one connection to the DB. However, I second @Igor's comment that you consider converting this logic into a stored procedure. Commented Mar 18, 2020 at 18:39
  • It carries a lot of business logic and can't be directly exposed in store procedures. Commented Mar 18, 2020 at 18:40
  • Then there is nothing of substance left to discuss. As such any answer would be rooted in either speculation or in opinion. Commented Mar 18, 2020 at 18:45

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.