Recently I started working on a project at work that requires me to connect to more then a single database at a time (different parts of the app connecting to different databases at the same time). I figured this should be pretty straight forward, Subsonic is relatively mature and should probably support this out of the box.
It doesn’t.
After figuring out that there was no simple easy solution for this, the next logical step was to start googling for answers right? Obviously. Off I went, finding little bits and pieces here and there, never the complete solution though, I thought I had a solution and my app worked for a couple of weeks until all of a sudden one day I could no longer connect to more than a single database! FML as they say!
I happened to stumble on this stackoverflow post that sort of addressed my issue, it gave some of the pieces of the solution, but I had to piece the rest of it together myself. After finally fixing my problem I give to you, dear reader, my solution.
Note: this post assumes that you have used subsonic, are fairly well versed in using it and have had the exact same problem as me.
1. Generate you DAL as normal using whatever method you like (i write a batch file to generate mine)
2. Create a file called whatever and place it in your subsonic project with this code in it (links to snipt.org, i’m straight copying and pasting my file, so change your namespaces and all that business).
3. Create a base class that all of your data access classes will inherit from (I call my RepositoryBase.cs)
4. Have the repository classes accept a connection string as part of the constructor, and pass it on to the base class.
5. In the base class, call the static method in the file you created in step 2 like so:
SSPProvider.SetProvider(”ConnectionStringName”, “ConnectionString”);
6. All of your subsonic related code will now use whatever connection string you passed to the SetProvider method.
7. ???
8. Profit?
Not so sure about the profit thing, but the code to switch databases works like a charm.
If there is interest I can post a more complete example but this should get you going for now.
One Comment
Hi,
Can you please send me complete example for multiple db connectivity using subsonic.
My sistuation: I have four applications (almost 90% common functionaility). since e are having multiple users and we want to have separate deploymnets, we planned to separate into different DBs for ech application. Now I need only one DAl (with multiple providers or multiple DB connections) .
Thanks
Charani