The connection was not closed. The connection's current state is open

I am building a, multiple threaded stress testing application application where each thread execute methods on another application.  I am tracking the time it takes for each method call so when each thread completes the test it fires an event which is captured by the main application thread and saves the data to a SQL Server.  My connection string utilizes connection pooling where the max pool size is 200 and min pool size is 10.  

For the most part it works but every once in a while I receive the error below.  You can see from my code snippet that I am wrapping my connection open/close in try/catch/finally so I really don't understand why this error is occurring.

My connection string is: <add name="ConnectionString" connectionString="Data Source=TMSVR;Initial Catalog=ServicesData;Integrated Security=True;Max Pool Size=200;Min Pool Size=10"/>



System.InvalidOperationException: The connection was not closed. The connection's current state is open.
   at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
{
	conn.Open();
 
	// Attempt to save result
	recSaved = cmd.ExecuteNonQuery();
 
	logger.Info(
		String.Format("Thread {0} Test Saved", threadID));
 
	conn.Close();
}
catch (Exception exp)
{
	conn.Close();
 
	if (retryCount < 5)
	{
		logger.Warn(
			String.Format("Thread {0} Failed to persist test results. Retrying",
			threadID), exp);
 
		// Increment Retry Count
		retryCount++;
 
		goto retry;
	}
	else
	{
		logger.Error(
			String.Format("Thread {0} Failed to persist test results after {1} attempts.",
				threadID,
				retryCount), exp);
	}
 
}
finally
{
	conn.Close();
}

Open in new window

ChiliyagoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ChetOS82Commented:
Are your threads sharing a common "conn" object, or does each one create its own?  It is possible that the thread is being suspended during the call to cmd.ExecuteNonQuery(), then another thread is tring to call the same (using the same conn object).

You cannot execute a command on a connection if another command is already running.
0
mastooCommented:
Can u post earlier code where you declare and set conn?
0
ChiliyagoAuthor Commented:
yes there is only one conn object which reads from the ConnectionString section of the app.config


 
private string connStr =
ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
 
private string testResultSproc =
ConfigurationManager.AppSettings["PersistTestResultSprocName"].ToString();
 
private SqlConnection conn;
private SqlCommand cmd;
 
 
public MainForm()
{
	InitializeComponent();
 
 
	// Setup database connection.
	conn = new SqlConnection(connStr);
	cmd  = new SqlCommand(testResultSproc, conn);
	cmd.CommandType = CommandType.StoredProcedure;
 
	// Define parameters
	SqlParameter TestName = new SqlParameter("@TestName", SqlDbType.Char, 50);
	SqlParameter BatchID = new SqlParameter("@BatchID", SqlDbType.Char, 50);
	SqlParameter StartTime = new SqlParameter("@StartTime", SqlDbType.DateTime);
	SqlParameter EndTime = new SqlParameter("@EndTime", SqlDbType.DateTime);
	SqlParameter MillisecDuration = new SqlParameter("@MillisecDuration", SqlDbType.SmallInt);
	SqlParameter Passed = new SqlParameter("@Passed", SqlDbType.Bit);
 
	// Add parameters
	cmd.Parameters.Add(TestName);
	cmd.Parameters.Add(BatchID);
	cmd.Parameters.Add(StartTime);
	cmd.Parameters.Add(EndTime);
	cmd.Parameters.Add(MillisecDuration);
	cmd.Parameters.Add(Passed);
}

Open in new window

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

mastooCommented:
Yes, that would be a problem.  The connection class isn't thread safe.  In your example, you probably want to declare the connection local to the method using it and create and close it each time.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ChiliyagoAuthor Commented:
In this scenario  I am trying to spin as many threads as I can and as such I am not sure they would share the same application pool would they?  Each thread is raising an event that is captured by the main thread which is responsible for saving the data to the sql server.  I also wonder what I could do in my main thread's event handler to make it safe.
0
mastooCommented:
1. Not sure why you would think they might not be the same pool.  And as a practical matter, how many concurrent threads are we talking about - tens, hundreds, thousands?

2. Event handlers run in the context of the thread that raised the event.  Or are you referring to a synchronization event?
0
ChiliyagoAuthor Commented:
Yes hundreds and I hope thousands. :)


I am not sure what you mean by synchronization event but to clarify:

In my case each thread is a type of unit test and declares an Event which is raised when a cache containing a group of test results is full.  When that cache is full the event is raised which passes the cache as an object. The main thread subscribes to this event prior to calling start on the thread which has a method handler which saves all the test results to the database.  
0
mastooCommented:
This is going to get a little beyond the original question, but let me make some suggestions:

You might look into practical limits on the number of threads.  I'd guess thousands of threads connecting to Sql Server would entail unacceptable overhead on both the OS and Sql Server.

Try a little test.  Set a break point in your code that "subscribes to this event" and when you hit the breakpoint look at the debugger's thread window to see which thread you are in.  I suspect you won't be in the main thread which I think is what you are expecting.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.