CODEWITHSUNDEEP
c#oracle-database
user147504
user147504

Reputation: 165

open/close an oracle connection multiple times c#

In my business logic, I use multiple oracle query's multiple times. What is the best way to open and close the oracle connection?

private void update()
{
     OracleConnection con = new OracleConnection("Connection Statement");
     OracleCommand command = new OracleCommand("Select Statement");
     con.Open();
     OracleDataReader reader = command.ExecuteReader();

     reader.Close();
     con.Close();

     // A for loop

     con.Open();
     command = new OracleCommand("Update statement");
     command.ExecuteNonQuery();
     con.Close();

     con.Open();
     command = new OracleCommand("Second Update statement");
     command.ExecuteNonQuery();
     con.Close();
}

My code looks like this. Should I open and close my oracle connection for every command or open before the first command and close after the last command.

P.S. This update function is called over 100 times in my application.

Upvotes: 3

Views: 2303

Answers (1)

Igor
Igor

Reputation: 62213

As the connection is local to the method create it in a using block and then use it as many times as you need to in that block. The block can contain loops or whatever, there is no rule that states you have to discard the connection after you use it once.

However connection sharing is discouraged, so do not create a class level instance or static instance for shared use.

private void update()
{
     using(OracleConnection con = new OracleConnection("Connection Statement"))
     {
         con.Open();
         using(var command = new OracleCommand("Select Statement", con))
         using(OracleDataReader reader = command.ExecuteReader()}
         {

         }

         // A for loop

         using(var command = new OracleCommand("Update statement", con))
         {
           command.ExecuteNonQuery();
         }

         using(var command = new OracleCommand("Second Update statement", con))
         {
           command.ExecuteNonQuery();
         }
     }
}

Upvotes: 6

Related Questions