Project Description
A simple, cross-database facade over .Net 2.0 DbProviderFactories to read database metadata.

Any ADO provider can be read  (SqlServer, SqlServer CE 4, MySQL, SQLite, System.Data.OracleClient, ODP, Devart, PostgreSql, DB2...) into a single standard model.

In Nuget Install-Package DatabaseSchemaReader

There are utilities and two simple UI projects demonstrating how to use the model:

  • View all data from any provider
  • Generate POCO classes for tables, and NHibernate or EF Code First mapping files
  • Generate simple ADO classes to use stored procedures
  • Generate table DDL (and translate to another SQL syntax, eg SqlServer to Oracle or SQLite)
  • Generate CRUD stored procedures (for SqlServer, Oracle, MySQL, DB2)
  • Copy a database schema and data from any provider (SqlServer, Oracle etc) to a new SQLite database (and, with limitations, to SqlServer CE 4)
  • Compare two schemas to generate a migration script

This is intended to be a tool for developers who use simple persistence databases on different platforms. It does not know advanced and provider-specific data types and concepts (geometry, tablespaces, enums).  For database specific support, use your database admin UI, schema comparison or conversion tools.


How to grab the schema:

1. Know your connection string and provider (here SqlServer; can be Oracle, SQLite, MySQL, PostgreSql, DB2, Sybase, Firebird, Ingres, Cache, VistaDB...)

const string providername = "System.Data.SqlClient";
const string connectionString = @"Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=Northwind";

2. Write two lines of code

var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();

3. Use the simple schema model!

foreach (var table in schema.Tables)
{
Debug.WriteLine("Table " + table.Name);

foreach (var column in table.Columns)
{
Debug.Write("\tColumn " + column.Name + "\t" + column.DataType.TypeName);
if (column.DataType.IsString) Debug.Write("(" + column.Length + ")");
if (column.IsPrimaryKey) Debug.Write("\tPrimary key");
if (column.IsForeignKey) Debug.Write("\tForeign key to " + column.ForeignKeyTable.Name);
Debug.WriteLine("");
}
//Table Products
// Column ProductID int Primary key
// Column ProductName nvarchar(40)
// Column SupplierID int Foreign key to Suppliers
// Column CategoryID int Foreign key to Categories
// Column QuantityPerUnit nvarchar(20)
// Column UnitPrice money
// Column UnitsInStock smallint
// Column UnitsOnOrder smallint
// Column ReorderLevel smallint
// Column Discontinued bit
}

Simple code-generation

foreach (var column in table.Columns)
{
//C# properties (the column name could be made .Net friendly too)
Debug.WriteLine("\tpublic " + column.DataType.NetDataTypeCsName + " " + column.Name + " { get; set; }");
}
// public int ProductID { get; set; }
// public string ProductName { get; set; }

 

Using code generator for POCO classes and NHibernate/ EF CodeFirst mapping.

var directory = new DirectoryInfo(Environment.CurrentDirectory);
 var codeWriterSettings = new CodeWriterSettings
{
// or CodeTarget.PocoNHibernateFluent or CodeTarget.PocoEntityCodeFirst
  CodeTarget = CodeTarget.PocoNHibernateHbm,
Namespace = "Northwind.Domain"
 };
var
codeWriter = new CodeWriter(schema, settings);
codeWriter.Execute(directory );

 

Simple SQL generation

var sqlWriter = 
new SqlWriter(table, DatabaseSchemaReader.DataSchema.SqlType.SqlServer);
var sql = sqlWriter.SelectPageSql(); //paging sql
sql = SqlWriter.SimpleFormat(sql); //remove line breaks

Debug.WriteLine(sql);
//SELECT [ProductID], [ProductName], ...etc...
//FROM
//(SELECT ROW_NUMBER() OVER( ORDER BY [ProductID]) AS
//rowNumber, [ProductID], [ProductName], ...etc..
//FROM [Products]) AS countedTable
//WHERE rowNumber >= (@pageSize * (@currentPage - 1))
//AND rowNumber <= (@pageSize * @currentPage)

 

Caveats:

  • The UIs are very simple, to demonstrate how to use the library. You can also use the features from the integration tests.
  • Reading the schema isn't very fast, and this library makes no attempt to be efficient.
  • The code was originally written in 2005 to explore the DbProviderFactory.GetSchema methods of .Net 2.0. There are VS 2008 and VS 2010 (2012/2013) projects, and much of the code is still in the .Net 2 style. The tests are are mostly integration tests depending on standard installations and databases (Northwind, HR).
  • The code generation and SQL generation are rudimentary. The conversion code which reads Oracle tables and writes SqlServer DDL - and vice versa -, is very basic. It will not convert your stored procedures! Conversion and generation will only understand the most common features of your database, not advanced platform specific features.

Last edited Feb 4 at 6:55 PM by Martinjw, version 14