Database Build Wizard

Introduction

The database assembly build tool is a tool to allow a developer to select database schema items (tables, views, stored procedures, users, and so forth) and export the DDL to create these items to an SQL file. This file then can be run against another database to recreate the source schema. It is intended to be extensible to allow it to be adapted to any database platform and an SQL Server implementation is included in this article code to show this.

DataSchemaBase

The DataSchemaBase is the "contract" between the data schema provider and the database build wizard. It sets out a group of interfaces that the data schema provider must implement to generate the DDL.

IDataSource

The IDataSource class is the root of the data schema provider. It gives information about the capabilities of the data schema provider (does it support stored procedures, views, and the like?) and read-only collections of those database objects.

Public MustInherit Class IDataSourceBase

   Public MustOverride ReadOnly Property Indexes() _
          As IIndexCollection
'...8< ....
   Public MustOverride ReadOnly Property SupportsViews() _
          As Boolean
End Class

ITablebase

This defines what information the database wizard needs to know about a table—essentially the fields that make up the table and the permissions that are applicable to that table.

#Region "Fields"
   '\\ --[Fields]--------------------------------------------------
   '\\ The individual fields (aka columns) in this table
   '\\ ------------------------------------------------------------
   Public MustOverride ReadOnly Property Fields() _
          As IFieldCollection
#End Region

#Region "Permissions"
   '\\ --[Permissions]---------------------------------------------
   '\\ The users or user groups that have permissions on this table
   '\\ ------------------------------------------------------------
   Public MustOverride ReadOnly Property Permissions() _
          As IPermissionCollection
#End Region

In addition, it provides a read-only property, DDL, which is the SQL statement(s) that would be needed to re-create this table.

IViewBase and so forth

Each of the other database item types has a similar implementation class that is broadly like ITableBase.

SQLServerDataSchema

This DLL contains an SQL Server-specific implementation of the DataSchemaBase classes to allow you to extract the DDL for an SQL Server (or MSDE) database.

SQLServerDatasource

This implements the IDataSource interface for an SQL server database. Because this database platform supports all the database item types listed, it returns true for the SupportsStoredProcedures and related properties and has to provide an implementation of the Storedprocedures and related object collections.

SQLServerTableCollection

This type safe collection returns a list of all the tables in the selected database. It does this by querying the table sysobjects:

Public Class SQLServerTables
    Inherits ITableCollection
'..8< .....
    Public Sub New(ByVal dbConn As OleDb.OleDbConnection)
        '\\ Creates a collection of all the tables in the named
        '\\ database
        Const SQL_GETTABLES = "select name from sysobjects
                               where type = 'U'"
'..8<...
    End Sub
End Class

SQLServerTable

This class returns the DDL required to create the specific table. It does this with a hard-coded part (create table) and then iterates through the fields and permissions, appending the DDL for them.

There are similar classes implementing each of the other database object types (view, stored procedure, and the like)

DatabaseAssemblyWizard

This is a single form windows application that uses the WizardControl from www.Divil.co.uk to allow the developer to select and connect to a database, select the objects to be extracted, and lastly the target file name and then produce the DDL.

Plug-in data schema providers

The different types of data schema providers are read in from the application configuration file to allow you to add more providers without having to recompile the application.

   <dataSchemaProviders>
   <!-- The plug in providers that implement the DataSchemabase
        classes to write out the DDL for creating the database
        objects on the target database platform
        Key= The unique name that the provider is known by
        ImplementingAssembly = The assembly that holds the provider
        ImplementingClassType = The class that implements the
                                provider
     -->
     <dataSchemaProvidor Key="SQL Server"
                ImplementingAssembly="SQLServerDataSchema.dll"
                ImplementingClassType=
                   "SQLServerDataSchema.SQLServerDatasource"/>
   </dataSchemaProvidors>

Step 1: Connecting to the database

The first page in the wizard presents you with a textbox into which you put the ODBC connection string and a drop down box to select the data schema provider. Once these are both filled in, it goes off and gets all the DB items from the database selected. This may take a minute or two on a large database schema.

Step 2: Selecting the objects to extract

The second step is to select the tables, views, users, stored procedures, triggers, or user groups you want to extract. You need to have at least one object type selected before you can proceed to Step 3.

Step 3: Selecting the output

Step 3 is to select an SQL file name to write the DDL to. If the file already exists. you will be prompted whether or not to delete it. You then can use the output file with the OSQL command line to generate the data schema on the target database.



About the Author

Duncan Jones

Freelance developer with 10 years experience in Visual basic and SQL - now moving on up to the next generation with .NET

Downloads

Comments

  • Excel

    Posted by john.coneyworth@ttems.com on 11/15/2004 06:29am

    Hi
    Is there any way i can import an excel spreadsheet into a datagrid without using the windows clipboard

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Instead of only managing projects organizations do need to manage value! "Doing the right things" and "doing things right" are the essential ingredients for successful software and systems delivery. Unfortunately, with distributed delivery spanning multiple disciplines, geographies and time zones, many organizations struggle with teams working in silos, broken lines of communication, lack of collaboration, inadequate traceability, and poor project visibility. This often results in organizations "doing the …

  • Live Event Date: April 22, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Database professionals — whether developers or DBAs — can often save valuable time by learning to get the most from their new or existing productivity tools. Whether you're responsible for managing database projects, performing database health checks and reporting, analyzing code, or measuring software engineering metrics, it's likely you're not taking advantage of some of the lesser-known features of Toad from Dell. Attend this live …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds