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

The DatabaseSchemaReader can turn its model back into SQL.


You must supply an output SqlType- although it can read more databases, only the most common databases are supported for output.
These are: SqlServer, Oracle, MySql, SQLite, SqlServerCe, PostgreSql and DB2.

The SqlWriter will generate simple SQL queries and insert/updates.

const string providerName = "System.Data.SqlClient";
const string connectionString = @"Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=Northwind";
var dbReader = new DatabaseReader(connectionString, providerName);
dbReader.DataTypes(); //ensure we have datatypes (this doesn't hit the database)
var categoriesTable = dbReader.Table("Categories"); //this hits database for columns and constraints
//create a SqlWriter
var writer = new SqlWriter(categoriesTable, SqlType.SqlServer);
var selectAll = writer.SelectAllSql();

//You can also generate a paged select
var pagedSelect = writer.SelectPageSql();

var insert = writer.InsertSql();

 

Here's the output:

 

SELECT [CategoryID], [CategoryName], [Description], [Picture] FROM [Categories]

SELECT [CategoryID], [CategoryName], [Description], [Picture] FROM
 (SELECT ROW_NUMBER() OVER(ORDER BY [CategoryID]) AS rowNumber,
  [CategoryID],[CategoryName],[Description], [Picture] FROM [Categories]) AS countedTable WHERE
   rowNumber > (@pageSize * (@currentPage - 1))
   AND rowNumber <= (@pageSize * @currentPage)

INSERT INTO [Categories] ([CategoryName],[Description],[Picture])
 VALUES (@CategoryName,@Description, @Picture);
 SET @CategoryID = SCOPE_IDENTITY();

 

If we were reading from MySql we create the SqlWriter(categoriesTable, SqlType.MySql) and get this output:

 

SELECT `CategoryID`,`CategoryName`, `Description`, `Picture` FROM `northwind`.`Categories`

set @rownum:=0;
SELECT `CategoryID`,`CategoryName`,`Description`,`Picture` FROM
 (SELECT @rownum:= @rownum+1) as rowNumber,
  `CategoryID`, `CategoryName`,`Description`,`Picture` FROM `northwind`.`Categories`
  ORDER BY  `CategoryID`)
 AS countedTable WHERE
   rowNumber > (?pageSize * (?currentPage - 1))
   AND rowNumber <= (?pageSize * ?currentPage)

INSERT INTO `northwind`.`Categories` (`CategoryName`, `Description`,`Picture`) VALUES 
 (?CategoryName, ?Description, ?Picture);
SELECT LAST_INSERT_ID();

 


In addition to the simple insert SQL shown here, there are "InsertSqlIncludingIdentity" and "InsertSqlWithoutOutputParameter" methods.

Table DDL

DatabaseSchemaReader.SqlGen contains utilities for writing SQL data definitions. These utilities are available from the SqlGen.DdlGeneratorFactory. Again, you must specify the output type.

 

var gen = new DdlGeneratorFactory(SqlType.Oracle)
  .TableGenerator(table);
var txt = gen.Write();

 

The output (here in Oracle format):

 

CREATE TABLE "Categories"
(
  "CategoryID" NUMBER (9) NOT NULL,
  "CategoryName" NVARCHAR2 (15) NOT NULL,
  "Description" CLOB,
  "Picture" BLOB
);
ALTER TABLE "Categories" ADD CONSTRAINT "PK_Categories" PRIMARY KEY ("CategoryID");

The DatabaseSchemaReader may miss some advanced information about the table definition, for instance security or specific datatypes.

 

Migrations

The SqlGen.DdlGeneratorFactory can also generate common operations such as adding, changing or dropping tables or columns.

You don't have to read the database- simply create the schema model in memory, and the MigrationGenerator will create the SQL.

 

var migration = new DdlGeneratorFactory(SqlType.SqlServer).MigrationGenerator();
//some of the migrations
var createTable = migration.AddTable(table);
var addColumn = migration.AddColumn(table, newColumn);
var addUniqueConstraint = migration.AddConstraint(table, uniqueConstraint);
var dropUniqueIndex = migration.DropIndex(table, index);

Each of the methods returns a string of SQL.

Not all changes apply to all databases. For instance, in SQLite, you can't add or drop a constraint on an existing table, or change or drop a column.  In these cases, the MigrationGenerator just writes a comment. It can check foreign keys, so dropping a table will first try to drop the foreign keys (if a cascade syntax isn't available).

The MigrationGenerator does not have detailed rules for each database, for instance on what column datatypes can be changed.

Last edited Jul 16, 2011 at 7:28 AM by Martinjw, version 3

Comments

No comments yet.