shenlong_wc
asked on
SQL connection in threading
Hi all,
i'm trying to make a program with multiple threads that do some work then insert records into my SQL 2000 . should i declare SQLconnection1 as shared, or private to each thread ? what do i have to look out for when working with databases and threads ?
i'm trying to make a program with multiple threads that do some work then insert records into my SQL 2000 . should i declare SQLconnection1 as shared, or private to each thread ? what do i have to look out for when working with databases and threads ?
ASKER
is it ok if i declare it private shared to the whole class ? (this classes might be part of a thred too )
it'll be used by some subs that are multi-threaded and some which are not.
other than checking if it's open, is there anything i should be aware of ?
it'll be used by some subs that are multi-threaded and some which are not.
other than checking if it's open, is there anything i should be aware of ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
code's kinda long , kinda difficult to post it here
how about if i create only 1 SQLconnection for the entire project then pass it by ref to all the methods that need it ? or maybe even declare it as a global variable ? I will open it when i start the app and close it when i exit the app. in between, it'll be free for all the threads , objects , sub etc to use, and they won't have to worry about opening or closing it ?
is that a good idea ?
sorry, my connection pooling concepts are kinda weak .
how about if i create only 1 SQLconnection for the entire project then pass it by ref to all the methods that need it ? or maybe even declare it as a global variable ? I will open it when i start the app and close it when i exit the app. in between, it'll be free for all the threads , objects , sub etc to use, and they won't have to worry about opening or closing it ?
is that a good idea ?
sorry, my connection pooling concepts are kinda weak .
Sounds like the way to go, yes!
ASKER
thanks !
ASKER
Hi there,
the problem didn't seem to go away ...
if i shared my OleDbCommand and OleDbConnection as a global, sometimes my threads scream something about the OleDbCommand still open or fecthing some data .
if i shared only my OleDbConnection as a global, i get errors saying that it still has open datareaders on it .
what should i do ? short of creating a new OleDbCommand and OleDbConnection everywhere , that is . how can i make my OleDbCommand thread safe ?
the problem didn't seem to go away ...
if i shared my OleDbCommand and OleDbConnection as a global, sometimes my threads scream something about the OleDbCommand still open or fecthing some data .
if i shared only my OleDbConnection as a global, i get errors saying that it still has open datareaders on it .
what should i do ? short of creating a new OleDbCommand and OleDbConnection everywhere , that is . how can i make my OleDbCommand thread safe ?
I suggest you share the SQLConnection and within the thread just check its state and see if it needs opening.
Dabas