Click here to Skip to main content
15,867,308 members
Articles / Programming Languages / C#
Article

JET to SQL Converter

Rate me:
Please Sign up or sign in to vote.
4.00/5 (9 votes)
31 Aug 20033 min read 95.5K   2.3K   26   15
A library to convert a JET database to SQL 'CREATE TABLE' statements

Image 1

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:

C#
// 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


Written By
Engineer
Australia Australia
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralDetermine wether a field is an autonumber field Pin
fantomas6231-Aug-06 1:00
fantomas6231-Aug-06 1:00 
GeneralWow... a real fast plunge into .NET Pin
gnatko14-Jan-04 19:23
gnatko14-Jan-04 19:23 
GeneralUseful code... Pin
Paul Evans8-Dec-03 23:46
Paul Evans8-Dec-03 23:46 
GeneralConformance to the Microsoft .NET Framework Design Guidelines Pin
Rui Holdorf3-Sep-03 8:30
Rui Holdorf3-Sep-03 8:30 
GeneralRe: Conformance to the Microsoft .NET Framework Design Guidelines Pin
Rui Holdorf3-Sep-03 8:31
Rui Holdorf3-Sep-03 8:31 
GeneralRe: Conformance to the Microsoft .NET Framework Design Guidelines Pin
Paul Evans8-Dec-03 23:45
Paul Evans8-Dec-03 23:45 
QuestionCan I connect to a SQL Server database for the schema? Pin
John M. Drescher2-Sep-03 23:54
John M. Drescher2-Sep-03 23:54 
GeneralNot Flashy, But... Pin
J. Dunlap1-Sep-03 10:27
J. Dunlap1-Sep-03 10:27 
GeneralRe: Not Flashy, But... Pin
antidemon2-Sep-03 23:36
antidemon2-Sep-03 23:36 
There is a SQL Server upgrade wizard available from Microsoft which will also do this.

GeneralRe: Not Flashy, But... Pin
John M. Drescher2-Sep-03 23:50
John M. Drescher2-Sep-03 23:50 
GeneralRe: Not Flashy, But... Pin
Damage_Inc3-Sep-03 1:41
Damage_Inc3-Sep-03 1:41 
GeneralRe: Not Flashy, But... Pin
antidemon3-Sep-03 3:14
antidemon3-Sep-03 3:14 
GeneralRe: Not Flashy, But... Pin
John M. Drescher3-Sep-03 3:59
John M. Drescher3-Sep-03 3:59 
GeneralRe: Not Flashy, But... Pin
Daniel Fisher (lennybacon)28-Oct-03 1:59
Daniel Fisher (lennybacon)28-Oct-03 1:59 
GeneralRe: Not Flashy, But... Pin
IgDev24-Oct-03 3:53
IgDev24-Oct-03 3:53 

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

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