Properly closing SqlDataReader and connection 1

[WebGuyToo]

Hello, I'm having an issue with my Connection Pool being max. The error I'm receiving is:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. All pooled connections were in use and max pool size was reached.

I did some research on google and found that I may not be closing my connections properly. Could someone please look at my code and let me know if this correct or could be done better.

Here is my code that makes the connection and populates the SqlDataReader:

---------------------------------------------------------
public SqlDataReader GetAllStatusTypes()
{
SqlConnection statConn = new SqlConnection(GetConn());

SqlCommand statCmd = new SqlCommand("GetAllStatusTypes", statConn);
statCmd.CommandType = CommandType.StoredProcedure;

statConn.Open();
SqlDataReader drStat = statCmd.ExecuteReader(CommandBehavior.CloseConnection);

return drStat;

}
-----------------------------------------------------------

Here is the method receiving the SqlDataReader:

public void LoadStatusTypes()
{
SqlDataReader drStatus;
drStatus = stat.GetAllStatusTypes();

//int index = 0;

while (drStatus.Read())
{
ListItem newListItem = new ListItem();
newListItem.Value = drStatus.GetValue(0).ToString();
newListItem.Text = drStatus.GetValue(1).ToString();
cboStatusTypes.Items.Add(newListItem);

if (drStatus["StatusTypeID"].ToString() == stat.StatusTypeID.ToString())
{
cboStatusTypes.SelectedValue = drStatus["StatusTypeID"].ToString();
}
}
drStatus.Close();
}
-----------------------------------------------------------

I cannot close the drStat in GetAllStatusTypes because then I will not be able to return it.

When I create the SqlDataReader drStatus to except the passed SqlDataReader object I close that one. Will this also close the one being passed?

Any help would be very appreciated.

Thank you,
WebGuytoo

 

[jmeckley]

when I need to access the ado objects direction (usually I let an ORM handle data access) I use the following technique.

DataTable results = new DataTable();
using(IDbConnection cnn =  DBProviderFactory.Create("name from web.config"))
using(IDbCommand cmd = cnn.CreateCommand())
{
   cnn.ConnectionString = cnnStringFromWebConfig;
   cmd.CommandText = "parameterized sql statement";
   //optional parameters
   IDbParameter paremeter = cmd.CreateParameter();
   parameter.Value = valueToPassIn;
   parameter.Id = "id";
   cmd.Parameters.Add(parameter);
   cnn.Open();
   DataTable.Load(cmd.ExecuteReader());
}
return results;

I'm sure there are syntax errors because I don't directly access the ado much these days, but it's the basic idea.

I would use a similar technique to for scalar and non-reader queries.

for testing purposes I would actually break this out into multiple objects that depend on one another. but for now we will start here.

if you were to seperate this into multiple objects
create connection
create/execute command
manage transactions
create paremeters
etc.
you can manage the ado objects using the "unit of work per view" concept. the basic idea is that each request gets 1 database connection and that connection is opened when the request begins and is disposed when the request ends. This is very popular with the ORM tool NHibernate.



Jason Meckley
Programmer
Specialty Bakers, Inc.