Switch Database Schema at Run-Time

Learn how to switch your database schema without using "Find and Replace."
- Jing Ding, Senior Systems Consultant of The Ohio State University Medical Center

November 10, 2008
Iron Speed Designer V5.X

Introduction

Most Iron Speed Designer enterprise-class applications are developed in three stages: development, QA and production. The databases in different stages have the same structure, but reside in different schemas and/or servers. If the schema names are the same, it is easy to switch the database from one stage to the next. All you have to do is to modify the connection string in the web.config.

However, this method will not work if the schema name changes during the switch. In that case, you also need to "find and replace" the schema name in data access layer’s table definition files, and then rebuild the entire application. Switching database schema at run-time can eliminate the error-prone "find and replace" process.

Solution

Iron Speed Designer-generated code accesses the database through business layer table (view) classes. Each table (view) class can only have a singleton static instance. It is possible to switch schema at run-time by manipulating these instances. The best place to do this is in Global.asax Application_Start() method. The following example uses an Oracle database, whose schema name is defined as "Database" in the connection string.

Implementation

Uncomment the Application_Start() method in Global.asax, and insert the following code.

C#:

// Find the App_Code assembly.
Assembly appAssembly = null;
foreach (Assembly assem in AppDomain.CurrentDomain.GetAssemblies()) {
    if (assem.FullName.StartsWith("App_Code")) {
        appAssembly = assem;
        break;
    }
}

// Find the table classes.
List BusinessLayerTables = new List();
foreach (Type typ in appAssembly.ManifestModule.GetTypes()) {
    if (Regex.IsMatch(typ.FullName, @"\w+\.Business\.(?!Base)\w+(Table|View)$"))
        BusinessLayerTables.Add(typ.GetField("Instance").GetValue(null) as BaseTable);
}

// Get the schema name from web.config
String connString = ConfigurationManager.AppSettings[“ConnectionName”];
DatabaseConnection dbConnection = DatabaseConnection.Parse(“ConnectionName”, connString);

// Switch schema name
foreach (BaseTable table in BusinessLayerTables)
    table.TableDefinition.Owner = dbConnection.DatabaseName;

Visual Basic .NET:

' Find the App_Code assembly.
Dim appAssembly As Assembly = Nothing
For Each assem As Assembly In AppDomain.CurrentDomain.GetAssemblies()
    If assem.FullName.StartsWith("App_Code") Then
        appAssembly = assem
        Exit For
    End If
Next

' Find the table classes.
Dim BusinessLayerTables As New List(Of BaseTable)()
For Each typ As Type In appAssembly.ManifestModule.GetTypes()
    If Regex.IsMatch(typ.FullName, "\w+\.Business\.(?!Base)\w+(Table|View)$") Then
        BusinessLayerTables.Add(TryCast(typ.GetField("Instance").GetValue(Nothing), BaseTable))
    End If
Next

' Get the schema name from web.config
Dim connString As String = ConfigurationManager.AppSettings("ConnectionName")
Dim dbConnection As DatabaseConnection = DatabaseConnection.Parse("ConnectionName", connString)

' Switch schema name
For Each table As BaseTable In BusinessLayerTables
    table.TableDefinition.Owner = dbConnection.DatabaseName
Next

Conclusion

Iron Speed Designer makes it easy to switch the database schema at run-time by using a singleton design pattern in the business layer.

About the Author

Jing Ding has a PhD in Computer Engineering, Bioinformatics and Computational Biology, and an M.S. in Toxicology from Iowa State University. He received his B.S. in biophysics from Fundan University in Shanghai, China. He is a self-taught programmer who "played" with assembly, C and C++ in the 1990s. He took a break from programming from 1997 to 2000. When he picked it up again in 2001, he worked with Java. Jing began working with C# and .NET in 2006.



	Copyright © 2005-2012, Iron Speed, Inc. Iron Speed® is a registered trademark. All rights reserved.	Privacy Statement