Knee deep in snow.

After my recent post  asking for the most elegant way to support multiple databases with the same schema at runtime, I received some good pointers in the SubSonic forums from dbr, a forum user. In the end, I admit should have done my homework before posting.

One elegant solution to change the SubSonic Provider/ConnectionString at runtime makes use of SharedDbConnectionScope. I personally do not like this solution, as I prefer my code to explicitly state what it’s doing via its properties or arguments instead of relying on contextual information.  I was also concerned about how it works with regards to concurrency and I did a little digging. Looking at the code, I discovered it internally uses the ThreadStatic attribute which seems like a godsend at first, but further investigation reveals the implementation may be flawed. I did see people complain that it didn’t work for them, but don’t know if it is related to the ThreadStatic attribute. I do not fully trust this technique, but I may wrong as I'm far from an expert in concurrency.

Returning to Dbr's suggestion, he simply generates different providers at runtime for each connection string. This sounds simple if you can manage to modify the ProviderName property on the collection (ActiveList) or object (ActiveRecord) every time you load/save from the database. Without resorting to SharedDbConnectionScope, you can't use the auto-generated constructors because they fall back to the default provider which is hardcoded in the generated object’s schema.

The elegant implementation to encapsulate loading/saving from the database is to use a controller, as would be suggested by the MVC design pattern. I have not yet played with the new MVC templates provided by SubSonic, but we already use a good generic SubSonicController here at LavaBlast.

I wanted to re-write my object loading/saving code using this new solution to get rid of my inelegant concurrency locks. Although obvious in appearance, I encountered a few little hiccups along the way and I thought I'd post my findings here.

Limitation 1: You can't create an object by specifying its ProviderName in an ActiveRecord constructor using the default generated code.

  • Workaround: You need to load it using a collection, which supports the ProviderName.
  • Workaround 2: Use SharedDbConnectionScope
  • Workaround 3: Change the code templates to add new constructors.

Limitation 2: You can't use a collection's Where parameter to load your data (via its primary key or other filter), because of incomplete framework code. Hopefully this will be resolved soon (see issue 13402).

  • Workaround: Copy-paste the code used by internally by the Collection, but pass in the extra ProviderName parameter to the new Query.

Limitation 3: You can't specify the ProviderName property on an ActiveRecord because the setter is marked as protected.

  • Workaround: Change the code templates and add a public method/property that sets PropertyName from within the class.
  • Use SharedDbConnectionScope.

Limitation 4: When you load an ActiveRecord by running a query or by loading a Collection, the ActiveRecord does not inherit the ProviderName from the Collection/Query. This is probably due to Limitation 3.  

My current prototype no longer uses the c# lock keyword. I create instances of a controller, passing in which connection string name to use. All database loading/saving is done via this controller, for which I have attached sample code extracts. I managed to get object loading code to my liking, but I had to resort to SharedDbConnectionScope for saving. Once the minor limitations in the framework are resolved, I will be more comfortable with the code.

In summary, I did manage to get a working prototype and I have attached the relevant portions of code that works with data from the appropriate database (chosen at runtime). Hope this helps!