SQL Generation

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.

From 2.9.0 (September 2023), there are extensions that make sql generation easier. See below

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.

From 2.8.2 you can change the DDL with UseGranularBatching. In SqlServer, statements can be terminated with semi-colon, or with a batching statement, "GO". If you use a sql database project (sqlproj), and want to turn the script into a dacpac, you need the GO after every statement.

var ddlGeneratorFactory = new DdlGeneratorFactory(SqlType.SqlServer);
//standard. For SqlServer, statements end with semicolon, with GO when required
var ddl = ddlGeneratorFactory.AllTablesGenerator(schema).Write();

//for SqlServer, a "GO" after every statement
 ddlGeneratorFactory.UseGranularBatching = true;
 var dllWithBatching = ddlGeneratorFactory.AllTablesGenerator(schema).Write();

Migrations

See Database 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.

ToSql* extensions

These were added in v2.9.0 (September 2023). Internally they use the methods described above.

First, add the using statement to make the extensions visible:

using DatabaseSchemaReader.SqlGen;

using (var connection = new SqlConnection(connectionString))
{
    var dr = new DatabaseSchemaReader.DatabaseReader(connection);
    var schema = dr.ReadAll();
    //find your table
    var table = schema.FindTableByName("Products");

    //then grab your sql!
    var selectSql = table.ToSqlSelectById();
    var insertSql = table.ToSqlSelectInsert();
    var createTableSql = table.ToSqlCreateTable();
    var foreignKeysSql = table.ToSqlCreateForeignKeys();

    //you can also get a Poco .net class
    var code = table.ToClass();

    //you can create all tables in the schema
    var createAllTablesSql = schema.ToSqlCreateTables();
}

The full list of extensions:

• DatabaseSchema
  • schema.ToSqlCreateTables();
• DatabaseTable
  • table.ToSqlCreateTable();
  • table.ToSqlCreateForeignKeys();
  • table.ToSqlSelectById();
  • table.ToSqlSelectPaged();
  • table.ToSqlSelectInsert();
  • table.ToSqlSelectUpdate();
  • table.ToClass();
• DatabaseColumn
  • column.ToSqlAddColumn();
  • column.ToSqlDropColumn();
• DatabaseConstraint
  • constraint.ToSqlAddConstraint(table); //NB add the table

You can control the sql generated by adding an optional SqlGenerationParameters

var sqlGenPars = new SqlGenerationParameters { 
//table and column names escaped (eg [Table])
EscapeNames = false, 
//include the schema (eg dbo)
IncludeSchema = false, 
//for sqlServer, uses GO instead of ; between every statement
UseGranularBatching = true 
};
var sql = schema.ToSqlCreateTables(sqlGenPars);