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:

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.

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.

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.

Believe it or not, early this morning I knew nothing about .NET... I just needed something to covert the structure of an .MDE to MS SQL. I downloaded this tool and it didn't work. This is the way I was driven to download the .NET framework 1.1 and now the tool works fine! Kinda fast migration to .NET)) I'm downloading the SDK now.

There was only one problem that I figured out: my .MDE file has tables and fields named in Russian. sqljet writes them down in Unicode, which is unreadable in a plain text file. In Russia, we usually convert OLE strings (where Russian chars are in Unicode double-char) to single-byte char strings when exporting somewhere. You could know that, anyway.

Generally, thanks for a quick solution to my problem and a good start with .NET!

FxCop is a code analysis tool that checks .NET managed code assemblies for conformance to the Microsoft .NET Framework Design Guidelines. It uses reflection, MSIL parsing, and callgraph analysis to inspect assemblies for more than 200 defects in the following areas: naming conventions, library design, localization, security, and performance.

I've written something similiar that I used to convert my access database to mysql since I couldn't find a free one. Works pretty well except it can't handle binary data such as type BLOB. At some stage or another I'd like to incorporate more database types to convert to-and-from. But yeah....when I have time :P.

year, but it sucks - or do you like the column "upsize_ts" and so on ...

# THIS CODE AND INFORMATION ARE PROVIDED # "AS IS" WITHOUT WARRANTY OF ANY# KIND, EITHER EXPRESSED OR IMPLIED,# INCLUDING BUT NOT LIMITED TO THE# IMPLIED WARRANTIES OF MERCHANTABILITY# AND/OR FITNESS FOR A PARTICULAR PURPOSE.# http://www.lennybacon.com/

Yeah, but that's not something you can wrap into a class or component and run as a "conversion factory". I do a lot of work with a place that has tons of MDBs and DBFs, and they need to be aggregated into MSSQL. This kind of tool will do the trick.