Dynamically Update a SQL Database Adding Tables
or Columns using SMO Version 9 and 10 - January 2011

If you download this tip please consider making a modest donation.

Over the years I have developed techniques for dynamically updating a database adding tables, columns and relationships. The technique allows a 'production database' to be updated with a new schema while maintaining the integrity of the database. For example: you decide to add a column to a customer name and address table. You define the new column then run the database update application. With this technique you can dynamically add the new column without the need to take the database offline. With the use of this technique and the ability to generate VB.Net code, as described in last months tip, you will be using the new column in a web application within minutes.

2011/02/09 - Today I converted the application to use SMO Version 10 [for SQL Server 2008]. See below.

Demo Web Application

I created a demo VS/2008, VB.Net, Asp.Net, SMO web application to demonstrate dynamic database updating. The application is called 'DynamicSQLDBUpdate'. The process is described using the following series of screen captures.


1. You can let the application search for SQL Servers or you can enter that information manually.
 

2a. You will see this page if the application searched for servers.
Select a server then click 'Continue'.
 

2b. In either case use this page to connect to SQL Server.
Enter connection information then click 'Connect'.
 

3. A list of databases attached to the selected SQL Server is displayed.
Click the View button to display the tables associated with the database.
If the database was created by this application you can Delete or Update it. See below.
 

4. The View button was clicked causing a list of tables found in the selected database to be displayed.
The number of columns found in each table and, if any, the number of relationships is displayed.
Click the Details button to display a list of the table's columns and relationships.
 

5. The 'Details' button was clicked on the prior page causing a list
of columns and relationships to be displayed.
 

6. Now create a new database.
The default name is constructed using the date and time.
You can enter any valid, unique database name.
In this example, database MySample... was created.
One control table is automatically added to the new database.
You could click the 'View' button to display the control table name then
click View again to see it's columns.
 

7. You can delete or update databases created here.
A small text file is stored in AppPath to indicate the database was created here.
Click the 'Update' button to display the next page.
 

8. You can add tables, columns and relationships by clicking one of the Option buttons.
We finally get to the 'guts' of this month's tip.
You will want to view the source code in class module cDSDUDoUpdate.vb.
 

9. All 3 update options were used to create this database's tables.
A total of 14 tables were added.
Click the Details button to display all the columns and
relationships associated with the selected table.
 

10. You can delete any database created via this application.
A warning message is displayed before the actual deletion takes place.

Download VB.Net Code

Download the source code by clicking on the zipped file name.

Download - Source Zipped File Size
VS2008 VB.Net, ASP.Net Solution - SMO Version 9 DynamicSQLDBUpdate.zip 152K
VS2008 VB.Net, ASP.Net Solution - SMO Version 10 DynamicSQLDBUpdate_SMO10.zip 155K

Previous Tip    Tip Index    Next Tip

Home

Updated 2011/04/16