OleDbConnection and MultiThreading

[Matthias Kwiedor]

Hello!

My Application is Multi-Threaded, where i have several threads which get
datas and write them to the database, or have to read from the database
to compare the datas and make updates if needed.

The Database is MS Access.

Connection String:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.mdb

I open the connection in the main routine

private OleDbConnection OleConn;
public OleDbConnection _strDatabase
{
get
{
if (OleConn == null)
{
try
{
OleConn = new OleDbConnection(strConnstring);
OleConn.Open();
}
}
return (OleConn);
}
}

Then i have a own dll where i have the write and read function included
and call it with "using" statement, the own writen dispose members don't
close the connection to the database (or did dispose do this automatic?)

Everything runs fine, but when i have 3 or more Threads running on the
same time i get sometimes the ole exception "ExecuteReader needs an open
and usable Connection. Current Connectionstatus: Open, Executing" and
the routine will get no datas back.

I included lock(this) into everything which calls up routines to read or
write to the database, only have lock(this) arround the read and write
function had no luck and produces much more errors like the above one.

I can do a workaround by opening the connection everytime i have a read
or write access. That works fine (no errors at all, even with large
amount of Threads), but i needs much more time to do all the database
stuff.

Any Help would be great




Regards




Matthias

 

[Matthias Kwiedor]

The call to Dispose will automatically close the connection. Also
there is a limitation in the current version of ADO.NET that you can
have only 1 open DataReader per connection object.

Hi Saurabh,

thanks for your answer, but i found the problem. I thought i had delete
the Post out of the newsgroup.

The dispose won't close the Connection, because it's defined at the Main
Program. The Problem was, that i did

lock(this)

which seems not to work, then i tried

lock(Conn) // Where Conn is the Connection created at Main Program

and it works fine now. Had only to lock the database access parts of the
two subroutines which do the job for me. Now i can access from
everypoint of the application to the dll-routine, where i give over Conn
in the constructor and have no more errors..

The weird point is, that at every Example about Thread Safety you see
lock(this) or Monitor.Enter(this), but that won't work here.




Thanks you



Matthias