CODEWITHSUNDEEP
c#.netormodbc
kmarks2
kmarks2

Reputation: 4875

Maintaining a .NET OdbcConnection object over a long time

I have a very small Data abstraction class inside a very long running service. It is a static class and called statelessly as a cross-class utility (like just DataTable someResults = Data.SelectFooFromBar()) -- I don't want to be instancing this in several discrete and unrelated classes. The service does Data.Initialize(connectionString) as it comes up. The Initialize looks like:

private static string connectionString;
private static OdbcConnection connection;

public static void Initialize(string mySqlConnectionString)
{
   /*mySqlConnectionString would look something like this:
     "Provider=MSDASQL;
      Driver={MySQL ODBC 3.51 Driver};
      UID=some_db_user;
      PWD=some_db_pass;
      Server=db-host;
      Port=3306;
      Database=some_db;
      Protocol=TCP;
      Compress=false;
      Pooling=true;
      Min Pool Size=0;
      Max Pool Size=100;
      Connection Lifetime=0;
      OPTION=16386;"*/

   //Ensure only one connection exists to prevent leaks, and also
   // only modify the connection if the connection string has changed:
   if (connection == null || connectionString != mySqlConnectionString)
   {
      connectionString = mySqlConnectionString;
      connection = new OdbcConnection(connectionString);
   }
}

And here is a method used by the public facing method that actually sanitizes and executes the query:

private static DataTable Select(string query)
{
   try
   {
      query = Sanitize(query);

      connection.Open();
      DataTable dt = new DataTable();
      OdbcDataAdapter da = new OdbcDataAdapter(query, connection);
      da.Fill(dt);
      connection.Close();

      return dt;
   }
   catch (Exception ex)
   {
      connection.Close();
      ExceptionHandler(ex, query);
      return null;
   }
}

And finally an actual public facing data method:

public static DataTable SelectFooFromBar()
{
   return Select("SELECT foo FROM bar");
}

My question is what is the best way to ensure my connection object is always good?

Upvotes: 0

Views: 711

Answers (3)

Serialize
Serialize

Reputation: 491

You should not leave it open all the time. You will have to write even more code to monitor the state of the connection and handle timeout situations, network connectivity issues, etc. for a long running open connection.

You will notice that most people wrap connection in Using. There isn't much cost in establishing your connection as needed.

Upvotes: 1

Kevin Seifert
Kevin Seifert

Reputation: 3572

If you are not using a connection pool, create a function GetConnection that returns an open connection. Inside this function, test if the global connection object is null or closed. If so, re-open it. It doesn't matter if the connection closes, all that matters is that it's open when you need it.

Upvotes: 0

Servy
Servy

Reputation: 203802

You should be opening up a new connection for every single operation, rather than opening up one connection and reusing it.

Behind the scenes the actual DB adapter will have a connection pool. A number of connections that it keeps open and assigns when a new connection is requested. Normally creating a new connection is expensive, but due to connection pooling this expense is simply not felt when creating a lot of connections for short periods of use.

(Of course, you'd want to make sure connection pooling is enabled in your DB configuration, but based on what you showed, it clearly already is.)

If your connection fails to connect it will throw an exception. Let that exception bubble up to somewhere that can appropriately deal with the situation. This is almost certainly not your DAL, but rather up into your business logic where you can, in some way, inform the user of the error and determine what should be done about it.

Upvotes: 2

Related Questions