CODEWITHSUNDEEP
c#multithreadingsql-server-2005database-connection
json
json

Reputation: 29

Multiple threaded database updated

I have a Windows service (C#) where I create multiple threads which will try to update status in my database (SQL Server 2005).

I have one SELECT initially and an UPDATE later that has to be executed. When I don't use lock to synchronize my threads, I am getting error

There is already an open DataReader associated with this Connection which must be closed first

But when I use lock (on static object) the update is very slow.

Can anyone help me out in solving this issue .

Upvotes: 0

Views: 1141

Answers (3)

4b0
4b0

Reputation: 22323

This happen when you have multiple DataReaders open concurrently on the same connection, ie you call SqlCommand.ExecuteReader but don't close the SqlDataReader returned by this method before calling it again (either on the same command or another command on the same connection).
Also try:
MultipleActiveResultSets=True in chains of connection.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

Each command object that you're using in your application should have it's own connection object, that you open immediately before executing the command, and close immediately afterwards. (Preferably, wrapped in a using statement)

Trust the connection pooling tech to deal with the actual underlying connections. This will stop multiple threads from trying to execute multiple commands on the same connection simultaneously (which is what the error you're seeing is talking about).

The only thing that should be shared between the threads is the connection string.

Upvotes: 5

Myles McDonnell
Myles McDonnell

Reputation: 13335

Ensure that your DataReader is closed before trying to use it's connection for anything else. What I would suggest is that you refactor your code so that you do not share connections between threads. If you can provide more information on exactly what you are trying to achieve i may be able to provide a useful code sample.

Upvotes: 5

Related Questions