0

First off, prior to asking this question I have been researching connection pooling for the better part of my morning. What I've found is that is turned on by default, handled by the application/system, and C#'s System.Data.X namespaces (where X is something like OLEDBConnection / SqlConnection ) handles it automatically.

But as I'm still new to the whole database access and pooling, I am looking for clarification since I wrote a class to handle connections at a higher level.

public abstract class AbstractDatabase<T> where T: where T : System.Data.Common.DbConnection
{
   // Take this class's connection string and return a new connection object
   public abstract T GetConnection();
   
   public abstract SqlKata.Compilers.Compiler { get; }


   // Compile the query and return a DataTable
   // This is actually pointing to a static method that uses the IDBConnection interface and logs any errors, but I copied the relavant code here
   public virtual DataTable GetDataTable(SqlKata.Query query)
   {
      using (var Conn = GetConnection())
      {
         if (Conn.State == ConnectionState.Closed) Conn.Open();
         using (var Cmd = Conn.CreateCommand())
         {
            Cmd.CommandType = CommandType.Text;
            Cmd.Connection = Conn;
            Cmd.CommandText = Compiler.Compile(Query).ToString(); // the query is fully formed string here
            Cmd.Parameters.AddDummyParameter(); // I noticed that without any parameters, this didn't work, so this method adds a dummy, even if the string doesn't have any '?' characters
            using (var DR = Cmd.ExecuteReader())
            {
               DataTable DT = new DataTable();
               DT.Load(DR);
               return DT;
            }   
         }
      }
   }
}

public class SomeDatabase : AbstractDatabase<OleDbConnection> 
{ 
    // class implementation 
} 

here is my question:

If I were to somewhere else in my program do something like this:

DataTable Tbl1 = SomeDatabase.GetDataTable(Query1);
DataTable Tbl2 = SomeDatabase.GetDataTable(Query2);
....
DataTable Tbl10 = SomeDatabase.GetDataTable(Query10);

My understanding is that the method's Using ( var Conn = GetConnection()) statement will automatically issue a Conn.Close() and dispose of the Conn as the method exits. But connection pooling is automatically enabled, so it will actually reuse that connection almost immediately instead of closing it, correct?

With that level of abstraction, does it make sense to write it like this?

using ( var conn = SomeDatabase.GetConnection())
{
   DataTable Tbl1 = SomeDatabase.GetDataTable(Query1);
   ....
   DataTable Tbl10 = SomeDatabase.GetDataTable(Query10);
}
  • Would this make any difference?
  • Should the method be written to accept the connection that already open ?
  • Should the class be written to use the same connection object itself, rather than calling GetConnection() every time ? ( for example, a get-only property that initializes the backing field on first request to it, then all subsequent requests actually receive the backing field's object)
10
  • Does this answer your question? C# Data Connections Best Practice? Commented Jul 21, 2022 at 13:45
  • 1
    In rare situations, mainly when performance is an issue, then yes it's worth it. Otherwise the extra complexity of passing an external connection isn't worth it, just create a new connection and dispose with using. And if (Conn.State == ConnectionState.Closed) is unnecessary in that case. Consider also retrieving all three resultsets in a single batch using DR.NextResult() Commented Jul 21, 2022 at 13:46
  • For most of the calls, the database will reside local on the user's pc, so for those the performance really isn't a concern. But the primary database the local db is synced from (read-only) will be remote. Most users will have to VPN into the home office then initiate the database sync to update their local db with latest info. Hence this question, since re-opening over the remote connection is likely going to be slow, and I wanted to alleviate that impact if possible Commented Jul 21, 2022 at 14:20
  • 1
    Given connection pooling, as you rightly point out, it doesn't close and reopen the connection unless there is a big gap between calls. But it still calls sp_reset_connection which has a tiny perf impact. To avoid even that you can put three SELECTs in the same batch. Commented Jul 21, 2022 at 14:26
  • 1
    Yes that is exactly what you do, and you loop both NextResult and Read. If you want to call DataTable.Load then instead create a DataSet and call Load on that, which will call NextResult for you and create a set of datatables. Commented Jul 21, 2022 at 14:59

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.