DatabaseSchemaReader 2.11.0

dotnet add package DatabaseSchemaReader --version 2.11.0
NuGet\Install-Package DatabaseSchemaReader -Version 2.11.0
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="DatabaseSchemaReader" Version="2.11.0" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add DatabaseSchemaReader --version 2.11.0
#r "nuget: DatabaseSchemaReader, 2.11.0"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install DatabaseSchemaReader as a Cake Addin
#addin nuget:?package=DatabaseSchemaReader&version=2.11.0

// Install DatabaseSchemaReader as a Cake Tool
#tool nuget:?package=DatabaseSchemaReader&version=2.11.0

General

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.

Supported databases include SqlServer, SqlServer Ce, Oracle (via Microsoft, ODP and Devart), MySQL, SQLite, Postgresql, DB2, Ingres, VistaDb and Sybase ASE/ASA/UltraLite. For .net Core, we support SqlServer, SqlServer CE 4, SQLite, PostgreSql, MySQL and Oracle (even before the database clients were available in .net Core).

Use

.net Core/6/7/8 (netStandard1.5)

//In .net Core, create the connection with the connection string
using (var connection = new SqlConnection("Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=Northwind"))
{
    var dr = new DatabaseSchemaReader.DatabaseReader(connection);
    //Then load the schema (this will take a little time on moderate to large database structures)
    var schema = dbReader.ReadAll();
}

Full .net framework (v3.5, v4.0, v4.5) ==

To use it simply specify the connection string and ADO provider (eg System.Data,SqlClient or System.Data.OracleClient)

const string providername = "System.Data.SqlClient";
const string connectionString = @"Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=Northwind";
//Create the database reader object.
var dbReader = new DatabaseReader(connectionString, providername);
//for Oracle, specify the Owner (Schema) as the full schema of an Oracle database is huge and will be very slow to load.
//var dbReader = new DatabaseReader("Data Source=XE;User Id=hr;Password=hr;", "System.Data.OracleClient", "HR");
//load the schema (this will take a little time on moderate to large database structures)
var schema = dbReader.ReadAll();

The DatabaseSchema object has a collection of tables, views, stored procedures, functions, packages and datatypes. Tables and views have columns, with their datatypes.

Code generation

//first the standard schema reader
const string providername = "System.Data.SqlClient";
const string connectionString = @"Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=Northwind";
var reader = new DatabaseReader(connectionString, providername);
//for Oracle, specify dbReader.Owner = "MyOwner";
//for .net Core, var reader = new DatabaseReader(new SqlConnection(connectionString));
var schema = reader.ReadAll();

//now write the code
var directory = new DirectoryInfo(Environment.CurrentDirectory);
var settings = new CodeWriterSettings
                   {
                       Namespace = "Northwind.Domain",
                       //CodeTarget = CodeTarget.Poco //default is POCO, or use EF Code First/NHibernate
                   };
var codeWriter = new CodeWriter(schema, settings);
codeWriter.Execute(directory);

SQL generation

//Simple SQL
var sqlWriter = new SqlWriter(schema.FindTableByName("ORDERS"), SqlType.PostgreSql);
var selectSql = sqlWriter.SelectPageStartToEndRowSql(); //and others...

//Script data INSERTs (not available in .net Core)
var sw = new DatabaseSchemaReader.Data.ScriptWriter {IncludeIdentity = true};
var inserts = sw.ReadTable("ORDERS", connectionString, providername);

Comparisons

You can compare the schemas of two databases to get a diff script.

//load your schemas - nb .net Core requires ADO connection object
var acceptanceDb = new DatabaseReader(connectionString, providername).ReadAll();
var developmentDb = new DatabaseReader(connectionString2, providername).ReadAll();

//compare
var comparison = new CompareSchemas(acceptanceDb, developmentDb);
var script = comparison.Execute(); //script to upgrade acceptanceDb into the same schema as developmentDb.

Migrations (low level)

//create a schema model
var dbSchema = new DatabaseSchema(null, SqlType.Oracle);
var table = dbSchema.AddTable("LOOKUP");
table.AddColumn<int>("Id").AddPrimaryKey().AddColumn<string>("Name").AddLength(30);
var newColumn = table.AddColumn("Updated", DbType.DateTime).AddNullable();
//create a migration generator
var factory = new DatabaseSchemaReader.SqlGen.DdlGeneratorFactory(SqlType.Oracle);
var migrations = factory.MigrationGenerator();
//turn the model into scripts
var tableScript = migrations.AddTable(table);
var columnScript = migrations.AddColumn(table, newColumn);
Product Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 was computed.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
.NET Core netcoreapp1.0 was computed.  netcoreapp1.1 was computed.  netcoreapp2.0 was computed.  netcoreapp2.1 was computed.  netcoreapp2.2 was computed.  netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
.NET Standard netstandard1.5 is compatible.  netstandard1.6 was computed.  netstandard2.0 is compatible.  netstandard2.1 was computed. 
.NET Framework net35 is compatible.  net40 is compatible.  net403 was computed.  net45 is compatible.  net451 was computed.  net452 was computed.  net46 was computed.  net461 was computed.  net462 was computed.  net463 was computed.  net47 was computed.  net471 was computed.  net472 was computed.  net48 was computed.  net481 was computed. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen tizen30 was computed.  tizen40 was computed.  tizen60 was computed. 
Xamarin.iOS xamarinios was computed. 
Xamarin.Mac xamarinmac was computed. 
Xamarin.TVOS xamarintvos was computed. 
Xamarin.WatchOS xamarinwatchos was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (10)

Showing the top 5 NuGet packages that depend on DatabaseSchemaReader:

Package Downloads
Inaction

.net core 快速开发框架,请下载Inaction.Web

Reportr

Reportr is a .NET template orientated reporting framework, designed from the ground up as a lightweight solution to solving various reporting problems faced in .NET applications.

r2rml4net

A C# implementation of R2RML, which allows creating R2RML and converting relational data to RDF using the .NET Framework

slp.evi.core

.NET implementation of an RDB2RDF storage based on R2RML mapping file

S2.SupplyCollector.Ado

SDK library for creating Second Sight supply collectors that are native to ADO.NET

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
2.11.0 600 4/29/2024
2.10.1 2,546 1/13/2024
2.10.0 2,270 11/22/2023
2.9.1 4,962 9/22/2023
2.9.0 706 9/9/2023
2.8.2 456 8/18/2023
2.8.1 8,319 4/13/2023
2.8.0 1,955 3/1/2023
2.7.17 8,000 11/12/2022
2.7.16 5,421 10/14/2022
2.7.15 550 10/6/2022
2.7.14 973 9/5/2022
2.7.13 453 9/5/2022
2.7.12 1,533 8/14/2022
2.7.11 20,856 10/25/2021
2.7.10 59,414 8/28/2021
2.7.9 1,030 7/30/2021
2.7.8 1,621 6/5/2021
2.7.7 946 5/13/2021
2.7.6 1,376 4/3/2021
2.7.5 9,666 3/20/2021
2.7.4 48,989 10/29/2020
2.7.3 14,528 6/18/2020
2.7.2 55,100 10/2/2019
2.7.1 22,806 1/12/2019
2.7.0 1,879 12/9/2018
2.6.4 70,746 11/17/2018
2.6.3 12,275 9/22/2018
2.6.2 1,720 9/2/2018
2.6.1 2,291 7/10/2018
2.5.1 10,036 4/15/2018
2.5.0 6,202 12/29/2017
2.4.0 13,881 10/28/2017
2.3.3 2,598 7/29/2017
2.3.2 3,907 6/6/2017
2.3.1 11,410 5/7/2017
2.3.0 2,265 2/4/2017
2.2.0 5,023 12/3/2016
2.1.1 2,660 8/20/2016
2.1.0 1,878 8/13/2016
2.0.0 2,723 7/23/2016
1.6.0.4 3,729 5/28/2016
1.5.0.5 3,913 3/14/2016
1.4.4 2,341 1/3/2016
1.4.3.11 2,034 11/21/2015
1.4.2.2 6,216 7/13/2015
1.4.2.1 2,041 7/12/2015
1.4.1.6 1,773 6/21/2015
1.4.0.32 5,912 3/29/2015
1.3.9 1,739 3/15/2015
1.3.8 1,864 3/8/2015
1.3.7 2,815 12/15/2014
1.3.6 2,621 10/24/2014
1.3.5 23,081 8/24/2014
1.3.4 2,555 7/27/2014
1.3.3 2,184 5/11/2014
1.3.2 1,989 4/7/2014
1.3.1 1,820 3/16/2014
1.3.0 1,886 2/4/2014
1.2.11 1,819 12/14/2013
1.2.10 2,213 8/3/2013
1.2.9 2,080 6/25/2013
1.2.8 2,136 5/12/2013
1.2.7 2,032 3/2/2013
1.2.6 1,872 2/2/2013
1.2.5 2,174 11/10/2012
1.2.4 2,068 10/23/2012

2.7.3: MySql time mapping thanks @tonyliving
2.7.4: Postgresql table list exclude views thanks @thomasjoscht
2.7.5: SqlLite Compound primary keys thanks @zls3201
2.7.6: PostgreSql MigrationGenerator cascade escaping thanks @groogiam
2.7.7: fixes null 'RefersToTable' of FK when only one table is read thanks @mjalil
2.7.8: PostgreSql sequences missing thanks @groogiam
2.7.9: PostgreSql sqlgen escaping thanks @r-work
2.7.11: CancellationToken leaking into net4 code thanks @KeithLRobertson
2.7.12: reader.ViewExists(name)
2.7.13: PostgreSql sql generation fixes thanks @Arnagos, @cm0201
2.7.15: PostgreSql sqlgen fix, CompareSchema fixes thanks @Amagos, @r-work
2.7.16: FK constraints allowed to unique indexes (not just PK/UK) thanks @Amagos
2.7.17: Procedure ResultSets don't have a DataType
2.8.0: Reads User Defined Types (Domain/composite types)
2.8.1: AddColumn can generate invalid sql (#161, thanks @Arnagos)
2.8.2: Fix mysql bool type (#164 , thanks @tonyliving); DdlGeneratorFactory add UseGranularBatching (#168 thanks @whibdev)
2.9.0: Add extensions on schema for simpler sql generation
2.9.1: Granular batching for indexes fix (#170), user defined types extensions (#171,thanks @whibdev)
2.10.0: Postgresql: support partial indexes (#140)
2.10.1: Postgresql: fix partial indexes generation (#140); sql server UDTs bug (#173)
2.11.0: SQLite: sqlGeneration of Drop Table If Exists (#179), multiple constraints on the same related table #184