ConnectionString Trick (ASP.NET)

Tue Nov 18, 2003 by Travis Laborde

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.

Legacy Comments


Peter Marshall 2003-11-19	re: ConnectionString Trick (ASP.NET) To make this a useful item. Why not pose the snippet of code that gets the machine name.

Nicko 2003-11-19	re: ConnectionString Trick (ASP.NET) To get the machine name use: Environment.MachineName

Elrey Ronald 2003-11-19	re: ConnectionString Trick (ASP.NET) // always get the correct connection string, throws exception if key is not there connectionString = ConfigurationSettings.AppSettings[Environment.MachineName + "ConnectionString"];

Travis Laborde 2003-11-19	re: ConnectionString Trick (ASP.NET) 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 :)

Adam Weigert 2003-11-19	re: ConnectionString Trick (ASP.NET) Watch out for machine name changes or application moves to other machines without your knowledge ... hey it could happen ;)

Don Kitchen 2003-11-19	re: ConnectionString Trick (ASP.NET) 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.

Travis Laborde 2003-11-19	re: ConnectionString Trick (ASP.NET) 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?

Justin Rudd 2003-11-19	re: ConnectionString Trick (ASP.NET) 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.

Andrew 2003-11-20	re: ConnectionString Trick (ASP.NET) 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.

jmg 2004-03-06	re: ConnectionString Trick (ASP.NET) 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.

Travis Laborde 2004-03-06	re: ConnectionString Trick (ASP.NET) 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!

lwm 2004-03-26	re: ConnectionString Trick (ASP.NET) Have you tried to use System.Web.HttpContext.Current.Request.Url.Host in a web farm environment?

Travis Laborde 2004-03-26	re: ConnectionString Trick (ASP.NET) 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.

Brent Asplund 2004-04-22	re: ConnectionString Trick (ASP.NET) 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.

Travis Laborde 2004-04-22	re: ConnectionString Trick (ASP.NET) 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.

xyz 2004-07-12	re: ConnectionString Trick (ASP.NET) idiot

Hal 2005-08-10	re: ConnectionString Trick (ASP.NET) 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.

Lazy Dev 2005-08-16	re: ConnectionString Trick (ASP.NET) 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?

Lazy Dev 2005-08-16	re: ConnectionString Trick (ASP.NET) 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"]; }

Lazy Dev 2005-08-16	re: ConnectionString Trick (ASP.NET) Oh yeah - dont forget using System.Diagnostics; when using attributes.

Geoff Furlong 2005-09-06	re: ConnectionString Trick (ASP.NET) 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?

SanaUllah Almani 2005-12-01	re: ConnectionString Trick (ASP.NET) hay Plz tell me the correct path to connect to the sqlserver 2000 immediatley

Pete 2006-02-02	re: ConnectionString Trick (ASP.NET) 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.

Sheir 2006-08-23	re: ConnectionString Trick (ASP.NET) 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

DaveO 2006-08-29	re: ConnectionString Trick (ASP.NET) 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

Abhishek Pareek 2006-09-08	re: ConnectionString Trick (ASP.NET) <connectionStrings> <add name="MyDbConn1" connectionString="Server=MyServer;Database=MyDb;Trusted_Connection=Yes;"/> </connectionStrings>

Nike 2006-11-08	re: ConnectionString Trick (ASP.NET) 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?

Lalit pandey 2006-12-06	re: ConnectionString Trick (ASP.NET) 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.

err 2006-12-07	re: ConnectionString Trick (ASP.NET) 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); } } }

MikeR 2006-12-11	re: ConnectionString Trick (ASP.NET) 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.