JET to SQL Converter

• Download demo project + source - 21.9 Kb
• Download source - 9.44 Kb

Introduction

To help with the deployment of a database project, I have chosen to embed a blank Access database into the setup program.  The tables will then be added by calling SQL statements on the database.  I chose this course for two reasons:

1. The database is still in heavy production.  Committing each change to CVS is blowing out the size of the repository.  Instead I can just save the SQL commands as a single file.
2. If I need to upgrade the database in a production setting, I can diff between versions and can call ALTER TABLE statements on the existing database as part of the upgrade setup.

I still find it easier to use Access to design my database.  I searched the internet for a tool that could automate the transformation of the database to SQL statements.  When I couldn't find anything suitable, I decided to write my own.

Background

I chose to write the module in C# (I am trying to learn it at the moment).  It uses the OleDbConnection.GetOleDbSchemaTable command to extract schema information from the database, then parses the information to give a collection of 'Tables'.  These Tables are then written out in SQL commands.

The only major problem that I have found is that I could not identify whether a column was AutoNumber or not from the schema information returned.  I have assumed that any Primary Key with an Integer data type is an AutoNumber.

Since the module was written primarily for my own use, I have really only checked that it works with my database.  I also have successfully read and written the Northwind database.  If other information is required, have a look at the information returned by the various OleDbSchemaGuid values.

The code still requires a lot of work on the error/exception handling side of things.  It shouldn't effect the database you are running it against, but use it at your own risk!

Using the code

The class SQLWriter contains a single public method, GetSQLString().  To use the library, simply instantiate the class by passing the filename of the Access database, then call GetSQLString().  This function returns a string of the SQL commands needed to create the database tables.

For example:

// An example of using GetSQLString()
using JetDBReader;

class ConsoleApp {
  public static void Main(string[] args)
  {
    SQLWriter cl = new SQLWriter (@"c:\databases\northwind.mdb");
    
    Console.WriteLine(cl.GetSQLString());
  }
}

The class also uses a separate library, AlgorithmLib, that I have started.  At the moment it only contains a single function, TopologicalSort (probably highly unoptimised), but I hope to add any generic algorithms as I need them.

The example project includes a very simple WinForms project that basically allows you to choose an input file and output file.  No verification or exception handling is included.

Points of Interest

Coming from a total non-CS background (I am a Civil Engineer by trade), there are likely to be a lot of stylistic and syntactic problems with the code.  I am really interested in people's comments as to how I can improve my coding and design skills, hopefully so, by the time the next dot-com boom comes around, I might be up to a reasonable standard.  Designing code beats designing sewer systems any day!

History

• Version 0.1: First upload.