?
Solved

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

Posted on 2008-11-13
8
Medium Priority
?
3,928 Views
Last Modified: 2013-12-17
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

0
Comment
Question by:Chiliyago
  • 4
  • 3
8 Comments
 
LVL 18

Expert Comment

by:ChetOS82
ID: 22953345
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
 
LVL 21

Expert Comment

by:mastoo
ID: 22953427
Can u post earlier code where you declare and set conn?
0
 

Author Comment

by:Chiliyago
ID: 22953767
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 21

Accepted Solution

by:
mastoo earned 1500 total points
ID: 22954359
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
 

Author Comment

by:Chiliyago
ID: 22954718
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
 
LVL 21

Expert Comment

by:mastoo
ID: 22954987
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
 

Author Comment

by:Chiliyago
ID: 22955394
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
 
LVL 21

Expert Comment

by:mastoo
ID: 22955657
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question