Hello,

We have a requirement to provide a third-party company with an assembly dll. For some reasons, that DLL must be thread-safe.

To be honest I'm not sure how to use the dataccessadapter properly in a multi threaded environment. In my winform application, I create a new instance of dataccessadapter with keepOpen set to true everytime I need to access the DB.

Doing that I wonder if there's a risk for two different threads to use the same connection and mess up transactions. Also I don't know if the max pool size could be easily reached in case of heavy load.

Any idea?

stefcl wrote:

Hello,

We have a requirement to provide a third-party company with an assembly dll. For some reasons, that DLL must be thread-safe.

To be honest I'm not sure how to use the dataccessadapter properly in a multi threaded environment. In my winform application, I create a new instance of dataccessadapter with keepOpen set to true everytime I need to access the DB.

Doing that I wonder if there's a risk for two different threads to use the same connection and mess up transactions. Also I don't know if the max pool size could be easily reached in case of heavy load.

Any idea?

In my project I use a different DataAccessAdapter for each thread. I have a "DataAdapterFactory" class with a "GetThreadDataAdapter" method that return the DataAccesAdapter for the current thread. I identify each thread using their hascode (System.Threading.Thread.CurrentThread.GetHashCode function). Internally, I have a static hashtable that associate each thread's Hascode with it's DataAccessAdapter.

The first time that a new thread call DataAdapterFactory.GetThreadDataAdapter i see that the thread's hascode isn't present in the hashtable, so I create a new dataAccessAdapter for this new thread.

Naturally, upon closing of the application, I loop my hashtable to close and dispose any existing DataAccessAdapter.

Thanks but... In my case, I don't know how they are going to use my code, in case they create a new thread for each call and don't recycle old threads it won't work. I also don't know if it's safe to store the reference of a thread created by them, that could prevent it from being properly released.

stefcl wrote:

We have a requirement to provide a third-party company with an assembly dll. For some reasons, that DLL must be thread-safe.

To be honest I'm not sure how to use the dataccessadapter properly in a multi threaded environment. In my winform application, I create a new instance of dataccessadapter with keepOpen set to true everytime I need to access the DB.

First of all why do you keep the connection open. You should only do that if you have multiplt database intercations going consecutively without any user intervention. But rather than that you should always close the connection once used.

A DataAccessAdapter instance shouldn't be shared across threads, as it's not thread safe. Is there a good reason why you have to expose the DataAccessAdapter from your dll?

Hello Walaa,

I use a singleton class which returns new instances of DataAccessAdapter .

public class Config
{
    ....


    public DataAccessAdapter GetAdapter()
   {
          return new DataAccessAdapter( keepOpen = true);
   } 
}

Then, when a database query / transaction is required, I use such code ...

using (var adapter = Config.Instance.GetAdapter() )
{
      ...execute queries here
}

Seems to work fine.... The adapter object isn't stored for later use, the method which requests it is also responsible of its disposal. Same goes for transactions, if a method starts a transaction, then only that method can decide whether it should call commit or rollback.

Is there a good reason why you have to expose the DataAccessAdapter from your dll?

DataAccessAdapter won't be exposed directly, we only expose a set of high-level methods, which are pretty stateless. Most of these methods will perform database tasks.

DataAccessAdapter won't be exposed directly, we only expose a set of high-level methods, which are pretty stateless. Most of these methods will perform database tasks

Then I think you are in a safe position.

Max wrote:

In my project I use a different DataAccessAdapter for each thread. I have a "DataAdapterFactory" class with a "GetThreadDataAdapter" method that return the DataAccesAdapter for the current thread. I identify each thread using their hascode (System.Threading.Thread.CurrentThread.GetHashCode function). Internally, I have a static hashtable that associate each thread's Hascode with it's DataAccessAdapter.

The first time that a new thread call DataAdapterFactory.GetThreadDataAdapter i see that the thread's hascode isn't present in the hashtable, so I create a new dataAccessAdapter for this new thread.

Naturally, upon closing of the application, I loop my hashtable to close and dispose any existing DataAccessAdapter.

Why do you bother at all to cache them? Just create an adapter right before you need it and dispose it right after you've done using it. If you cache it than you also induce a load on garbage collector because your object becomes a long living one.

stefcl wrote:

Hello Walaa,

I use a singleton class which returns new instances of DataAccessAdapter .

public class Config
{
    ....


    public DataAccessAdapter GetAdapter()
   {
          return new DataAccessAdapter( keepOpen = true);
   } 
}

DataAccessAdapter won't be exposed directly, we only expose a set of high-level methods, which are pretty stateless. Most of these methods will perform database tasks.

I guess you should declare both Config class and GetAdapter methods as internal as a precaution. At least the method...

mihies wrote:

Max wrote:

In my project I use a different DataAccessAdapter for each thread. I have a "DataAdapterFactory" class with a "GetThreadDataAdapter" method that return the DataAccesAdapter for the current thread. I identify each thread using their hascode (System.Threading.Thread.CurrentThread.GetHashCode function). Internally, I have a static hashtable that associate each thread's Hascode with it's DataAccessAdapter.

The first time that a new thread call DataAdapterFactory.GetThreadDataAdapter i see that the thread's hascode isn't present in the hashtable, so I create a new dataAccessAdapter for this new thread.

Naturally, upon closing of the application, I loop my hashtable to close and dispose any existing DataAccessAdapter.

Why do you bother at all to cache them? Just create an adapter right before you need it and dispose it right after you've done using it. If you cache it than you also induce a load on garbage collector because your object becomes a long living one.

Mmmh... that's a good question (thanks for your interest )

The DataAdapterFactory class is born because I needed a way to being able to get a valid dataadapter anywhere in my application, without bothering where I am. This means also being able to get a dataadapter even in the LLBLGen DBGenerig project.

So I created an interface, implemented by the DataAdapterFactory, to make the reference works.

My application uses 2 different DB, and these DBs can be SQL Server or MS-Access. I dont want to bother with connection string, db type, db user/password... so I have incorporated all the needed logic in my Dataadapter factory.

I wanted to have a single place where dataadapter are created. I also use a modified version of dataadapter, it's a Read-Only dataadapter that will throw an exception if you try to do anything that change the data in the DB. This is very useful in the validation procedure, where I need to access the DB, but I'm not authorized to change anything.

In my project there are various methods that can do various DB-related thing, I don't know when and where these method will be called; but these method I need anyway to be able to get a valid dataadapter. Of course, at least I know what the current thread is doing... so creating a dataadapter for each thread IMHO do make sense.

The cache is because... way create and dispose 10'000 dataadapter, when I can only create 10 or 20? And the cache give me a way to hold the dataadapter during long operation involving transaction controlled at the begin/end of the thread. Inside a particular thread I can use the "CurrentThreadDataAdapter" without bothering about transaction, about passing the dataadapter like a parameter, about creating/disposing dataadapter.

I believe that a cache is the fastest/efficient way to get a valid dataadapter, without bothering with a lot of thing

Of course I could be totally wrong...