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 sqlsql = SqlWriter.SimpleFormat(sql); //remove line breaksDebug.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