Database Schema Reader

下载地址： http://dbschemareader.codeplex.com/releases/view/612307

Contents

• DatabaseSchemaReader.dll - Class library (.net3.5)
• DatabaseSchemaViewer.exe - UI to read and view database schemas; options to generate SQL and code; option to compare another schema
• CopyToSQLite/CopyToSQLite.exe - UI to copy any database schema and data to SQLite or, if installed, SQL Server CE 4.0
• net4/DatabaseSchemaReader.dll - .Net 4.0 class library

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)

API

• Schema Reading - reading the schema from the database
• Limitations - what this schema reader can and cannot do
• AutoNumbering - dealing with identity and sequences for autonumbering
• SQL Generation - generating SQL from a schema
• Sql Conversion - converting SQL DDL from one database to another
• Code Generation - generating C# classes and projects from a schema
• Code First - reverse engineering C# EF Code First projects from a schema
• Comparison - comparing two schemas and making a migration script
• Migrations - generating scripts to migrate database schemas
• Writing Data - creating a script of SQL "INSERT"s

UI

UI Documentation

Last edited Feb 4, 2014 at 4:15 PM by Martinjw, version 17

相关地址：

http://dbschemareader.codeplex.com/wikipage?title=UI%20Documentation&referringTitle=Documentation

http://dbschemareader.codeplex.com/releases/view/612307