Runtime Connection Switching

Hi,Robert

   thanks a lot for you help!

   I did it what you told me to do. But I still got the errors.

   Could you chang the ConnectionString to an invalid database in the web.config,  then you add a correct ConnectionString in the Page_Init, and Change the DataSourceControl's Provider, then you'll find the errors.

  17279, 17280 for you reference!
  thanks again!

NetFan

Hey Bobby,

I was wondering if you ever figured out a new way for this situation. I think I have a similar situation described here:

http://community.codesmithtools.com/forums/p/7596/28519.aspx#28519

Thanks!

Josh 

Hello, I'm having some problems with this feature, maybe someone can help.

Here's my setup: I'm using Nettiers 2.2 and a Domain Model, with EntLib 3.1. I am just generating the data access, no web service or web interfaces.

I have a single connection string in the app.config, and in my code I can successfully change the connection string dynamically:

ConnectionScope.Current.ConnectionStringKey = "mySecondConnectionString";
ConnectionScope.Current.DynamicConnectionString = "Data Source=localhost;Initial Catalog=MyDatabase;Integrated Security=True";
MyDataAccessCall();

Problems are:

1) how do I get back to the default database (the one configured in the app.config file)? I tried setting the ConnectionStringKey to the name of the CS Key in app.config, and it doesn't work. This seems to be related to the following:

 2) how do I change to another database after having done a data access with a dynamic connection string?

 It seems that from the moment I change the connection scope and do a data access, the data source is locked. So if I do:

ConnectionScope.Current.ConnectionStringKey = "mySecondConnectionString";
ConnectionScope.Current.DynamicConnectionString = "Data Source=localhost;Initial Catalog=MyDatabase;Integrated Security=True";
MyDataAccessCall();

ConnectionScope.Current.ConnectionStringKey = "myThirdConnectionString";
ConnectionScope.Current.DynamicConnectionString = "Data Source=localhost;Initial Catalog=MyThirdDatabase;Integrated Security=True";
MyDataAccessCall();

The second "MyDataAccessCall()" still gets data from MyDatabase, and not MyThirdDatabase.

Any ideas?

Note: "MyDataAccessCall()" just includes a GetAll() call such as this:

 TList<MyNS.Domain.MyEntity> partners = MyNS.Domain.MyEntity.GetAll();

 Any help is greatly welcome. Thanks in advance.

João Pedro Martins

I've been debugging through the code, and it seems to be the "problem" is in ConnectionScope.cs (SqlClient project), in the "DataProvider" property.

The comment at the top states:

        /// To use a dynamic connection, you must set both the
        /// DynamicConnectionString and a unique ConnectionStringKey properties;

However, the code only looks for this dynamic connection string if dataProvider == null. Since I'm doing sequential accesses to different databases, dataProvider is != null, and my dynamic connection setting is ignored.

Am I correct? and is there a way around this behaviour?

On a second note: doing dynamic database access at the service or domain level requires at least two calls: setting the connection string key and then doing the data access. This approach doesn't seem to work in multithreading scenarios unless I do locks in my multithreaded app. I'm using BizTalk Server with NetTiers, and these problems keep creeping up. Ideas?

Hello

I've never had to do what your implementing... but I was curious and still trying to figure out why you would need to switch datasources at runtime. There are many better ways to go about updating a clustered enviroment...

Thanks

-Blake Niemyjski