I’m working on a project that must be test and implemented on different environments (local, development, staging and production). It means all functions and piece of codes need to be done on local environment then be moved to development environment. Only everything is working well on staging environment then it is moved to production. The process is quite complicated, isn’t it?
In order to avoid creating several web.config for different environments or renaming web.config file when move your web application among environments, we need a way to detect the environment (local, development, staging and production) automatically then return an appropriate connection string for that environment.
Below is an idea that I’m using for my project, it may save your time and avoid some unexpected errors while update the web.config file:
1. Define all environment URLs and Connection String for each
Open your web.config file (or create a new one if it isn’t existed) and add some key/value in the appSettings section as below:
[xml][/xml]<?xml version="1.0"?> <configuration> <appSettings> <add key="envLocal" value="localhost"/> <add key="envDev" value="dev.4rapiddev.com"/> <add key="envSG" value="staging.4rapiddev.com"/> <add key="envProd" value="4rapiddev.com"/> <add key="csLocal" value="Data Source=localhost;Initial Catalog=4rapiddev;Persist Security Info=True;User ID=sa; Password=123456;"/> <add key="csDev" value="Data Source=localhost;Initial Catalog=db_development;Persist Security Info=True;User ID=db_user; Password=db_pass;"/> <add key="csSG" value="Data Source=localhost;Initial Catalog=db_staging;Persist Security Info=True;User ID=db_user; Password=db_pass;"/> <add key="csPRod" value="Data Source=localhost;Initial Catalog=db_production;Persist Security Info=True;User ID=db_user; Password=db_pass;"/> </appSettings> <system.web> <compilation debug="true" /> </system.web> </configuration> |
2. Create a static function that returns a proper connection string base on environment URL automatically
This function will be called every time create a new SqlConnection (or SqlDatabase) instance.
using System; using System.Collections.Generic; using System.Configuration; using System.Web; /// <summary> /// Summary description for common /// </summary> public class common { public static string LoadConnectionString() { string urlPath = HttpContext.Current.Request.Url.AbsoluteUri.ToLower(); string connStr = ""; if (urlPath.Contains(ConfigurationManager.AppSettings["envLocal"])) connStr = ConfigurationManager.AppSettings["csLocal"]; else if (urlPath.Contains(ConfigurationManager.AppSettings["envDev"])) connStr = ConfigurationManager.AppSettings["csDev"]; else if (urlPath.Contains(ConfigurationManager.AppSettings["envSG"])) connStr = ConfigurationManager.AppSettings["csSG"]; else if (urlPath.Contains(ConfigurationManager.AppSettings["envProd"])) connStr = ConfigurationManager.AppSettings["csProd"]; else connStr = ConfigurationManager.AppSettings["csProd"]; return connStr; } } |
3. Example
Check out an example below that demonstrates How to detect the environment and change the connection string for that environment automatically.
using System; using System.Collections.Generic; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.SqlClient; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { string connString = common.LoadConnectionString(); using (SqlConnection conn = new SqlConnection(connString)) { string cmdText = "SELECT * FROM tbl_customers"; SqlCommand cmd = new SqlCommand(cmdText, conn); cmd.Connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Response.Write(reader["name"].ToString() + "<br>"); } reader.Close(); cmd.Connection.Close(); } } } |
Download all source code above