Database wrapper design for C#

Hi everyone,

I am designing a database wrapper for C#.
Below are the two options I have:

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();
     }
} 

Code (markup):

Option B:

class DBWrapper
{
     public DBWrapper()
     {            
     }

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

Code (markup):

For option A connection is opened when class is instantiated. So no matter how many times the caller calls RunQuery the connection is always ready. But If the application instantiates DBWrapper early in the application, the connection will be just opened and doing nothing until the application is finished. Also, it could have many DBWrapper instantiated during the execution. So, it's kinda wasting resources.

For option B it doesn't have the problem option A has, but the a new connection has to be opened and closed everytime the caller calls RunQuery. I am not sure how much it will hurt the performance.

Please share your expertise. Thank you for reading.

 

My recommendation would be to go for option B and keep the connection open for as short a time as possible. There should be no effect on performance as connection pooling will likely cache the connection details for you.

You could even go so far as to place the SqlConnection instance in a using clause to ensure the connection is disposed even in case of an exception:

using (SqlConnection sqlConn = new SqlConnection("my connection string"))
{
.....implementation......
sqlConn.Close();
}

 

It's good to know that connection pooling is likely to cache the connection details. Can someone confirm that?

 

Since you are using the SqlConnection class I'm assuming you'll be using SQL Server in one form or another. In this case, connection pooling will be supported.

 

So the connection pooling is done on the SQL server side not the application side?

 

On the client side - take a look at this link: http://msdn.microsoft.com/en-us/library/8xx3tyca(VS.71).aspx

 

I have written nice database wrapper and would like to share. Hope it will help you.

Command.cs
=============================
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public class DbCommand
{

public SqlCommand _Command;
private TransactionManager _TransManager = null;

#region "Constructors"

public DbCommand()
{
_Command = new SqlCommand();
}

public DbCommand(string pCommandText, CommandType pCommandType)
{
_Command = new SqlCommand(pCommandText);
_Command.CommandType = pCommandType;
}

public DbCommand(TransactionManager pTransManager)
{
_TransManager = pTransManager;
}

public DbCommand(string pCommandText, CommandType pCommandType, TransactionManager pTransManager)
{
_TransManager = pTransManager;
_Command = new SqlCommand(pCommandText);
_Command.CommandType = pCommandType;
}

#endregion

#region "Properties"

private static string ConnectionString
{
get { return ConfigurationManager.ConnectionStrings["ConnString"].ToString(); }
}


public string CommandText
{
get { return _Command.CommandText; }
set { _Command.CommandText = value; }
}

public CommandType CommandType
{
get { return _Command.CommandType; }
set { _Command.CommandType = value; }
}

public TransactionManager TransactionManager
{
get { return _TransManager; }
set { _TransManager = value; }
}


#endregion

#region "Parameter Methods"

public void AddInParameter(string pName, DbType pDbType, object pValue)
{
SqlParameter sqlParam = new SqlParameter(pName, pDbType);
sqlParam.Value = pValue;

if ((pValue != null))
{
sqlParam.Value = pValue;
}
else
{
sqlParam.Value = null;
}

sqlParam.Direction = ParameterDirection.Input;
_Command.Parameters.Add(sqlParam);
}

public void AddOutParameter(string pName, DbType pDbType)
{
SqlParameter sqlParam = new SqlParameter(pName, pDbType);
sqlParam.Direction = ParameterDirection.Output;
_Command.Parameters.Add(sqlParam);
}

#endregion

#region "Execute Methods"

public DataSet ExecuteDataset()
{
DataSet resultDs = new DataSet();
SqlDataAdapter sqlAdapter = new SqlDataAdapter();
try
{
if ((_TransManager != null))
{
_Command.Connection = _TransManager.Connection;
_Command.Transaction = _TransManager.Transaction;
}
else
{
_Command.Connection = new SqlConnection(ConnectionString);
_Command.Connection.Open();
}
sqlAdapter.SelectCommand = _Command;
sqlAdapter.Fill(resultDs);
}
finally
{
if ((_TransManager == null))
{
if ((_Command.Connection != null))
{
_Command.Connection.Close();
}
}
}
return resultDs;
}

public IDataReader ExecuteReader()
{
IDataReader dataRdr;
if ((_TransManager != null))
{
_Command.Connection = _TransManager.Connection;
_Command.Transaction = _TransManager.Transaction;
}
else
{
_Command.Connection = new SqlConnection(ConnectionString);
_Command.Connection.Open();
}
dataRdr = _Command.ExecuteReader();
return dataRdr;
}

public object ExecuteScalar()
{
object resultObj;
try
{
if ((_TransManager != null))
{
_Command.Connection = _TransManager.Connection;
_Command.Transaction = _TransManager.Transaction;
}
else
{
_Command.Connection = new SqlConnection(ConnectionString);
_Command.Connection.Open();
}
resultObj = _Command.ExecuteScalar();
}
finally
{
if ((_TransManager == null))
{
if ((_Command.Connection != null))
{
_Command.Connection.Close();
}
}
}
return resultObj;
}

public int ExecuteNonQuery()
{
int affectedRow;
try
{
if ((_TransManager != null))
{
_Command.Connection = _TransManager.Connection;
_Command.Transaction = _TransManager.Transaction;
}
else
{
_Command.Connection = new SqlConnection(ConnectionString);
_Command.Connection.Open();
}
affectedRow = _Command.ExecuteNonQuery();
}

finally
{
if ((_TransManager == null))
{
if ((_Command.Connection != null))
{
_Command.Connection.Close();
}
}
}
return affectedRow;
}

public void CloseConnection()
{
if ((_TransManager == null))
{
if ((_Command.Connection != null))
{
if ((_Command.Connection.State != ConnectionState.Closed))
{
_Command.Connection.Close();
}
}
}
}

#endregion

}


===================================
TransactionManager.cs
===================================
using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;

public class TransactionManager
{

private SqlTransaction _Transaction;
private SqlConnection _Connection;
private bool _IsTransactionOpen = false;


#region "Properties"

private string ConnectionString {
get { return ConfigurationManager.ConnectionStrings["ConnString"].ToString(); }
}

public SqlTransaction Transaction {
get { return _Transaction; }
}

public SqlConnection Connection {
get { return _Connection; }
}

public bool IsOpen {
get { return _IsTransactionOpen; }
}

#endregion

#region "Exposed Methods"

public void BeginTransaction()
{
BeginTransaction(IsolationLevel.ReadCommitted);
}

public void BeginTransaction(IsolationLevel pIsolationLevel)
{
if ((_IsTransactionOpen == true))
{
throw new Exception("Transaction already open.");
}
else
{
try {
_Connection = new SqlConnection(ConnectionString);
_Connection.Open();
_Transaction = _Connection.BeginTransaction(pIsolationLevel);
_IsTransactionOpen = true;
}
catch (Exception ex) {
if ((_Connection != null))
{
_Connection.Close();
}
if ((_Transaction != null))
{
_Transaction.Dispose();
}
_IsTransactionOpen = false;
throw;
}
}
}


public void Commit()
{
if ((_IsTransactionOpen == true))
{
try {
_Transaction.Commit();
}
finally {
if ((_Connection != null))
{
_Connection.Close();
}
if ((_Transaction != null))
{
_Transaction.Dispose();
}
_IsTransactionOpen = false;
}
}
else
{
throw new Exception("Transaction not started.");
}
}

public void RollBack()
{
if ((_IsTransactionOpen == true))
{
try {
_Transaction.Rollback();
}
finally {
if ((_Connection != null))
{
_Connection.Close();
}
if ((_Transaction != null))
{
_Transaction.Dispose();
}
_IsTransactionOpen = false;
}
}
else
{
throw new Exception("Transaction not started");
}
}
#endregion


}

 

Cool. You wrote a wrapper for SqlCommand to handle transaction.
Thank you for sharing.

 

thanks mate