SQL Server provider - multiple connection strings?

[mvording]

Hello:

I would like to use the framework with SQL Server.

In the current apps I work with depending on which customer the web user logs in as, the database connection will be different, for the same website. Currently the connectionstrings are loaded into a dictionary keyed by customer.

Is there an easy way to extend the Sprint.Net data provider for sql server 2005 to handle lookup of a connection string based on some key, instead of from a config entry? Or is it a better approach to write this in another class that somehow tells the provider which connection to use?

Thank you for your patience, I haven't dug deep into the framework details yet!

Sincerely,
Matt

[Erich Eichinger]

Hi,

I don't think this is possible out of the box. A (possible and untested!) solution coming to my mind right now might look like this:

1) Define a couple of <db:provider> objects that meet your needs.

<db:provider id="db1_provider">..
<db:provider id="db2_provider">..


2) Implement the IFactoryObject that takes a list of providers to choose from:

Configuration:

<object id="myMultiProvider" type="MyMultiProvider">
<property name="ProviderTable">
<dictionary>
<entry key="UserName1" value-ref="db1_provider" />
<entry key="UserName2" value-ref="db2_provider" />
</dictionary>
</property>
</object>


IFactoryObject.GetObject() implementation:

private Hashtable _providerTable;

object GetObject()
{
string userName = HttpContext.Current.User.Identity.Name;
IDbProvider provider = _providerTable[userName];
return provider;
}


And finally inject 'myMultiProvider' into your consumer objects instead of a concrete provider.

Of course this is only a rought sketch but you get the idea.


hope this helps,
Erich

[Mark Pollack]

Hi,

This came up once before and I came up with MultiDelegatingDbProvider which is located in the data integration test directory in the distribution. What you do is set a variable in thread local storage to indicate which of the multiple dbProviders you want (each with different connection strings or databases) before performing you data access operations. Since MultiDelegatingDbProvider implements IDbProvider this can be easily swapped in/out, but otherwise is quite similar to what Erich suggested. I've attached it for easy access.

How about we add this to the distribution?

Cheers,
Mark