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

Source also on github

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);
//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"
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

//SELECT [ProductID], [ProductName], ...etc...
//rowNumber, [ProductID], [ProductName], ...etc..
//FROM [Products]) AS countedTable
//WHERE rowNumber >= (@pageSize * (@currentPage - 1))
//AND rowNumber <= (@pageSize * @currentPage)



  • 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 Mar 29, 2015 at 1:41 PM by Martinjw, version 15