Connection String Dynamically

~Bean~ · May 27th, 2005, 07:33 AM

I have taken over a (ASP.NET) project where the original deisgner placed the connection string for DB access in the web.config file as a key. The class structure for the web app is like this...

Each class in the app uses data access, and inherits the connection string from a single class like so:

Code:

Imports System.Data.SqlClient
Public Class clsTeacher : Inherits clsSQLBase
    Private m_FirstName As String
    Private m_LastName As String
...
etc...

Public Function blahfoo

'using m_connString for DB access here...

End Function

etc.
...
End Class

class clsSQLBase looks like this...

Code:

Imports System.Configuration
Public Class clsSQLBase
    Friend m_connString As String

    Sub New()
        Dim myreader As New AppSettingsReader
'this is where it loads the string from the web.config file...
        m_connString = myreader.GetValue("SqlConnection1.ConnectionString", GetType(System.String))
    End Sub
End Class

Now I am looking for a way/ideas on how to (easily) change what connection string is used for the app according to what URL has been entered. So, if the user type in "http://trainme.foosite.com" it will use a connection string pointing to our training database, if they enter a URL like "http://www.foosite.com" the app will use a connection string for the live data...

-------------------------
Beware of programmers with screwdrivers...

jbenson001 · May 27th, 2005, 11:04 PM

Pass a querystring variable to an intermidated form. Grab that variable and redirect to the page based on it's value.

Imar · May 28th, 2005, 02:00 AM

There is no need for an intermediate page; the clsSQLBase class should be able to figure out where it's used. Besides, an intermediate page probably wouldn't work because you need this setting on each page that performs data access.

Put the following in the New method of your clsSQLBase class, replacing what was in there:

Code:

Dim keyFromConfig As String = "SqlConnection1.ConnectionString" ' the default setting
Dim myreader As New AppSettingsReader

Try
  If HttpContext.Current.Request.ServerVariables.Get("HTTP_HOST").ToLower() = "www.foosite.com" Then
    keyFromConfig  = "SqlConnection1.ConnectionString"
  Else
    keyFromConfig = "Name of Config Key for training site"
  End If
Catch

Finally
  m_connString = myreader.GetValue(keyFromConfig , GetType(System.String))
End Try

The ServerVariables.Get("HTTP_HOST") returns the current server name used, like www.foosite.com. Based on this name, the web.config key is changed and the correct one is retrieved from the file.

The Try Catch is needed in case this database class is also (accidentally) used in an environment where HttpContext.Current doesn't make any sense, like a desk top application.
Should the code crash, then it defaults back to SqlConnection1.ConnectionString so you always get a value back from the config file.

I haven't tested this code, but I am sure you get the idea.

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: The sound of the Big Babou by Laurent Garnier (Track 4 from the album: Unreasonable Behaviour) What's This?