Database Schema Reader

╰+哭是因爲堅強的太久メ 2022-08-07 04:45 206阅读 0赞

下载地址： [http://dbschemareader.codeplex.com/releases/view/612307][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][UI Documentation]

[http://dbschemareader.codeplex.com/releases/view/612307][http_dbschemareader.codeplex.com_releases_view_612307]

[http_dbschemareader.codeplex.com_releases_view_612307]: http://dbschemareader.codeplex.com/releases/view/612307
[Install-Package DatabaseSchemaReader]: https://www.nuget.org/packages/DatabaseSchemaReader/
[Schema Reading]: http://dbschemareader.codeplex.com/wikipage?title=Schema%20Reading&referringTitle=Documentation
[Limitations]: http://dbschemareader.codeplex.com/wikipage?title=Limitations&referringTitle=Documentation
[AutoNumbering]: http://dbschemareader.codeplex.com/wikipage?title=AutoNumbering&referringTitle=Documentation
[SQL Generation]: http://dbschemareader.codeplex.com/wikipage?title=SQL%20Generation&referringTitle=Documentation
[Sql Conversion]: http://dbschemareader.codeplex.com/wikipage?title=Sql%20Conversion&referringTitle=Documentation
[Code Generation]: http://dbschemareader.codeplex.com/wikipage?title=Code%20Generation&referringTitle=Documentation
[Code First]: http://dbschemareader.codeplex.com/wikipage?title=Code%20First&referringTitle=Documentation
[Comparison]: http://dbschemareader.codeplex.com/wikipage?title=Comparison&referringTitle=Documentation
[Migrations]: http://dbschemareader.codeplex.com/wikipage?title=Migrations&referringTitle=Documentation
[Writing Data]: http://dbschemareader.codeplex.com/wikipage?title=Writing%20Data&referringTitle=Documentation
[UI Documentation]: http://dbschemareader.codeplex.com/wikipage?title=UI%20Documentation&referringTitle=Documentation
[Martinjw]: http://www.codeplex.com/site/users/view/Martinjw