This project has moved and is read-only. For the latest updates, please go here.

When you use the SQL DDL writers (from SqlGen.DdlGeneratorFactory) you have to specify the output database: one of SqlServer, Oracle, MySql, SQLite, SqlServerCe, PostgreSql and DB2.

The output syntax does not have to be the same as the database that was read. You can read the schema of a SqlServer database and output the table DDL for Oracle - or any of the other output types.

 

//read SqlServer Northwind database
const string providerName = "System.Data.SqlClient";
const string connectionString = @"Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=Northwind";
var dbReader = new DatabaseReader(connectionString, providerName);
var schema = dbReader.ReadAll();

//write the tables for Oracle
var gen = new DdlGeneratorFactory(SqlType.Oracle).AllTablesGenerator(schemas);
gen.IncludeSchema = false; //we don't want "dbo." prefixes
var txt = gen.Write();
//txt contains CREATE TABLE for each Northwind table, which can be run on Oracle

Note the "AllTablesGenerator" (and the TableGenerator and MigrationGenerator) has an "IncludeSchema" property which you should use during conversions,

 

What it converts

  • Common datatypes
  • Name escaping (columns and tables)
  • Specific syntax (for instance to alter columns)
  • Identity primary keys

What it does not convert

  • Uncommon datatypes (it has some knowledge of specific datatypes like SERIAL in PostgreSql)
  • Check constraints (very simple check constraints may work, but the SQL syntax and functions are usually database platform specific)
  • Triggers
  • Stored procedures and functions

It will not round-trip table definitions very well (from SqlServer to Oracle to SqlServer). SQLite has only four (loosely applied) datatypes so converting from a SQLite database to one with many datatypes will not work well.

Sample output

SqlServer Northwind Categories table

 

CREATE TABLE [dbo].[Categories]
(
  [CategoryID] INT IDENTITY(1,1) NOT NULL,
  [CategoryName] NVARCHAR (15)  NOT NULL,
  [Description] NVARCHAR (MAX),
  [Picture] IMAGE
);
ALTER TABLE [dbo].[Categories] ADD CONSTRAINT [PK_Categories] PRIMARY KEY ([CategoryID]);

 

 

Oracle

 

CREATE TABLE "Categories"
(
  "CategoryID" NUMBER (9) NOT NULL,
  "CategoryName" NVARCHAR2 (15) NOT NULL,
  "Description" NCLOB,
  "Picture" BLOB
);
ALTER TABLE "Categories" ADD CONSTRAINT "PK_Categories" PRIMARY KEY ("CategoryID");
--additionally a SEQUENCE and TRIGGER are created for the identity pk

 

MySQL

 

CREATE TABLE `Categories`
(
  `CategoryID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `CategoryName` VARCHAR (15) NOT NULL,
  `Description` TEXT,
  `Picture` BLOB
);

 

SQLite

 

CREATE TABLE [Categories]
(
  [CategoryID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  [CategoryName] TEXT NOT NULL,
  [Description] TEXT,
  [Picture] BLOB
);

 

 

Last edited Jul 16, 2011 at 12:07 PM by Martinjw, version 1

Comments

No comments yet.