Click here to Skip to main content
Click here to Skip to main content
Go to top

JET to SQL Converter

, 31 Aug 2003
Rate this:
Please Sign up or sign in to vote.
A library to convert a JET database to SQL 'CREATE TABLE' statements

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Rowdy75
Engineer
Australia Australia
No Biography provided

Comments and Discussions

 
GeneralWow... a real fast plunge into .NET Pinmembergnatko14-Jan-04 19:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140922.1 | Last Updated 1 Sep 2003
Article Copyright 2003 by Rowdy75
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid