General question on connection string setting within code

[Steve Y]

Hi.

This is not really a Telerik issue but I have not been able to find a solution on standard asp.net forums etc etc, so I thought I'd give it a try here as there are lots of very knowledgeable folks here. I'm sure this has been seen many times and it would be useful to have the answer for a lot of development/hosted testing environments...

I'm in the middle of a longish development cycle and I want to be able to switch automatically between my local development system/database and my hosted live/test system & database within code. Currently I have two web.config files and I'm alternating between the two. One on my local server and the other in my hosted environment. Unfortunately, this means that if I have to make a change within my web.config file I have to remember to do it for both files. I'm using the standard microsoft asp.net membership provider for login etc.

I'm using VS2008. This is an asp.net development and I'm a C# person. I'm using latest Q3 Telerik asp.net ajax controls. I'm using a SQL Server 2005 database server which contains my application database tables as well as the standard AspNetSqlMembershipProvider tables. I also have a hosted live system where I copy all my code etc, as well as a hosted instance of my database (app & standard AspNetSqlMembershipProvider tables).

I want to be able to programmatically  set the correct connectionstring for my app depending on whether I'm running locally in a dev environment or whether I'm running on the live hosted server so I don't have to maintain two different web.config files - I can just use one and my application will work out which database to use. I have to do it for both my main data access and the membership provider. I've worked out how to do it for my main app database. I'm using LinqToSql and I have generated a partial class for my datacontext which sets the correct connectionstring.  In my web.config file I have two connectionstrings - one for my live servers and one for my dev environment. 

Here's my connection strings in web.config.

        <add name="realtracproConnectionStringLive" connectionString="Data Source=72.18.156.252,1533;  Initial Catalog=realtracpro;Persist Security Info=True;User ID=realtracpro;Password=xxxxxx" providerName="System.Data.SqlClient"/>
        <add name="realtracproConnectionStringDev" connectionString="Data Source=STEVE-IBM;Initial Catalog=realtracpro;Persist Security Info=True;User ID=realmaxcrm;Password=xxxxxx" providerName="System.Data.SqlClient"/>

My code looks like this:

partial class MyDataContext

[

partial void OnCreated()

[

ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["MyConnectionStringLive"];

switch (HttpContext.Current.Server.MachineName)

[

case "DEV-SERVER":

connectionString = ConfigurationManager.ConnectionStrings["MyConnectionStringDev"];

break;

case "DB-SERVER":

connectionString = ConfigurationManager.ConnectionStrings["MyConnectionStringDev"];

break;

]

if (connectionString != null) Connection.ConnectionString = connectionString.ConnectionString;

]

]

However, I cannot work out how to do it for the membership provider stuff (I'm using the standard membership provider, I just want to point it at the correct DB through the connection string depending on if I'm running on my dev system or if I'm hosted in my live environment). Here's the web.config for the membership provider item. This is what I use in web.config for my development system. The web.config on my hosted server points to the live connection string.

<add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="realtracproConnectionStringDev" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" passwordStrengthRegularExpression="" applicationName="/"/>

Any ideas on how to automatically select the correct connection string for the standard membership provider through c# code?

Thanks, Steve

[Nikolay Rusev]

Hello Steve,

You can try implement custom MembershipProvider and override its Initialize method. This the place where your logic for setting proper connection string must be placed.

Links below will give details of implementing and setting your custom provider.
Implementing a Membership Provider
Create a Custom Membership Provider
ProviderBase..::.Initialize Method
MembershipProvider Class

I hope this helps.

Regards,
Nikolay
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Andrew]

I am not sure that this is the answer for you, but it works for me.

Put the following in the web.config:

  <connectionStrings configSource="ConnectionStrings.config">
  </connectionStrings>

and then just maintain different ConnectionString.config files in each deployment.

<connectionStrings>
  <add name="ConnectionStringxxx" connectionString="Data Source=xxx.xxx.xxx.xxx;Initial Catalog=Master;Integrated Security=True"
    providerName="System.Data.SqlClient" />
</connectionStrings>

Your code can always refer to ConnectionStringxxx

[Steve Y]

Andrew.

Simple, elegant, and it works. This is a great tip.

Thank you.

regards,
Steve