Connection strings and .netTiers

Last post 05-16-2008 10:16 AM by vbandrade. 8 replies.

Page 1 of 1 (9 items)
	Sort Posts: Previous Next

03-09-2006 2:35 PM

mmlachak
Joined on 03-09-2006
Posts 10
Points 255

Connection strings and .netTiers

Reply Contact

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.

<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.

Post Points: 35

03-09-2006 3:07 PM In reply to

Robert Hinojosa
Joined on 04-05-2004
Fort Worth, TX
Posts 2,797
Points 70,970

Re: Connection strings and .netTiers

Reply Contact

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. Smile [:)]

Robert Hinojosa

-------------------------------------
Member of the Codesmith Tools, .netTiers, teams

http://www.nettiers.com
-------------------------------------

Post Points: 5

03-09-2006 3:23 PM In reply to

Robert Hinojosa
Joined on 04-05-2004
Fort Worth, TX
Posts 2,797
Points 70,970

Re: Connection strings and .netTiers

Reply Contact

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

This does work in the current implementation. Tongue Tied [:S]

DataRepository.Providers["SqlClientActivityProvider"].MyTableProvider.GetAll();

Robert Hinojosa

-------------------------------------
Member of the Codesmith Tools, .netTiers, teams

http://www.nettiers.com
-------------------------------------

Post Points: 35

03-09-2006 4:15 PM In reply to

bgjohnso
Joined on 09-15-2005
Spokane, WS
Posts 763
Points 22,465

Re: Connection strings and .netTiers

Reply Contact

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;
      }
   }
}

Ben Johnson
------------------------------
Member of the .NetTiers team
Visit http://www.nettiers.com
------------------------------

Post Points: 35

03-13-2006 9:40 PM In reply to

rockmaster1
Joined on 02-05-2006
Posts 96
Points 2,360

Re: Connection strings and .netTiers

Reply Contact

Hi,

As a quick hack (that now stays forever Smile [:)] ), 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 Smile [:)] )

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

Clynton

-------------------------------------
Member of the .NetTiers team

http://www.nettiers.com
-------------------------------------

Post Points: 35

03-14-2006 9:38 AM In reply to

Meech
Joined on 02-14-2006
Posts 98
Points 2,788

Re: Connection strings and .netTiers

Reply Contact

Not sure if this helps, but I've hacked my netTiers such that the default constructor creates a functioning SQLProvider class. Here is the code that I am using, I wonder if you can hook in and swap connection strings as necessary?

DAL.Bases.NetTiersServiceSection _netservice = (DAL.Bases.NetTiersServiceSection)System.Web.Configuration.WebConfigurationManager.GetSection("netTiersService");
System.Configuration.ProviderSettings _provider = _netservice.Providers[_netservice.DefaultProvider];

string _constringname = _provider.Parameters["connectionStringName"];
string _usesp = _provider.Parameters["useStoredProcedure"];
string _provname = _provider.Parameters["providerInvariantName"];
string _constr = System.Configuration.ConfigurationManager.ConnectionStrings[_constringname].ConnectionString;

base.ConnectionString = _constr;
base.UseStoredProcedure = (_usesp.ToLower() == "yes" ? true : false);
base.ProviderInvariantName = _provname;

Post Points: 35

04-02-2006 6:46 PM In reply to

Robert Hinojosa
Joined on 04-05-2004
Fort Worth, TX
Posts 2,797
Points 70,970

Re: Connection strings and .netTiers

Reply Contact

Hi,

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

Robert Hinojosa

-------------------------------------
Member of the Codesmith Tools, .netTiers, teams

http://www.nettiers.com
-------------------------------------