To read a database schema you need a valid connection string and ADO provider (e.g. 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";

Then load the schema (this will take a little time on moderate to large database structures)

var dbReader = new DatabaseReader(connectionString, providername);
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.

Unlike the GetSchema method, there are no datatables, and the structure is identical for all providers.

Oracle and Schema/Owner

The code for reading Oracle is almost identical. I could use Microsoft's OracleClient, Oracle's ODP, Devart or DataDirect as providers, simply by changing the provider name (and connection string according to the capabilities of the provider).

 

const string providername = "Oracle.DataAccess.Client";
const string connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=XE)));User Id=HR;Password=HR;";

var dbReader = new DatabaseReader(connectionString, providername);
//IMPORTANT - set the Owner for Oracle!
dbReader.Owner = "HR";
var schema = dbReader.ReadAll();

In Oracle, every database object is owned by a schema owner. This is equivalent to the schema in SqlServer (it's usually "dbo" but the AdventureWorks sample database has different schemas). If you don't specify an owner for Oracle, it will select every database object for every owner- which includes all the system objects. Even in an empty database, this is a huge amount of data and will be very slow.

 

MySQL

Here's the code to read the sakila sample database from MySql. For convenience, we're specifying the owner "sakila".

 

const string providername = "MySql.Data.MySqlClient";
const string connectionString = @"Server=localhost;Uid=root;Pwd=mysql;Database=sakila;Allow User Variables=True;";

var dbReader = new DatabaseReader(connectionString, providername);
dbReader.Owner = "sakila";
var schema = dbReader.ReadAll();

 

DB2

Here we use DB2.

 

const string providername = "IBM.Data.DB2";
const string connectionString = @"Server=localhost:50000;UID=db2admin;pwd=db2;Database=Sample";

var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();

 

And many others

Just specify the provider, connection string and possibly the owner, and you can read many databases, getting a standard schema object.

It has been tested with:

  • SqlServer,
  • Oracle (with Microsoft, ODP, Devart and DataDirect drivers) ,
  • MySql (MySql and Devart providers),
  • PostgreSql (with Npgsql and Devart drivers),
  • SQLite,
  • SqlServerCe 4,
  • DB2,
  • Firebird,
  • Intersystems Cache
  • Ingres
  • Sybase AnyWhere (ASA)
  • Sybase UltraLite
  • Sybase ASE
  • Access 97 and Access 2007
  • VistaDB

Referencing the provider

You do not have to take a direct reference to the ADO provider. Most providers will be installed on your machine and will work "out of the box" because the references are installed in machine.config.

If the provider isn't installed, you do need to reference the provider dll, and you must change the app.config so .Net knows how to find it.

 

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite"/>
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite"
 type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
    </DbProviderFactories>
  </system.data>
</configuration>

Programmatic provider references

You can add the reference dynamically, without config. There is a static class called FactoryTools which allows you to "inject" a reference to a DbProviderFactory (as from v1.5, March 2016).

Attach a DbProviderFactoryRepository (closely based on Sandrino Di Mattia's code) and add a description, indicating the standard ADO provider factory class. You can then load the schema by passing in the invariant name.

FactoryTools.ProviderRepository = new DbProviderFactoryRepository();
var manualDescription = new DbProviderFactoryDescription
{
    Description = ".NET Framework Data Provider for SuperDuperDatabase",
    InvariantName = "SuperDuperDatabase",
    Name = "SuperDuperDatabase Data Provider",
    AssemblyQualifiedName = typeof(SuperDuperProviderFactory).AssemblyQualifiedName,
};
FactoryTools.ProviderRepository.Add(manualDescription);
var dr = new DatabaseReader("Data Source=localhost;""SuperDuperDatabase"); 
var tables = dr.ReadAll();

You can also load dlls from a folder dynamically. This will discover dlls containing ADO provider factories and make them loadable- the invariant name is the namespace of the factory class. (Code thanks to th0masj0)

FactoryTools.ProviderRepository = new DbProviderFactoryRepository();
FactoryTools.ProviderRepository.LoadExternalDbProviderAssemblies(Environment.CurrentDirectory);

 

Last edited Mar 12, 2016 at 4:02 PM by Martinjw, version 8

Comments

No comments yet.