Connection strings and .netTiers

I looked at the forums and nobody has been able to fully answer the issue regarding being able to dynamic change the value of a connection string at runtime using the netTiers2.cst for .NET 2.0.  I have about 50 clients who each have their own database.  When a user logs on and accesses the system and goes after any tables generated by CODESMITH and netTiers2 I need to change the SQL catalog in the connection string that represents their (client) specific tables. We have used netTiers2 to generate batches of tables each with their netTierService.  Inside each service we have a single client connection.  It is this client connection that we need to change dynamically.

 If someone could point out how to do this from the provider/datarepository generated code, I will be able to make the changes to the code template. Here are the pertinent sections of my web config, which I have currently working for two batches of tables that I mentioned above.

 <configSections>
 <section name="netTiersService" type="Ips.Orchestrata.DataObjects.Activity.DataAccessLayer.Bases.NetTiersServiceSection, Ips.Orchestrata.DataObjects.Activity.DataAccessLayer"/>
 <section name="netTiersService1" type="Ips.Orchestrata.DataObjects.Position.DataAccessLayer.Bases.NetTiersServiceSection, Ips.Orchestrata.DataObjects.Position.DataAccessLayer"/>
</configSections>

<connectionStrings>
 <add name="IPS_Client" providerName="System.Data.SqlClient" connectionString="Data Source=PROTON\ORCHESTRATADEV;Integrated Security=True;Initial Catalog=ipsClient"/>
</connectionStrings>

<!-- neTiers Service providers for DataObjects-->
<netTiersService defaultProvider="SqlClientActivityProvider">
 <providers>
  <add name="SqlClientActivityProvider" type="Ips.Orchestrata.DataObjects.Activity.DataAccessLayer.SqlClient.SqlNetTiersProvider,Ips.Orchestrata.DataObjects.Activity.DataAccessLayer.SqlClient" useStoredProcedure="true" connectionStringName="IPS_Client" applicationName="OrchestrataWeb" providerInvariantName="System.Data.SqlClient"/>
  <add name="SqlNetTiersProvider" type="Ips.Orchestrata.DataObjects.Activity.DataAccessLayer.SqlClient.SqlNetTiersProvider,Ips.Orchestrata.DataObjects.Activity.DataAccessLayer.SqlClient" useStoredProcedure="true" connectionStringName="IPS_Client" applicationName="OrchestrataWeb" providerInvariantName="System.Data.SqlClient"/>
 </providers>
</netTiersService>
<netTiersService1 defaultProvider="SqlClientPositionProvider">
 <providers>
  <add name="SqlClientPositionProvider" type="Ips.Orchestrata.DataObjects.Position.DataAccessLayer.SqlClient.SqlNetTiersProvider,Ips.Orchestrata.DataObjects.Position.DataAccessLayer.SqlClient" useStoredProcedure="true" connectionStringName="IPS_Client" applicationName="OrchestrataWeb" providerInvariantName="System.Data.SqlClient"/>
 </providers>
</netTiersService1>

Any help would greatly be appreciated.

While it's definately possible to do, the current implementation initializes the providers, and does not allow a way to refresh them, they are all readonly. 

Temporary Workaround:
You can create instances of the EntityProviders you are needing at runtime.
So you would say something like
SqlMyEntityProvider provider = new SqlMyEntityProvider(connectionString, false,null);
TList<MyEntity> list = provider.GetAll();

We are planning a high priority fix on this.  While completely obvious that this functionality needs to be in place.

The implmentation would be something like this: 
we would make DataRepository.Provider assignable instead of readonly.

DataRepository.Provider = DataRepository.Providers["SqlClientActivityProvider"];
upon set, a lock would be issued to serialize the provider and change all of the instatiated internal sql providers. 

Definately need to test this out a bit more, and ensure that this solution will be performant, but I think it won't be too long before implementation.

Other Option:
Changing the connection string at runtime,
((NetTiers.DAL.SqlClient.SqlNetTiersProvider)DataRepository.Provider).ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["netTiersConnectionString2"].ConnectionString;

It doesn't reset the connection string to child sqlEntityProviders.  We need to either, trickle that set operation to the child providers. for that call.

Would be nice to just be able to use a dictionary like this:
DataRepository.Providers["SqlClientActivityProvider"].MyEntityProvider.GetAll() and have the method invoke the correct innerSqlEntityProvider for each type.

I hope this makes sense, I've been in a rambling mood today. 

Well, i've obviously been drinking too much of the kool-aid today.

This does work in the current implementation.
DataRepository.Providers["SqlClientActivityProvider"].MyTableProvider.GetAll();

It looks like he is declaring multiple sections, and each section only has a single provider.  Each provider in each section is sharing a single connection string item, so he still needs the ability to override the connection string...  I suppose a hack that would work right now would be to create separate connection string entries for each database and have each provider use it's own connection string item.

Here's another solution (which could be implemented if/when we move back to using base classes):

1.  Change SqlNetTiersProvider to SqlNetTiersProviderBase:

public class SqlNetTiersProviderBase : NetTiersTest2.Northwind.DataAccessLayer.Bases.NetTiersProvider

2.  Add a new SqlNetTiers class that inherits from SqlNetTiersProviderBase and override the Initialize method:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Specialized;

namespace NetTiersTest2.Northwind.DataAccessLayer.SqlClient
{
   public sealed class SqlNetTiersProvider: SqlNetTiersProviderBase
   {
      public override void Initialize(string name, NameValueCollection config)
      {
         base.Initialize(name, config);
         //Change the connection string
         SqlConnectionStringBuilder connBuilder = new SqlConnectionStringBuilder(this.ConnectionString);
         connBuilder.InitialCatalog = "Northwind";
         this.ConnectionString = connBuilder.ConnectionString;
      }
   }
}

Hi,

As a quick hack (that now stays forever ), I found this method in SqlNetTiersProvider.cst:

public override void Initialize(string name, NameValueCollection config)

I found where the connection string was getting set:

_connectionString = WebConfigurationManager.ConnectionStrings[connect].ConnectionString;

And then I changed it to get it (at runtime) from my own code

_connectionString = MySecretConnectionStringHelper.CurrentConnectionString;

The only thing I have to do is add the small MySecretConnectionStringHelper  project to my SqlClient project after a regen... It's not perfect (but perfection is relative afterall )

- Perhaps someone on the team can implement a more reliable way of doing this that doesn't rely on any hard-coding or hacking...

Hi,

This functionality has been added.
http://community.codesmithtools.com/forums/permalink/12166/12166/ShowThread.aspx#12166

 

 Hello,

 

how can I check, if a connectionString to DB korrekt?

 

Create a text file and write your connectin string in it.

Then change the file extension to .UDL

Double click the file and presse Test Connection.

 

 

['s