Data access class design question

[Wes Brown]

Hello there everyone,

In my current project I have created a Data Access class to encapsulate
all the calls to the DB. Right now I have decided to use static functions
along with private static connection string and SqlConnection variables.
The class looks like this:

public class DataAccessBase : IDisposable
{
private static SqlConnection conn;
private static string connectionString;

private DataAccessBase() { }

public void Dispose()
{
Dispose(true);
}

public void Dispose(bool disposing)
{
if (disposing)
{
if (conn != null)
{
conn.Dispose();

GC.SuppressFinalize(this);
}
}
}

private static SqlConnection Connection
{
get
{
if (conn == null)
{
conn = new SqlConnection(ConnectionString);
}

return conn;
}
}

private static string ConnectionString
{
get
{
if (connectionString == null)
{
connectionString =
ConfigurationSettings.AppSettings["ConnectionString"];
}

return connectionString;
}
}

public static SqlCommand CreateCommand(string storedProcedure)
{
SqlCommand cmd = new SqlCommand(storedProcedure, Connection);
cmd.CommandType = CommandType.StoredProcedure;

return cmd;
}

public static DataTable CreateDataTable(SqlCommand cmd)
{
lock (Connection)
{
try
{
Connection.Open();

using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
{
DataTable tbl = new DataTable();

adp.Fill(tbl);

return tbl;
}
}
finally
{
Connection.Close();
}
}
}
}

Is it a "Bad Thing (TM)" to not dispose of my SqlCommand object after each
use? Is closing a connection good enough or am I still bypassing the
built-in connection-pooling by doing that?

Thank you for taking a look at this; any advice would be greatly
appreciated.

Wes Brown
(e-mail address removed)

 

[William Ryan eMVP]

Wes:

By Closing the connection (calling Connection.Close()) you are returning it
to the pool. As far as disposing goes, that's more of an issue of how you
use the class but from this example, it's ok to do. If you had a connection
object that was instantiated in the constructor and multiple methods which
use it, then you Wouldn't necessarily want to dispose of it although as a
rule, you want to close it as soon as you don't need it anymore.

HTH,

Bill

 

[Wes Brown]

Thanks for the help Bill!