locking OLEDB vs. ADODB

[Eric]

I'm developing an application in C#. The application consist of a data layer
which must be capable of using mysql, mssql and access for data storage.
I started using OleDb with interfaces like IdbConnection to achive this.
Everything worked fine until i came at the issue of locking. I found out
that
Access only supports IsolationLevel.ReadCommited which only avoids
dirty reads. I need more advanced locking for my application.

I then tried the same with the classic ADODB (com interface) and found out
that
i have far more locking options working (on Access). Why can't i use let's
say
Isolationlevel.Serializable with OleDb but can use it with ADODB? I can make
use of ADODB for all supported databases but i prefer to use the Native .NET
drivers
(SqlConnection & MySqlConnection) since there have a far better performance.

Thanks in advance for any advice!

 

[Pavel KOHOUT]

Hi Eric,
I have one idea you probably dont want to hear but from my poit of view is
best aproach
to buld you own class which handle the locking problem then you can use any
database engine
(the info about the locks you can handle in separate columns in your
datasets or directly in your databases).

 

[Eric]

I was indeed thinking about adding an extra column named locked which is set
to true or false.
This seems to be the best solution i can think of...

 

[Pavel KOHOUT]

yes but not only T/F column you will need column containg date of lock and
some independent "service" which will with eg. 5mins delay celar the crashed
lock ...

 

[John Kraft]

I was indeed thinking about adding an extra column named locked which is set
to true or false.
This seems to be the best solution i can think of...

I don't know how well it works since I'm only working with experimenting
now, but has anyone tried or thought of trying the mutex class for this?

John Kraft
Illinois State University