Multithreading and DAL

[gecka]

Hello,

it's been a while since I last posted here. I started database programming quite recently and am now in process of writing a Database Access Layer (DAL) to support various databases, including MSSql, SQLite, Access and Firebird.

All works fine, except one thing. My DAL will be used in multithreaded desktop applications, so I started thinking if my DAL is thread-safe. Well, it is not.

Now I am trying to think of how I could make it thread-safe and I have faced a couple of questions.

I have a static DataAccessProvider class that is used to execute sql statements, stored procedures and has transaction support. It has two member fields: an IDbConnection field to store an opened connection and an IDbTransaction field used for transaction support.

Now make it thread-safe, my first question is: is it good to open and then close the connection in every static method used to execute some sql statement?

Second question: should I use locks?

I am interested how this will affect the performance of my DAL.

[trayion]

Speaking purely from a database perspective you will most of the time be a lot better off just opening the connection, doing your thing and then closing the connection again.
Of course, that rests on the premise that an open connection demands more resources from the Database then the opening and closing of the connection does (which can typically be resolved with thread pooling, to a certain degree).
And personally I'd recommend using locking, what level of locks you need is subject to the changes you're making, but you should always lock off while you're updating/altering tables or rows, simply to avoid non-repeatable read and dirty reads.

In any event, I'd recommend doing a bit of testing and calculations on the load of the server in regards to the first section, but usually you are better off with multiple connections opening and closing, then you are with one connection constantly open and doing a lot of work.