Access & asp.net 2.0 Mini How to: Switch between Live and local connections without tears...

Hello All,

I have seen many posts and queries regarding how to switch between Local and Live connection strings (without pain)
here's my solution. (replace the color highlited items within <...> with your own values. remove the <>)
This one is for MS Access and Asp.net 2.0 related sites.
But you could use the same logic for other scenarios.



In your web.config do this
Add 2 entries between the <connectionStrings></connectionStrings> like shown below


<connectionStrings>
<add name="ConnectionString_Live" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\web\<your user account name here>\htdocs\_database\foo.mdb;Persist Security Info=False"/>
<add name="ConnectionString_Local" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<your local drive>:\database\foo.mdb;Persist Security Info=False"/>
</connectionStrings>
<system.web>

*******************************************************************************

In the Data Access layer Create a new property like so

#region public properties
public static string ConnectionString
{
get
{
// determine if we are on a local box or on live server
if (HttpContext.Current.Request.ServerVariables["HTTP_HOST"] == "localhost")
{
return ConfigurationManager.ConnectionStrings["ConnectionString_Local"].ConnectionString;
}
else
{
return ConfigurationManager.ConnectionStrings["ConnectionString_Live"].ConnectionString;
}
}
}
#endregion

and use the property ConnectionString anywhere and everywhere. It will work like a charm. No more tears.


public static List<QuoteCategory> GetQuotes(bool All)
{
string sql = "SELECT c.CategoryId, c.Category FROM Category c ORDER BY c.Category";
using (OleDbConnection connection = new OleDbConnection(ConnectionString))
{
//put code here
}
}

hope this helps.
Please let me know if you need any more code / samples.

Enjoy
KM