I am trying to implement an abstract query factory in .NET. My solution uses a Factory that creates a concrete command class using reflection and caches an instance of it in a HashTable. Each concrete command class uses the Data Access Application block SQLHelper to execute queries and return a typed dataset. The Factory then caches the DataSet using the Caching Application Block implementation of an in Memory cache. To call a query then, using the factory we would basically do the following
DataSet ds = Webservice.Execute(String queryname, DataSet Parameters)
The solution works well but when I hit is hard in testing with multiple threads I am having issues. The key classes involves are basically as follows: I would post a diagram if I could
Factory (regular createable object. Static hashtable that is synchroized for storing the references to created queries
CacheManager (Singleton implementation by MS) - Used to store datasets returned from calls to concrete queries
ConcreteQueries (created by reflection. Implement interface and return dataset. Utilize static field variables for tablemappings
SQLHelper (static filldatasetmethod is the only one I use) Utilized multiple times by each concrete query to get data from whereever and buildt he dataset.
I am having synchronization issues when I multithread this guy. Basically unless I synchronize my calls to the concrete queries, I get issues with the dataadapter complaining about 'Mapping for Table .... already exists'. Somehow, the DataAdapter must be retaining mappings from previous calls. I cannot put a sync block around this or performance will get killed by serializing my calls to the database.
Can anyone tell me if the SqlHelper is thread safe? How about the DataAdapter it is using? I know ADO must be stateful in it's implementation to allow for connection pooling etc but I cannot understand how it is retaining these mappings between calls.
Thank you.
