C # database shell design

Question

I am creating a database shell for C #. The following are two options:

Option A:

class DBWrapper:IDisposable
{
     private SqlConnection sqlConn;

     public DBWrapper()
     {
            sqlConn = new SqlConnection("my connection string");
            sqlConn.Open();
     }

     public DataTable RunQuery(string Sql)
     {
              implementation......
     }

     public Dispose()
     {
            if(sqlConn != null)
                   sqlConn.Close();
     }
}

Option B:

class DBWrapper
{
     public DBWrapper()
     {            
     }

     public DataTable RunQuery(string Sql)
     {
             SqlConnection sqlConn = new SqlConnection("my connection string");
             .....implementation......
             sqlConn.Close();               
     }   
}

For option A, the connection opens when the class is instantiated. Therefore, no matter how many times the caller calls the RunQuery call, the connection is always ready. But if the application creates an instance of DBWrapper at the beginning of the application, the connection will just be open and will not do anything until the application is completed. In addition, many DBWrapper instances may occur at run time. So it seems to be wasting resources.

For option B, there is no problem with parameter A, but a new connection needs to be opened and closed each time the caller calls RunQuery. I'm not sure how much this will hurt performance.

, . , .

Answer 1

B ( , , ). C:

class DBWrapper:IDisposable { private SqlConnection sqlConn;

public void EnsureConnectionIsOpen()
{
    if (sqlConn == null)
    {
      sqlConn = new SqlConnection("my connection string");
      sqlConn.Open();
    }

}

public DataTable RunQuery(string Sql)
{
    EnsureConnectionIsOpen();
    implementation......
}

public Dispose()
{
   if(sqlConn != null)
              sqlConn.Close();
}

} 

singleton, , DBWrapper.

Answer 2

, :

, () , , . , NETCF .

, , , , ( ) try/finally + dispose() .

!

Answer 3

, , , , . , , , ( ) . , , . , , , .

Answer 4

B , . ADO.NET , , SqlConnection .

, ; .

, , , .

Answer 5

C, RunQuery ( ) ( ). , .

, :

class DBWrapper
{

     public DBWrapper()
     {            
     }

     SqlConnection sqlConn = null; 

     public DataTable RunQuery(string Sql)
     {
             if(sqlConn == null) sqlConn = new SqlConnection("my connection string");
             .....implementation......               
     }   
     public Dispose()
     {
            if(sqlConn != null)
                   sqlConn.Close();
     }

}

Also remember that the Dispose moment is not always called immediately after the object is no longer needed (for example, a function variable after using the function). As far as I know, it will be executed when the garbage collector collects the object (which is not direct). But I'm not quite sure about that. This behavior may also vary between a web and non-web application.