Connections not closing and display slow.

[tshad]

I am having a problem with my Web Page and closing connections as well as
running very slow after filling my dropdown objects.

If I run my page and continually hit my button that fills my GridView object
it fills it in about 1 second.

But when I run a stored procedure from my dropdown, I am finding that my
connection is staying open.

Once I open my dropdown and then hit my button again, I am finding that it
is taking about 45 seconds to display the data (I am only displaying 15
rows).

I found 2 issues.

One is that the connections seem to be staying open.

My procedure is:

SqlCommand dbCommand;

dbCommand = new SqlCommand("GetClientName",
new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnectString"].ConnectionString));
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Connection.Open();
ddlClient.DataSource =
dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
ddlClient.DataTextField = "ClientName";
ddlClient.DataValueField = "ClientId";
ddlClient.DataBind();
ddlClient.Items.Insert(0, " --All--");

Wouldn't the CommandBehavior.CloseConnection close the connect when it fills
the dropdown?

I look at sp_who2 and keep seeing that connection is still open.

I thought this might be causing my problem as I was seeing about 10
connection opens due by dropdowns.

But after tracing the page, I found that it is going slow AFTER the
PreRender event is run.

At that point, the Ajax progress image stops spinning and it takes about 45
seconds to finish.

There are about 9 dropdowns on the page - but most of them are not filled
and the ones that are are only filled with 5-15 items.

And the grid isn't very large so why would it take so long to Render?

Could Ajax be causing the problem????

Thanks,

Tom

 

[tshad]

I still have questions on the connections.

On the slowness, the problem appears to Ajax. If I take out the Ajax code,
the page works fast no matter how many times I make selections from the
dropdowns.

The ajax seems to work fine until I start selecting from the dropdown.

I can't put Ajax in until I figure out why it is having a problem.

Thanks,

Tom

 

[Scott M.]

I am having a problem with my Web Page and closing connections as well as
running very slow after filling my dropdown objects.

If I run my page and continually hit my button that fills my GridView
object it fills it in about 1 second.

But when I run a stored procedure from my dropdown, I am finding that my
connection is staying open.

Once I open my dropdown and then hit my button again, I am finding that it
is taking about 45 seconds to display the data (I am only displaying 15
rows).

I found 2 issues.

One is that the connections seem to be staying open.

My procedure is:

SqlCommand dbCommand;

dbCommand = new SqlCommand("GetClientName",
new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnectString"].ConnectionString));
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Connection.Open();
ddlClient.DataSource =
dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
ddlClient.DataTextField = "ClientName";
ddlClient.DataValueField = "ClientId";
ddlClient.DataBind();
ddlClient.Items.Insert(0, " --All--");

Wouldn't the CommandBehavior.CloseConnection close the connect when it
fills the dropdown?

No. You must close your DataReader (which you can't do with your code
because you are not explicitly storing a reference to it) and then that will
cause the connection to close. Also, if you wish to bind a dropdown list to
the results of a DataReader, you'll either have to iterate over all the rows
retreived by the reader or populate a DataTable with the reader's rows and
bind to that. The second option is easier.

Your code should be written like this for better flexibility:

using( SqlConnection con = new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnectString"].ConnectionString))
{
SqlCommand dbCommand = new SqlCommand("GetClientName", con);
dbCommand.CommandType = CommandType.StoredProcedure;

try
{
dbCommand.Connection.Open();
SqlDataReader dr =
dbCommand.ExecuteReader(CommandBehavior.CloseConnection);

//Populate a DataTable with the results that the DataReader makes
available.
datatable dt=new datatable();
dt.load(dr);

// Closes the reader and the connection because of the way the
reader was defined.
dr.Close();
ddlClient.DataSource = dt;
ddlClient.DataTextField = "ClientName";
ddlClient.DataValueField = "ClientId";
ddlClient.DataBind();
ddlClient.Items.Insert(0, " --All--");
}
catch (Exception ex)
{

}
}


-Scott

 

[tshad]

I am having a problem with my Web Page and closing connections as well as
running very slow after filling my dropdown objects.

If I run my page and continually hit my button that fills my GridView
object it fills it in about 1 second.

But when I run a stored procedure from my dropdown, I am finding that my
connection is staying open.

Once I open my dropdown and then hit my button again, I am finding that
it is taking about 45 seconds to display the data (I am only displaying
15 rows).

I found 2 issues.

One is that the connections seem to be staying open.

My procedure is:

SqlCommand dbCommand;

dbCommand = new SqlCommand("GetClientName",
new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnectString"].ConnectionString));
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Connection.Open();
ddlClient.DataSource =
dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
ddlClient.DataTextField = "ClientName";
ddlClient.DataValueField = "ClientId";
ddlClient.DataBind();
ddlClient.Items.Insert(0, " --All--");

Wouldn't the CommandBehavior.CloseConnection close the connect when it
fills the dropdown?

No. You must close your DataReader (which you can't do with your code
because you are not explicitly storing a reference to it) and then that
will cause the connection to close. Also, if you wish to bind a dropdown
list to the results of a DataReader, you'll either have to iterate over
all the rows retreived by the reader or populate a DataTable with the
reader's rows and bind to that. The second option is easier.

I have always been told that if you bind directly to a dropdown or datagrid
that the connection would be closed:

This from others I found when googling:

"Actually, it closes the connection after the ExecuteReader method has
returned all of the records (in ReturnResults method). This behavior occurs
because you are passing the CommandBehavior.CloseConnection enum to the
ExecuteReader method."

On MSDN (http://msdn.microsoft.com/en-us/library/aa175863(SQL.80).aspx):
"Orphaned connections and overflowing pools are serious problems, and
judging by the number of newsgroup discussions about them, they're fairly
common. The most likely culprit is the DataReader. To test the behavior of
the DataReader, I wrote a sample Windows Forms (WinForms) application
concentrating on the CommandBehavior.CloseConnection option. (You can
download this application by entering InstantDoc ID 39031 at
http://www.sqlmag.com.) You can set this option when you use the SqlCommand
object's ExecuteReader method to execute the query and return a DataReader.
My test application shows that even when you use this option, if you don't
explicitly close the DataReader (or SqlConnection), the pool overflows. The
application then throws an exception when the code requests more connections
than the pool will hold.
Some developers insist that if you set the CommandBehavior.CloseConnection
option, the DataReader and its associated connection close automatically
when the DataReader finishes reading the data. Those developers are
partially right—but the option works this way only when you're using a
complex bound control in an ASP.NET Web application. Looping through a
DataReader result set to the end of its rowset (that is, when Dr.Read—the
DataReader's Read method—returns false) isn't enough to trigger automatic
connection closing.

However, if you bind to a complex bound control such as the DataGrid, the
control closes the DataReader and the connection—but only if you've set the
CommandBehavior.CloseConnection option."

As far as I know, this would include the DropDownList.

This is what I have always understood.

Your example below wouldn't need the CommandBehavior.CloseConnect, because
the connection would be closed because of the Using statement.

On MSDN
(http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx)
the example they are using here shows it being bound to a DataRead (not a
DropDownList) and you can see that it is not using the
CommandBehavior.CloseConnection but it does close the reader.

private static void ReadOrderData(string connectionString)
{
string queryString =
"SELECT OrderID, CustomerID FROM dbrders;";

using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command =
new SqlCommand(queryString, connection);
connection.Open();

SqlDataReader reader = command.ExecuteReader();

// Call Read before accessing data.
while (reader.Read())
{
Console.WriteLine(String.Format("{0}, {1}",
reader[0], reader[1]));
}

// Call Close when done reading.
reader.Close();
}
}

As far as I know, if you use the CommandBehavior.CloseConnection, and you
bind to a DataReader, you must close the reader and when the reader closes
the connection will be closed.

If you bind directly to a complex control (datagrid or dropdownlist), it
will close the dataReader when done reading all the records and if you
include CommandBehavior.CloseConnection the connection will be closed.

This is why if you bind directly to a control you can't use multiple return
sets because the connection will be closed when it finishes reading the 1st
set of data to the dropdownlist (if you include the CloseConnection
parameter).

Maybe I'm wrong, but this is what I have understood as well as others.

Thanks,

Tom

Your code should be written like this for better flexibility:

using( SqlConnection con = new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnectString"].ConnectionString))
{
SqlCommand dbCommand = new SqlCommand("GetClientName", con);
dbCommand.CommandType = CommandType.StoredProcedure;

try
{
dbCommand.Connection.Open();
SqlDataReader dr =
dbCommand.ExecuteReader(CommandBehavior.CloseConnection);

//Populate a DataTable with the results that the DataReader makes
available.
datatable dt=new datatable();
dt.load(dr);

// Closes the reader and the connection because of the way the
reader was defined.
dr.Close();
ddlClient.DataSource = dt;
ddlClient.DataTextField = "ClientName";
ddlClient.DataValueField = "ClientId";
ddlClient.DataBind();
ddlClient.Items.Insert(0, " --All--");
}
catch (Exception ex)
{

}
}


-Scott

I look at sp_who2 and keep seeing that connection is still open.

I thought this might be causing my problem as I was seeing about 10
connection opens due by dropdowns.

But after tracing the page, I found that it is going slow AFTER the
PreRender event is run.

At that point, the Ajax progress image stops spinning and it takes about
45 seconds to finish.

There are about 9 dropdowns on the page - but most of them are not filled
and the ones that are are only filled with 5-15 items.

And the grid isn't very large so why would it take so long to Render?

Could Ajax be causing the problem????

Thanks,

Tom

 

[Scott M.]

The code I've show is written to ensure that the connection closes (hence
the "using" block). If your stored proc. threw an error and your
ExectueReader didn't function properly, your connection would remain open.

The CommanBehavior.CloseConnection argument means that the connection will
be closed when the DataReader is closed. *If* the DataReader doesn't get
closed, neither will the connection. Putting all your eggs in that basket
isn't a good idea. As I said, if your CommandText fails for some reason,
your connection will stay open.

Simply adding a call to close the DataReader is a simple (and wise) piece of
code to add to ensure that the connection closes AND putting the connection
in a "using" on top of that is also a wise practice. It doesn't hurt for
the compiler to try to close a closed connection - - no execption will occur
(unlike in classic ADO).

But the bottom line is, did you try the re-worked code? Does it help?

-Scott

I am having a problem with my Web Page and closing connections as well as
running very slow after filling my dropdown objects.

If I run my page and continually hit my button that fills my GridView
object it fills it in about 1 second.

But when I run a stored procedure from my dropdown, I am finding that my
connection is staying open.

Once I open my dropdown and then hit my button again, I am finding that
it is taking about 45 seconds to display the data (I am only displaying
15 rows).

I found 2 issues.

One is that the connections seem to be staying open.

My procedure is:

SqlCommand dbCommand;

dbCommand = new SqlCommand("GetClientName",
new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnectString"].ConnectionString));
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Connection.Open();
ddlClient.DataSource =
dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
ddlClient.DataTextField = "ClientName";
ddlClient.DataValueField = "ClientId";
ddlClient.DataBind();
ddlClient.Items.Insert(0, " --All--");

Wouldn't the CommandBehavior.CloseConnection close the connect when it
fills the dropdown?

No. You must close your DataReader (which you can't do with your code
because you are not explicitly storing a reference to it) and then that
will cause the connection to close. Also, if you wish to bind a dropdown
list to the results of a DataReader, you'll either have to iterate over
all the rows retreived by the reader or populate a DataTable with the
reader's rows and bind to that. The second option is easier.

I have always been told that if you bind directly to a dropdown or
datagrid that the connection would be closed:

This from others I found when googling:

"Actually, it closes the connection after the ExecuteReader method has
returned all of the records (in ReturnResults method). This behavior
occurs
because you are passing the CommandBehavior.CloseConnection enum to the
ExecuteReader method."

On MSDN (http://msdn.microsoft.com/en-us/library/aa175863(SQL.80).aspx):
"Orphaned connections and overflowing pools are serious problems, and
judging by the number of newsgroup discussions about them, they're fairly
common. The most likely culprit is the DataReader. To test the behavior of
the DataReader, I wrote a sample Windows Forms (WinForms) application
concentrating on the CommandBehavior.CloseConnection option. (You can
download this application by entering InstantDoc ID 39031 at
http://www.sqlmag.com.) You can set this option when you use the
SqlCommand object's ExecuteReader method to execute the query and return a
DataReader. My test application shows that even when you use this option,
if you don't explicitly close the DataReader (or SqlConnection), the pool
overflows. The application then throws an exception when the code requests
more connections than the pool will hold.
Some developers insist that if you set the CommandBehavior.CloseConnection
option, the DataReader and its associated connection close automatically
when the DataReader finishes reading the data. Those developers are
partially right-but the option works this way only when you're using a
complex bound control in an ASP.NET Web application. Looping through a
DataReader result set to the end of its rowset (that is, when Dr.Read-the
DataReader's Read method-returns false) isn't enough to trigger automatic
connection closing.

However, if you bind to a complex bound control such as the DataGrid, the
control closes the DataReader and the connection-but only if you've set
the CommandBehavior.CloseConnection option."

As far as I know, this would include the DropDownList.

This is what I have always understood.

Your example below wouldn't need the CommandBehavior.CloseConnect, because
the connection would be closed because of the Using statement.

On MSDN
(http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx)
the example they are using here shows it being bound to a DataRead (not a
DropDownList) and you can see that it is not using the
CommandBehavior.CloseConnection but it does close the reader.

private static void ReadOrderData(string connectionString)
{
string queryString =
"SELECT OrderID, CustomerID FROM dbrders;";

using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command =
new SqlCommand(queryString, connection);
connection.Open();

SqlDataReader reader = command.ExecuteReader();

// Call Read before accessing data.
while (reader.Read())
{
Console.WriteLine(String.Format("{0}, {1}",
reader[0], reader[1]));
}

// Call Close when done reading.
reader.Close();
}
}

As far as I know, if you use the CommandBehavior.CloseConnection, and you
bind to a DataReader, you must close the reader and when the reader closes
the connection will be closed.

If you bind directly to a complex control (datagrid or dropdownlist), it
will close the dataReader when done reading all the records and if you
include CommandBehavior.CloseConnection the connection will be closed.

This is why if you bind directly to a control you can't use multiple
return sets because the connection will be closed when it finishes reading
the 1st set of data to the dropdownlist (if you include the
CloseConnection parameter).

Maybe I'm wrong, but this is what I have understood as well as others.

Thanks,

Tom

Your code should be written like this for better flexibility:

using( SqlConnection con = new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnectString"].ConnectionString))
{
SqlCommand dbCommand = new SqlCommand("GetClientName", con);
dbCommand.CommandType = CommandType.StoredProcedure;

try
{
dbCommand.Connection.Open();
SqlDataReader dr =
dbCommand.ExecuteReader(CommandBehavior.CloseConnection);

//Populate a DataTable with the results that the DataReader makes
available.
datatable dt=new datatable();
dt.load(dr);

// Closes the reader and the connection because of the way the
reader was defined.
dr.Close();
ddlClient.DataSource = dt;
ddlClient.DataTextField = "ClientName";
ddlClient.DataValueField = "ClientId";
ddlClient.DataBind();
ddlClient.Items.Insert(0, " --All--");
}
catch (Exception ex)
{

}
}


-Scott

I look at sp_who2 and keep seeing that connection is still open.

I thought this might be causing my problem as I was seeing about 10
connection opens due by dropdowns.

But after tracing the page, I found that it is going slow AFTER the
PreRender event is run.

At that point, the Ajax progress image stops spinning and it takes about
45 seconds to finish.

There are about 9 dropdowns on the page - but most of them are not
filled and the ones that are are only filled with 5-15 items.

And the grid isn't very large so why would it take so long to Render?

Could Ajax be causing the problem????

Thanks,

Tom