ConnectionString Trick (ASP.NET)

<< changing owners | Home | Remote Development Trick >>

ConnectionString Trick (ASP.NET)

Like lots of people, I've wanted to store my ConnectionString in the web.config file. Again, like lots of people, I have different ConnectionStrings, one for the "live" server, one for the "development" server, and yet another for the "reporting" server. I'm sure many of you have the same thing. So then, you do development using the appropriate ConnectionString. Then when you deploy, how do you switch the ConnectionString to the new one? What I do, is in my web.config file I have quite a few parameters, not just ConnectionString. Like this:

LiveWebServerName, LiveServerConnectionString, DevServerConnectionString, ReportServerConnectionString, etc.

Then, in the application start code, I read those settings, and determine which ConnectionString to use. For example, if the LiveWebServerName is "SuperServer" and my application start code determines that it is indeed running on a box named "SuperServer" then it assigns the value from LiveServerConnectionString to be the "active connection string" for use throughout the site. Otherwise, it uses the DevServerConnectionString.

The strength of this, is that you don't have to remember to switch your web.config file, or have a second copy of the web.config, one for "live" and one for "dev." Or any tricks like that. This makes it foolproof. What is cool is that, no matter where you run your site from (your laptop, any team member's localhost, etc), it will only use the Development Server, never the Live Server. No chance for you to make a mistake and mess it up :) And of course once you deploy to your Live Web Server, it will automatically use the Live SQL Server.

Print | posted on Tuesday, November 18, 2003 8:38 PM

Feedback

# re: ConnectionString Trick (ASP.NET)

left by Peter Marshall at 11/19/2003 2:47 AM

To make this a useful item. Why not pose the snippet of code that gets the machine name.

# re: ConnectionString Trick (ASP.NET)

left by Nicko at 11/19/2003 3:26 AM

To get the machine name use:

Environment.MachineName

# re: ConnectionString Trick (ASP.NET)

left by Elrey Ronald at 11/19/2003 3:57 AM

// always get the correct connection string, throws exception if key is not there
connectionString = ConfigurationSettings.AppSettings[Environment.MachineName + "ConnectionString"];

#

left by blog.dvhome.co.uk at 11/19/2003 5:42 AM

# re: ConnectionString Trick (ASP.NET)

left by Travis Laborde at 11/19/2003 6:42 AM

Within an ASP.NET application, to find the name of the currently executing webserver machine, I use this snippet:

HttpContext.Current.Server.MachineName

Then, reading from the config file (one way is as mentioned by Elrey above - thanks!) I determine if the server is the "live" server or not, and then use the appropriate connection string.

This works when you know the name of one server for sure (for example the LIVE server) and you want to specify that if the web app is running from THAT server, then it should use a certain connection string, otherwise to use a different connection string. The example I gave uses the LIVE server example, but the concept works equally well for laptop versus any remote server that you may deploy to later.

Example: You have a connection string that you will always use while on the laptop, but if the site runs on a webserver other than your laptop (like a remote deployment where you dont know the machine name ahead of time) then you want to use a different connection string. Same idea, just used in the opposite way :)

# re: ConnectionString Trick (ASP.NET)

left by Adam Weigert at 11/19/2003 7:32 AM

Watch out for machine name changes or application moves to other machines without your knowledge ... hey it could happen ;)

# re: ConnectionString Trick (ASP.NET)

left by Don Kitchen at 11/19/2003 8:07 AM

Machine changes could definitely happen if you're using a shared hosting environment provided by a 3rd party. This moving could be a result of a) the original machine crashing or b) them moving your site because there are too many sites on one machine.

I have had this happen to me before. Luckily I wasn't using any code that referenced the machine name directly.

# re: ConnectionString Trick (ASP.NET)

left by Travis Laborde at 11/19/2003 10:34 AM

Exactly. I use this trick in two scnearios:

1) In corporate development, where the production server is constant, but there is a team of developers who each run the web app as localhost during development. In this case, I set the LiveServerName, and use the "else" part to point to the development sql server.

2) In doing remote development to be deployed to places outside my control. Here, the development server is constant (my laptop) but the live server changes. In this case I set the LiveServerName to be my laptop, and use the "else" part to point to the sql server that is to be used no matter where the app is deployed.

Simple eh?

# re: ConnectionString Trick (ASP.NET)

left by Justin Rudd at 11/19/2003 4:23 PM

I think you would have to change it a bit to support web farms in your "live" environment. All the machines can't be named "SuperServer". They'd probably be something like "SuperServer1", "SuperServer2", etc. So your code would need to check that the current machine name starts with LiveServerName.

# re: ConnectionString Trick (ASP.NET)

left by Andrew at 11/20/2003 4:56 PM

I haven't yet seen a solution I really like except getting the connectionString from CommerceServer.

There is the file=user.config attribute option with the user.config not being part of the project and thus not deployed.

I have one project where the web.config is set to buildAction:None. Again the file is not automaticaly deployed.

A protective approach is that the dev environment cannot reach the production SQL server and the production env can't reach the dev SQL.

# re: ConnectionString Trick (ASP.NET)

left by jmg at 3/6/2004 12:34 AM

Seems like a stupid questions but why not have the connection string in the appSettings section of the machine.config. You have the added benefit of the security behind the machine.config. So for example when I run locally my machines machine.config points to development. the development, staging and production application servers machine.config also have the correct connection string for that environment.

# re: ConnectionString Trick (ASP.NET)

left by Travis Laborde at 3/6/2004 7:58 AM

Not a bad idea at all, if you have access to the machine.config of all the machines that might be used.

But even in those cases, it's just a bit "more trouble" in my book. The web.config file travels with your app, and can xcopy deploy (other than the virtual directory - but even that is going to go away in 2.0 as I understand). It's just quicker (to me).

Thanks for the question!

# re: ConnectionString Trick (ASP.NET)

left by lwm at 3/26/2004 10:58 AM

Have you tried to use System.Web.HttpContext.Current.Request.Url.Host in a web farm environment?

# re: ConnectionString Trick (ASP.NET)

left by Travis Laborde at 3/26/2004 7:59 PM

I have not. (un?)Fortunately, all of the work I've done so far has been on websites that only needed one physical server. Our company is growing though :) I'll post back here when we've put something like that in place.

# re: ConnectionString Trick (ASP.NET)

left by Brent Asplund at 4/22/2004 1:42 PM

Try using a windows Environment Variable for this. All production servers will have the same value: production, QA servers: QA, etc. This way you dont have to ever look at the machine name or urls to determine which environment the app is running in.

# re: ConnectionString Trick (ASP.NET)

left by Travis Laborde at 4/22/2004 2:51 PM

That's a great extension of the same idea! To get "groups" so that more than one server could be in the same role within the app.

There have been cases when I had no control over the server at all, so I couldn't have used this, where I could still detect the machine name, but that is also imperfect in that, if I do have that little control over the server, whats to stop them from changing it's name?

Good addition, thanks.

# re: ConnectionString Trick (ASP.NET)

left by xyz at 7/12/2004 2:49 PM

idiot

# re: ConnectionString Trick (ASP.NET)

left by Hal at 8/10/2005 10:01 PM

Using an environment variables can have a number of downfalls:

1. Environment variables are open and available to all applications. In a shared environment this can be huge.

2. Applications do not necessarily see changes to application variables without a restart of IIS.

I am definitely nervous about the connection string being hardcoded into the web config, especially if using sql server authentication. I would recommend using a DSN so that the password is not hardcoded into a file on the file system.

# re: ConnectionString Trick (ASP.NET)

left by Lazy Dev at 8/16/2005 8:45 AM

This all sounds a bit too complicated for my lil brain. What I would look for is using conditional attributes - to check if you have DEBUG or BUILD environment. (Im reading this book effective C# by B.Wagner) - let me know if any of you has gotten it to work the best way?

# re: ConnectionString Trick (ASP.NET)

left by Lazy Dev at 8/16/2005 9:21 AM

So here's my code I came up with:

private string cnStr()
{ string connString = "";
connString = System.Configuration.ConfigurationSettings.AppSettings["LiveDB"];
this.DevCnStr(ref connString); // This will
// only be included in Debug Environment
return connString;
}

[ Conditional( "DEBUG" ) ]
private void DevCnStr(ref string cnStr )
{
cnStr = System.Configuration.ConfigurationSettings.AppSettings["DevDB"];
}

# re: ConnectionString Trick (ASP.NET)

left by Lazy Dev at 8/16/2005 9:26 AM

Oh yeah - dont forget
using System.Diagnostics;
when using attributes.

# re: ConnectionString Trick (ASP.NET)

left by Geoff Furlong at 9/6/2005 3:04 AM

What if the user selects the database?
E.g. I have a "Live" and "Training" database with 2 conn strings in web.config and I want to switch when the user selects a database on the initial page. Then use the selected connection string all through the app.
The problem is how to store which database was selected. I don't want to use a Session variable since they are insecure and timeout.
This app needs to be up 24x7.

Any ideas?

# re: ConnectionString Trick (ASP.NET)

left by SanaUllah Almani at 12/1/2005 5:31 AM

hay Plz tell me the correct path to connect to the sqlserver 2000 immediatley

# re: ConnectionString Trick (ASP.NET)

left by Pete at 2/2/2006 1:31 PM

A simple method is to test for IsLocal using the request object, like so:

If Request.IsLocal Then
'Use local connection
Else
'Use server connection
End If

Add this to the application start section of the global asax and stick the connection string in an application variable.

# re: ConnectionString Trick (ASP.NET)

left by Sheir at 8/23/2006 1:14 PM

Hi,
I was wondering how can I read a key/value pair in the machine.config AppSettings section?
We are going to store the name of the database server there and so I figure out how to read that key/value.
This would be a web app in .NET 1.1 and
ConfigurationSettings.AppSettings["keyInMachineConfig"] returns null.

Thanks
sheir@magma.ca

# re: ConnectionString Trick (ASP.NET)

left by DaveO at 8/29/2006 12:30 PM

Debug directives are useful for this scenario. The correct code is compiled when switching between debug and release mode.

#if DEBUG
connString = ConfigurationSettings.AppSettings["DevelopmentConnString"];
#else
connString = ConfigurationSettings.AppSettings["ProductionConnString"];
#endif

# re: ConnectionString Trick (ASP.NET)

left by Abhishek Pareek at 9/8/2006 2:20 AM

# re: ConnectionString Trick (ASP.NET)

left by Nike at 11/8/2006 1:22 PM

What if I need different connection strings (depending not on debug/release, but on machine name) also in ConnectionStrings used in page controls, like Datasources?
Is there a way to make conditional items in web.config itself?

# re: ConnectionString Trick (ASP.NET)

left by Lalit pandey at 12/6/2006 3:01 AM

First, import the "System.Data.OleDb" namespace. We need this namespace to work with Microsoft Access and other OLE DB database providers. We will create the connection to the database in the Page_Load subroutine. We create a dbconn variable as a new OleDbConnection class with a connection string which identifies the OLE DB provider and the location of the database.

# re: ConnectionString Trick (ASP.NET)

left by err at 12/7/2006 8:13 PM

check this out -

using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.ComponentModel;

namespace MyStuff.WebControls
{

public class SqlDataSource2 : SqlDataSource
{
public SqlDataSource2()
{
}

private string lcs = "";
public string LocalConnectionString
{
get
{
//return ViewState["lcs"];
return lcs;
}
set
{
//ViewState["lcs"] = (string)value;
lcs = (string)value;
}
}

private string lpn = "";
public string LocalProviderName
{
get
{
//return ViewState["lpn"]
return lpn;
}
set
{
//ViewState["lpn"] = (string)value;
lpn = (string)value;
}
}

protected override void OnLoad(EventArgs e)
{
if (HttpContext.Current.Request.IsLocal)
{
if (LocalConnectionString != "")
{
ConnectionString = LocalConnectionString;
}
if (LocalProviderName != "")
{
ProviderName = LocalProviderName;
}
}
base.OnLoad(e);
}

}

}

# re: ConnectionString Trick (ASP.NET)

left by MikeR at 12/11/2006 1:32 PM

Try adding an entry to the C:\WINDOWS\system32\drivers\etc\hosts file.

#IP address of SQL Server Alias Server Name

127.0.0.1 SQLServer

Do this on your development, and production environments setting the IP to the correct server.

Then reference the server as "SQLServer" in your connection string.

Comments have been closed on this topic.

Carter's Dad - Parker's Dad Too!

play boxing daddy?

Archives