VB.Net - Database Build wizard

[Merrion]

There are a number of components to this application...I am adding them one at a a time to this thread and explaining each in turn....

Purpose:
The database build wizard allows you to extract objects from a database (tbales, users, views, groups etc.) and save them as seperate script files or as a .Net resources file which can be run into a traget database using an installer.
The wizard is designed to be extensible so that it can work with any database platform (once you write a data schema component for that database). I have written data schema providors for SybaseXI and SQL Server (also works with MSDE).

Part 1 : The data schema base
This describes the functionality that is required by the data schema providors. This is done as a set of MustInherit classes....

[Merrion]

Part 2: The Sybase data schema providor
This implements the data schema base for a Sybase XI database.

Part 3: The SQL serverdata schema providor
This implements the data schema base for a SQL Server database.

These are the meat of the application, containing logic to extract the SQL for creating objects (tables, users, views, user groups etc.) for the two database platforms.

[Merrion]

Part 4 : The database build wizard
This is the main application.
It consists of a single form using the excellent "wizard" control from www.divil.co.uk

Using this you select a data schema providor (from the drop down list) and fill in the ODBC connection string.
In step 2 you select the objects (users, tables, views etc...) that you want to extract from the checked list
In step 3 you specify the output location and whether you want the output to be scripts or a resource file.

[Merrion]

To use this, compile up the first three components and copy the dlls to the \bin directory in the application.

Any issues or suggestions for improvement, let me know...

[techgnome]

Hmmmm, odd. I can't compile the first project.... tells me that it can't find the "DataSchemaBase.snk" file??

Tg

[Merrion]

Ah - in all the projects you need to change the file AssemblyInfo.vb

It has a line like:-

<Assembly: AssemblyKeyFile("DataSchemabase.snk")>

Add the path to the file in your machine..i.e. if the project is in c:\my projects\vbforums\DataSchemaBase it should be changed to:

<Assembly: AssemblyKeyFile("c:\my projects\vbforums\DataSchemaBase\DataSchemaBase.snk")>

This is a known issue with Visual Studio.

[techgnome]

Ok, got it to compile. It's now telling me that my ODBC connection string isn't valid. I've back track it to this point in the SQLServerDatasource.vb project:

Public Overrides WriteOnly Property ConnectionString() As String
        Set(ByVal Value As String)
            If SQLServerDatasource.TraceSettings.TraceInfo Then
                Trace.WriteLine("SQLServerDatasource.ConnectionString = " & Value, Me.GetType.ToString)
            End If
 
            try 
                _dbconn = New OleDb.OleDbConnection(Value)
            catch e as exception
 
            end try
        End Set
    End Property

Value is set correctly, but _dbconn is still nothing. Even "e" is nothing which would indicate that all was OK. Something else that stands out is that try...catch..end try isn't capitalizing nore does intellisense work any where in the vb file. I could really use this on a project that I'm working on, and would like to get it going. Uh... using SQL Server 2000 (local), Win XP, SQL Server auth. Any clues?

Tg

[Merrion]

Your ODBC string needs to be something like:-

Provider=SQLOLEDB;Persist Security Info=False;Integrated Security=SSPI;database=MyDatabase;server=(local);Connect Timeout=60

Naturally change MyDatabase to the name of the database you are connecting to.

[techgnome]

Your ODBC string needs to be something like:-

Provider=SQLOLEDB;Persist Security Info=False;Integrated Security=SSPI;database=MyDatabase;server=(local);Connect Timeout=60

Naturally change MyDatabase to the name of the database you are connecting to.

D'oh! I had {SQL Server} as the provider.... not oldeDB.... will give it a go. Thanks for all your help!

Tg

[Merrion]

code fix...
The fields collection was not being output in the correct order. This is fixed.