C
Craig Lister
Hi guys,
I have created a very simple generic database class, which all my data
accessors make use of. I'm worried though, that I am leaving lost of
open connections, as my web service is becoming unavailble sometimes.
This is my database class:
public Database()
{
_conn = new SqlConnection(_myConString);
}
public SqlDataReader ExecuteStoredProcedure(SqlCommand cmd)
{
if (_conn.State == ConnectionState.Closed)
_conn.Open();
cmd.Connection = _conn;
try
{
SqlDataReader dr = cmd.ExecuteReader();
if (dr != null)
if (dr.HasRows)
{
return dr;
}
}
catch (Exception e)
{
LoggingService.WriteDebugLog("DB Error: " +
e.Message);
LoggingService.WriteDebugLog("Procedure call was: " +
cmd.CommandText);
foreach (SqlParameter sqlParameter in cmd.Parameters)
{
LoggingService.WriteDebugLog(string.Format(" -
Parameter name = [{0}], value = [{1}]", sqlParameter.ParameterName,
sqlParameter.Value));
}
throw;
}
return null;
}
public void Dispose()
{
}
I then use the class in all of my data accessors like this:
public static UserObject Get(int userId)
{
// Create the Database object, using default database (defined in
config file).
Database db = new Database();
const string sqlCommand = "up_user_SelectBy_PK_user_id";
SqlCommand cmd = new SqlCommand(sqlCommand);
cmd.CommandType = CommandType.StoredProcedure;
// Add parameters to the parameter cache.
cmd.Parameters.Add(new SqlParameter("@user_id", userId));
UserObject userObject = null;
using (IDataReader dataReader = db.ExecuteStoredProcedure(cmd))
{
if (dataReader.Read())
{
userObject = ReadResult(dataReader);
}
}
return userObject;
}
Does this seem OK?
I have created a very simple generic database class, which all my data
accessors make use of. I'm worried though, that I am leaving lost of
open connections, as my web service is becoming unavailble sometimes.
This is my database class:
public Database()
{
_conn = new SqlConnection(_myConString);
}
public SqlDataReader ExecuteStoredProcedure(SqlCommand cmd)
{
if (_conn.State == ConnectionState.Closed)
_conn.Open();
cmd.Connection = _conn;
try
{
SqlDataReader dr = cmd.ExecuteReader();
if (dr != null)
if (dr.HasRows)
{
return dr;
}
}
catch (Exception e)
{
LoggingService.WriteDebugLog("DB Error: " +
e.Message);
LoggingService.WriteDebugLog("Procedure call was: " +
cmd.CommandText);
foreach (SqlParameter sqlParameter in cmd.Parameters)
{
LoggingService.WriteDebugLog(string.Format(" -
Parameter name = [{0}], value = [{1}]", sqlParameter.ParameterName,
sqlParameter.Value));
}
throw;
}
return null;
}
public void Dispose()
{
}
I then use the class in all of my data accessors like this:
public static UserObject Get(int userId)
{
// Create the Database object, using default database (defined in
config file).
Database db = new Database();
const string sqlCommand = "up_user_SelectBy_PK_user_id";
SqlCommand cmd = new SqlCommand(sqlCommand);
cmd.CommandType = CommandType.StoredProcedure;
// Add parameters to the parameter cache.
cmd.Parameters.Add(new SqlParameter("@user_id", userId));
UserObject userObject = null;
using (IDataReader dataReader = db.ExecuteStoredProcedure(cmd))
{
if (dataReader.Read())
{
userObject = ReadResult(dataReader);
}
}
return userObject;
}
Does this seem OK?
