SqlDataReader Bug? ExecuteNonQuery() throws an Exception on new Connections. [REPOST]

  • Thread starter Thread starter Sean Wolfe
  • Start date Start date
S

Sean Wolfe

I haven't received an answer on this so I'm reposting.
I have searched through the newsgroups on an exception I am having with
SqlCommand.ExecuteNonQuery() being used asynchronously in a Thread Pool. But
every problems seems to cover the issue of using an existing connection and
using the same connection for execution on 2 or more separate threads. But
in my method, I create a new connection for each Execution.

I did read also that there was a bug in the 1.0 framework that was later
fixed with a service pack. But I'm using the 1.1 frameworks. Did this bug
make it back into the 1.1 framework?

This seems to be a bug since I am using a new connection for each reader. Or
is there a problem with the Connection pool?

Here is an example of the method that is called to execute the object.
Basically a higher up method iterates through the collection and then Calls
this method per object using the ThreadPool class. a threadCallState object
holds the state data to run the execution. This method build a parameter
list from other utility methods, creates a new connection, Executes the
query, then closes and disposes the connection. But I still get the "There
is already an open DataReader associated with this Connection which must be
closed first."

private void ExecuteCommandOnObject(object state)
{
threadCallState callState = (threadCallState)state;
SqlCommand command = callState.command;
object commandDataItem = callState.commandDataItem;
ListDictionary persistentParams = callState.persistentParams;

SqlConnection executeConnection = new
SqlConnection(this.sqlConnectionString);
//Open a database connection and Fill the table with the data.
command.Connection = executeConnection;
command.Connection.Open();

int parametersCount = command.Parameters.Count;
for(int paramIndex = 0; paramIndex < parametersCount; paramIndex++)
{
SqlParameter parameter = command.Parameters[paramIndex];
string paramName = parameter.ParameterName;

// get the property and put it into the parameter.
if(persistentParams.Contains(paramName))
parameter.Value = persistentParams[paramName];
else
{
paramName = paramName.Replace("@", "");
System.Reflection.PropertyInfo property =
this.propertyArray[this.dataMap.GetIndexOfColumnName(paramName)];
parameter.Value = property.GetValue(commandDataItem, null ); //
Will not work on indexed properties.
}
}

// Execute the query, then close the connection.
command.ExecuteNonQuery();
executeConnection.Close();
executeConnection.Dispose();
executeConnection = null;
}



Thanks,
sean
 
See my response to Sirnivas.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Sean Wolfe said:
I haven't received an answer on this so I'm reposting.
I have searched through the newsgroups on an exception I am having with
SqlCommand.ExecuteNonQuery() being used asynchronously in a Thread Pool. But
every problems seems to cover the issue of using an existing connection and
using the same connection for execution on 2 or more separate threads. But
in my method, I create a new connection for each Execution.

I did read also that there was a bug in the 1.0 framework that was later
fixed with a service pack. But I'm using the 1.1 frameworks. Did this bug
make it back into the 1.1 framework?

This seems to be a bug since I am using a new connection for each reader. Or
is there a problem with the Connection pool?

Here is an example of the method that is called to execute the object.
Basically a higher up method iterates through the collection and then Calls
this method per object using the ThreadPool class. a threadCallState object
holds the state data to run the execution. This method build a parameter
list from other utility methods, creates a new connection, Executes the
query, then closes and disposes the connection. But I still get the "There
is already an open DataReader associated with this Connection which must be
closed first."

private void ExecuteCommandOnObject(object state)
{
threadCallState callState = (threadCallState)state;
SqlCommand command = callState.command;
object commandDataItem = callState.commandDataItem;
ListDictionary persistentParams = callState.persistentParams;

SqlConnection executeConnection = new
SqlConnection(this.sqlConnectionString);
//Open a database connection and Fill the table with the data.
command.Connection = executeConnection;
command.Connection.Open();

int parametersCount = command.Parameters.Count;
for(int paramIndex = 0; paramIndex < parametersCount; paramIndex++)
{
SqlParameter parameter = command.Parameters[paramIndex];
string paramName = parameter.ParameterName;

// get the property and put it into the parameter.
if(persistentParams.Contains(paramName))
parameter.Value = persistentParams[paramName];
else
{
paramName = paramName.Replace("@", "");
System.Reflection.PropertyInfo property =
this.propertyArray[this.dataMap.GetIndexOfColumnName(paramName)];
parameter.Value = property.GetValue(commandDataItem, null ); //
Will not work on indexed properties.
}
}

// Execute the query, then close the connection.
command.ExecuteNonQuery();
executeConnection.Close();
executeConnection.Dispose();
executeConnection = null;
}



Thanks,
sean
Click to expand...
 
William (Bill) Vaughn said:
See my response to Sirnivas.
Click to expand...

So are you saying that there is an issue with Connection's timing out in the
current version of The SqlClient?

This is quite bizarre, since The issue I seem to be having is creating new
connections per WorkerThread. Synchronous execution has no issues. And each
Execution statement I have been working with have been simple insert
statements, and do not return data (other than the rows affected). So this
doesn't seem to quite seem to apply in srinivas's situation. Each new
ExecuteNonQuery thread has a new connection created (which should come from
the connection pool). Now is this an issue because there are multiple
connection attempts coming in at once? And the connectionPool treats the
batch of individual connections as one command, hence a timeout?

If this has to do with starvation on the ConnectionPool, I thought if the
Pool runs out of connections, all other connection requests block, until a
Connection in the pool is available. Is there no queuing of requests if
there are multiple Connection requests pending and blocking? And I thought
this was suppose to happen at the OpenConnection level, not the
ExecuteNonQuery.

Sean
 
I'm saying that until MARS arrives with Yukon and ADO 2.0 you can't expect a
Connection to support more than one operation at a time. If you're trying to
use the same connection from several threads, you can expect the exception
you're getting.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Sean,
I do not see anything obviously wrong with your code, you seem to be
creating a new connection and executing a command using the new
connection.

Could you add something to your code to log the connection hash code
that you are expecting to use and the one you are actually using?
Something like adding

Console.WriteLine(executeConnection.GetHashCode() ) after opening the
connection and
Console.WriteLine(command.Connection.GetHashCode() ) just before
calling ExecuteNonQuery.

The only explanation I can come up for this exception is that you are
somehow using one connection across multiple threads.

Angel
This post is provided "AS IS" and confers no rights, please don't
respond to this alias, it is for posting purposes only.


Sean Wolfe said:
I haven't received an answer on this so I'm reposting.
I have searched through the newsgroups on an exception I am having with
SqlCommand.ExecuteNonQuery() being used asynchronously in a Thread Pool. But
every problems seems to cover the issue of using an existing connection and
using the same connection for execution on 2 or more separate threads. But
in my method, I create a new connection for each Execution.

I did read also that there was a bug in the 1.0 framework that was later
fixed with a service pack. But I'm using the 1.1 frameworks. Did this bug
make it back into the 1.1 framework?

This seems to be a bug since I am using a new connection for each reader. Or
is there a problem with the Connection pool?

Here is an example of the method that is called to execute the object.
Basically a higher up method iterates through the collection and then Calls
this method per object using the ThreadPool class. a threadCallState object
holds the state data to run the execution. This method build a parameter
list from other utility methods, creates a new connection, Executes the
query, then closes and disposes the connection. But I still get the "There
is already an open DataReader associated with this Connection which must be
closed first."

private void ExecuteCommandOnObject(object state)
{
threadCallState callState = (threadCallState)state;
SqlCommand command = callState.command;
object commandDataItem = callState.commandDataItem;
ListDictionary persistentParams = callState.persistentParams;

SqlConnection executeConnection = new
SqlConnection(this.sqlConnectionString);
//Open a database connection and Fill the table with the data.
command.Connection = executeConnection;
command.Connection.Open();

int parametersCount = command.Parameters.Count;
for(int paramIndex = 0; paramIndex < parametersCount; paramIndex++)
{
SqlParameter parameter = command.Parameters[paramIndex];
string paramName = parameter.ParameterName;

// get the property and put it into the parameter.
if(persistentParams.Contains(paramName))
parameter.Value = persistentParams[paramName];
else
{
paramName = paramName.Replace("@", "");
System.Reflection.PropertyInfo property =
this.propertyArray[this.dataMap.GetIndexOfColumnName(paramName)];
parameter.Value = property.GetValue(commandDataItem, null ); //
Will not work on indexed properties.
}
}

// Execute the query, then close the connection.
command.ExecuteNonQuery();
executeConnection.Close();
executeConnection.Dispose();
executeConnection = null;
}



Thanks,
sean
Click to expand...
 
Sean,
I do not see anything obviously wrong with your code, you seem to be
creating a new connection and executing a command using the new
connection.

Could you add something to your code to log the connection hash code
that you are expecting to use and the one you are actually using?
Something like adding

Console.WriteLine(executeConnection.GetHashCode() ) after opening the
connection and
Console.WriteLine(command.Connection.GetHashCode() ) just before
calling ExecuteNonQuery.

The only explanation I can come up for this exception is that you are
somehow using one connection across multiple threads.

Angel
This post is provided "AS IS" and confers no rights, please don't
respond to this alias, it is for posting purposes only.
Click to expand...

Hi, thanks for your reply angel. The function operates from a call to
a threadPool. So each time that function is called, it is being used
on a new thread from the thread pool. And as you can see from the
function, the Connection is created and destroyed withing that single
function. Hence I would come to believe that this is a single
connection per thread.

I do beleive that the issue with the threadpool being corrupt does
come into play here, becasue after that one error, all subsequent
calls to ExecuteNonQuery are failing.

I'll have to try out getting the hashcode to the Connection. It might
help debug this issue, but I probably won't be able to get to if for
some time. I have moved on to other issues, and did a simple
workaround for this issue.

Sean
 
Back
Top