Change Database Dynamically at Run-Time

Updated June 5, 2006
Iron Speed Designer V3.2 and V4.0

In some situations, you may need to connect to a different database based on the currently logged in user. For example, say there are four identical databases and you require the ability to connect to one of the four based on the logged in user. This can be accomplished by dynamically changing connection strings. You can define multiple connection strings, one for each database, and then change the default connection string used by a database table dynamically. Here are step-by-step instructions on how to do this.

Step 1: Create an application using one of the identical databases. The Web.config file will contain a connection name key to connect to the first database including the type of connection, user Id and password as follows:

<add key="DatabaseNorthwind1" value="Provider=SQLOLEDB;

Data Source=(local);

Database=Northwind1;

Trusted_Connection=yes;

User Id=;

Password="

Step 2: Modify your application’s Web.config file to create connection name keys for each database, e.g:

<add key="DatabaseNorthwind1" value="Provider=SQLOLEDB;

Data Source=(local);

Database=Northwind1;

Trusted_Connection=yes;

User Id=;

Password="

<add key="DatabaseNorthwind2" value="Provider=SQLOLEDB;

Data Source=(local);

Database=Northwind2;

Trusted_Connection=yes;

User Id=;

Password="

<add key="DatabaseNorthwind3" value="Provider=SQLOLEDB;

Data Source=(local);

Database=Northwind3;

Trusted_Connection=yes;

User Id=;

Password="

<add key="DatabaseNorthwind4" value="Provider=SQLOLEDB;

Data Source=(local);

Database=Northwind4;

Trusted_Connection=yes;

User Id=;

Password="

Step 3: For each database table that you would like to change, override the ConnectionName property for the SqlTable class. For example, if you have a Customers table, you will have a CustomersSqlTable class in:

…\MyApp\DataAccess\CustomersSqlTable.vb

You can retrieve the userName of the currently logged in user from the session data and compare this user name to return the connection name.

Public Overrides Property ConnectionName() As String

' Define the Get property
Get

' Get the user name from the session

Dim sessionData As System.Collections.Hashtable

sessionData = CType(System.Web.HttpContext.Current.Session.Item( _

BaseClasses.Utils.SessionConst.LoginInfo), _

Hashtable)

Dim userName As String

userName = CStr(sessionData(BaseClasses.Utils.SessionConst.UserName))

' Select the connection name key based on the user

Select Case username

Case "User1"

Return "DatabaseNorthwind1"

Case "User2"

Return "DatabaseNorthwind2"

Case "User3"

Return "DatabaseNorthwind3"

Case "User4"

Return "DatabaseNorthwind4"

End Select

' Default case

Return MyBase.ConnectionName()

End Get

' Define the set property

Set(ByVal Value As String)

' call the underlying base set method.

MyBase.ConnectionName = Value

End Set

End Property

Step 4: Repeat this customization for all tables that require run-time switching. To make this change across your application, modify the base class where all SqlTable classes are derived from in the BaseClasses. The specific class depends on which database access method you are using, as illustrated in the partial list below.

Microsoft Access:

…\BaseClasses\Data\SqlProvider\DynamicAccessAdapter.vb

Microsoft SQL Server with inline SQL:

…\BaseClasses\Data\SqlProvider\DynamicSQLServerAdapter.vb

Microsoft SQL Server with generated stored procedures:

…\BaseClasses\Data\SqlProvider\StoredProceduresSQLServerAdapter.vb

Oracle with inline SQL:

…\BaseClasses\Data\SqlProvider\Oracle9DynamicSQLAdapter.vb

Oracle with inline SQL:

…\BaseClasses\Data\SqlProvider\StoredProceduresOracleAdapter.vb

Step 5: Build and Run your application.

Review the adapter files in the SqlProvider folder for the specific adapter file you need.

Change Database Dynamically at Run-Time

See Also