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.