public void OracleManagedClient()
{
//tested using
//<configuration>
// <system.data>
// <DbProviderFactories>
// <remove invariant="Oracle.ManagedDataAccess.Client" />
// <add name="ODP.NET, Managed Driver"
// invariant="Oracle.ManagedDataAccess.Client"
// description="Oracle Data Provider for .NET, Managed Driver"
// type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.121.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
// </DbProviderFactories>
// </system.data>
//</configuration>
const string providername = "Oracle.ManagedDataAccess.Client";
const string connectionString = ConnectionStrings.OracleHr;
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
dbReader.Owner = "HR";
var schema = dbReader.ReadAll();
var employees = schema.FindTableByName("EMPLOYEES");
Assert.AreEqual(11, employees.Columns.Count);
var table = dbReader.Table("EMPLOYEES");
Assert.AreEqual(11, table.Columns.Count);
}
public void FirebirdTest()
{
// <system.data>
// <DbProviderFactories>
// <add
// name="Firebird Data Provider"
// invariant="FirebirdSql.Data.FirebirdClient" description="Firebird"
// type="FirebirdSql.Data.FirebirdClient.FirebirdClientFactory, FirebirdSql.Data.FirebirdClient, Version=2.5.2.0, Culture=neutral, PublicKeyToken=3750abcc3150b00c"
// />
// </DbProviderFactories>
// </system.data>
const string providername = "FirebirdSql.Data.FirebirdClient";
const string path = @"C:\Program Files\Firebird\Firebird_2_1\examples\empbuild\EMPLOYEE.FDB";
const string connectionString = "User=SYSDBA;Password=masterkey;Database=" + path + ";Server=localhost; Connection lifetime=15;Pooling=true";
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();
var employees = schema.FindTableByName("EMPLOYEE");
Assert.AreEqual(11, employees.Columns.Count);
var table = dbReader.Table("EMPLOYEE");
Assert.AreEqual(11, table.Columns.Count);
}
public void OleDb()
{
const string providername = "System.Data.OleDb";
const string connectionString = "Provider=msdaora;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=XE)));User Id=hr;Password=hr;";
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
//exclude a lot of system sprocs that get picked up. This speeds us up massively.
dbReader.Exclusions.StoredProcedureFilter = new PrefixFilter("APEX", "ANY", "AGGR", "AQ$", "BLAST",
"CTX", "DBMS", "DM_", "DBU", "DEVELOP", "DICT", "DIV", "DIU", "DRI", "DRV", "HTF",
"FTP", "FUNCSTATS", "HS$", "HH", "HTML", "HTP", "HTTP", "KUP", "LCR", "MVAGG",
"ODC", "ODM", "OGC", "OLAP", "ORA", "OUTLN", "OWA", "PLIT", "PRIVAT", "PRVT",
"RE$", "SCHEDULER$", "SDO", "SERVER_", "SQL_", "ST_", "STANDARD", "SYS_", "TBLAST", "TFM", "TRANSFORM_",
"URI", "UTL_", "WPG_", "WPIUTL", "WRI$", "WWV", "XDB", "XML");
dbReader.Owner = "HR";
var schema = dbReader.ReadAll();
var employees = schema.FindTableByName("EMPLOYEES");
Assert.AreEqual(11, employees.Columns.Count);
var table = dbReader.Table("EMPLOYEES");
Assert.AreEqual(11, table.Columns.Count);
}
private static DatabaseReader GetNortwindReader()
{
const string providername = "System.Data.SqlClient";
const string connectionString = ConnectionStrings.Northwind;
ProviderChecker.Check(providername, connectionString);
return(new DatabaseReader(connectionString, providername));
}
public void DiscoverDb2Schemas()
{
const string providername = "IBM.Data.DB2";
const string connectionString = @"Server=localhost:50000;UID=db2admin;pwd=db2;Database=Sample";
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
var schemas = dbReader.AllSchemas();
Assert.IsTrue(schemas.Count > 0);
}
public static void Config(TestContext context)
{
//nuget install FirebirdSql.Data.FirebirdClient
// <system.data>
// <DbProviderFactories>
// <add name="FirebirdClient Data Provider" invariant="FirebirdSql.Data.FirebirdClient"
// description = ".NET Framework Data Provider for Firebird"
// type = "FirebirdSql.Data.FirebirdClient.FirebirdClientFactory, FirebirdSql.Data.FirebirdClient" />
// </DbProviderFactories>
// </system.data>
ProviderChecker.Check(ProviderName, ConnectionString);
}
public void IngresTest()
{
const string providername = "Ingres.Client";
//you may need to add User Id=x;pwd=p (windows account)
const string connectionString = "Host=localhost;database=demodb;";
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();
Assert.IsTrue(schema.Tables.Count > 0);
}
public void Db2Test()
{
const string providername = "IBM.Data.DB2";
const string connectionString = @"Server=localhost:50000;UID=db2admin;pwd=db2;Database=Sample";
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();
var table = schema.FindTableByName("EMPLOYEE");
Assert.IsTrue(table.Columns.Count > 0);
}
public void MySqlSchemasTest()
{
const string providername = "MySql.Data.MySqlClient";
var connectionString = ConnectionStrings.MySql;
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
dbReader.Owner = "sakila";
var schemas = dbReader.AllSchemas();
Assert.IsTrue(schemas.Count > 0, "Schemas should contain sakila");
}
public void SybaseUltraLiteTest()
{
const string providername = "iAnyWhere.Data.UltraLite";
//default sample location on Windows 7
//In .Net 4 this isn't added to machine.config, so add the DbProvider in app.config
const string connectionString = @"DBF=C:\Users\Public\Documents\SQL Anywhere 12\Samples\UltraLite.NET\CustDB\custdb.udb";
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();
Assert.IsTrue(schema.Tables.Count > 0);
}
public void SybaseAnyWhereTest()
{
const string providername = "iAnyWhere.Data.SQLAnyWhere";
const string connectionString = "Data Source=SQL Anywhere 12 Demo";
ProviderChecker.Check(providername, connectionString);
DatabaseSchemaReader.Utilities.DiscoverProviderFactory.Discover(connectionString, providername);
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();
Assert.IsTrue(schema.Tables.Count > 0);
}
public void MySqlTableTest()
{
const string providername = "MySql.Data.MySqlClient";
var connectionString = ConnectionStrings.MySql;
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
var country = dbReader.Table("country");
Assert.AreEqual(3, country.Columns.Count);
Assert.IsNotNull(country.PrimaryKeyColumn);
Assert.IsTrue(country.FindColumn("country_id").IsPrimaryKey);
}
public static void Config(TestContext context)
{
// <system.data>
// <DbProviderFactories>
// <add
// name="Firebird Data Provider"
// invariant="FirebirdSql.Data.FirebirdClient" description="Firebird"
// type="FirebirdSql.Data.FirebirdClient.FirebirdClientFactory, FirebirdSql.Data.FirebirdClient, Version=2.5.2.0, Culture=neutral, PublicKeyToken=3750abcc3150b00c"
// />
// </DbProviderFactories>
// </system.data>
ProviderChecker.Check(ProviderName, ConnectionString);
}
public void SybaseAseTest()
{
//using pubs3 with default sa account with Ase Developer Edition 15 on localhost (had to use IP address to get it to connect)
const string providername = "Sybase.Data.AseClient";
const string connectionString = "Server=192.168.1.100;Port=5000;Uid=sa;Pwd='';Initial Catalog=pubs3";
ProviderChecker.Check(providername, connectionString);
DatabaseSchemaReader.Utilities.DiscoverProviderFactory.Discover(connectionString, providername);
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();
Assert.IsTrue(schema.Tables.Count > 0);
}
public void VistaDbTest()
{
//using VistaDb 4.2.18.4 (trial) with samples in default location
const string providername = "System.Data.VistaDB";
const string connectionString = @"Data Source='C:\Users\Public\Documents\VistaDB\Databases\TicketSystemSample.vdb4'";
ProviderChecker.Check(providername, connectionString);
//DatabaseSchemaReader.Utilities.DiscoverProviderFactory.Discover(connectionString, providername);
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();
Assert.IsTrue(schema.Tables.Count > 0);
}
public void TestNpgsql()
{
//using the MySql world database ported to Postgres
const string providername = "Npgsql";
const string connectionString = @"Server=127.0.0.1;User id=postgres;password=sql;database=world;";
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
dbReader.Owner = "public"; //otherwise you have "postgres" owned tables and views
var schema = dbReader.ReadAll();
var orders = schema.FindTableByName("country");
Assert.AreEqual(15, orders.Columns.Count);
}
public void TestNpgsql()
{
//using the MySql world database ported to Postgres
const string providername = "Npgsql";
var connectionString = ConnectionStrings.PostgreSql;
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
dbReader.Owner = "public"; //otherwise you have "postgres" owned tables and views
var schema = dbReader.ReadAll();
var country = schema.FindTableByName("country");
Assert.IsTrue(country.Columns.Count > 0);
}
public void TestDevartPostgreSql()
{
//http://www.devart.com/dotconnect/postgresql/docs/MetaData.html
const string providername = "Devart.Data.PostgreSql";
var connectionString = ConnectionStrings.PostgreSql;
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
dbReader.Owner = "public"; //otherwise you have "postgres" owned tables and views
var schema = dbReader.ReadAll();
var country = schema.FindTableByName("country");
Assert.IsTrue(country.Columns.Count > 0);
}
public void TestDevartPostgreSql()
{
//http://www.devart.com/dotconnect/postgresql/docs/MetaData.html
const string providername = "Devart.Data.PostgreSql";
const string connectionString = @"Server=127.0.0.1;User id=postgres;password=sql;database=world;";
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
dbReader.Owner = "public"; //otherwise you have "postgres" owned tables and views
var schema = dbReader.ReadAll();
var orders = schema.FindTableByName("country");
Assert.AreEqual(15, orders.Columns.Count);
}
public void MySqlTest()
{
const string providername = "MySql.Data.MySqlClient";
const string connectionString = @"Server=localhost;Uid=root;Pwd=mysql;Database=sakila;Allow User Variables=True;";
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();
var orders = schema.FindTableByName("country");
Assert.AreEqual(3, orders.Columns.Count);
var table = dbReader.Table("city");
Assert.AreEqual(4, table.Columns.Count);
}
public void SqlServerCe4Test()
{
if (!File.Exists(FilePath))
{
Assert.Inconclusive("SqlServerCe4 test requires database file " + FilePath);
}
const string connectionString = "Data Source=\"" + FilePath + "\"";
ProviderChecker.Check(ProviderName, connectionString);
var dbReader = new DatabaseReader(connectionString, ProviderName);
var schema = dbReader.ReadAll();
var orders = schema.FindTableByName("Orders");
Assert.IsTrue(orders.Columns.Count > 2); //we don't care if it's not standard Northwind
}
public void SqlServerAdventureWorks()
{
const string providername = "System.Data.SqlClient";
const string connectionString = @"Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=AdventureWorks";
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();
var product = schema.FindTableByName("Product");
Assert.IsTrue(product.Columns.Count >= 17); //different versions of AdventureWorks have different # columns
var table = dbReader.Table("Product");
Assert.IsTrue(table.Columns.Count >= 17);
}
public void DevartSqlServer()
{
const string providername = "Devart.Data.SqlServer";
const string connectionString = @"Data Source=localhost\SQLEXPRESS;Integrated Security=true;Initial Catalog=AdventureWorks";
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername, 0);
var schema = dbReader.ReadAll();
var product = schema.FindTableByName("Product");
Assert.AreEqual(25, product.Columns.Count);
var table = dbReader.Table("Product");
Assert.AreEqual(25, table.Columns.Count);
}
public void MySqlTest()
{
const string providername = "MySql.Data.MySqlClient";
var connectionString = ConnectionStrings.MySql;
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();
var country = schema.FindTableByName("country");
Assert.AreEqual(3, country.Columns.Count);
Assert.IsNotNull(country.PrimaryKeyColumn);
var table = dbReader.Table("city");
Assert.AreEqual(4, table.Columns.Count);
}
public void DataDirectSqlServer()
{
//not sql express
const string providername = "DDTek.SQLServer";
const string connectionString = @"Server=localhost;AuthenticationMethod=NTLM;DatabaseName=AdventureWorks";
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername, 0);
var schema = dbReader.ReadAll();
var product = schema.FindTableByName("Product");
Assert.AreEqual(25, product.Columns.Count);
var table = dbReader.Table("Product");
Assert.AreEqual(25, table.Columns.Count);
}
public void DataDirectOracle()
{
const string providername = "DDTek.Oracle";
const string connectionString = "Host=localhost;Service Name=XE;User Id=HR;Password=HR;";
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername, 0);
dbReader.Owner = "HR";
var schema = dbReader.ReadAll();
var employees = schema.FindTableByName("EMPLOYEES");
Assert.AreEqual(11, employees.Columns.Count);
var table = dbReader.Table("EMPLOYEES");
Assert.AreEqual(11, table.Columns.Count);
}
public void MySqlUnsignedIntegersTest()
{
const string providername = "MySql.Data.MySqlClient";
var connectionString = ConnectionStrings.MySql;
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();
var country = schema.FindTableByName("country");
var pk = country.PrimaryKeyColumn;
Assert.IsNotNull(pk, "Primary key constraints should be loaded");
Assert.AreEqual(pk.DbDataType, "smallint(5) unsigned");
Assert.AreEqual(pk.DataType.TypeName, "SMALLINT");
}
public void ReadNorthwindUsingOdbc()
{
//you'll get much more information from System.Data.SqlClient
const string providername = "System.Data.Odbc";
const string connectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=Northwind;Trusted_Connection=Yes;";
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername)
{
Owner = "dbo"
};
//this is slow because it pulls in sp_ stored procedures and system views.
dbReader.Exclusions.StoredProcedureFilter = new PrefixFilter("sp_", "fn_", "dm_", "xp_");
var schema = dbReader.ReadAll();
Assert.IsTrue(schema.Tables.Count > 0);
}
public void Oracle()
{
const string providername = "System.Data.OracleClient";
const string connectionString = ConnectionStrings.OracleHr;
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
dbReader.Owner = "HR";
var schema = dbReader.ReadAll();
var employees = schema.FindTableByName("EMPLOYEES");
Assert.AreEqual(11, employees.Columns.Count);
var table = dbReader.Table("EMPLOYEES");
Assert.AreEqual(11, table.Columns.Count);
}
public void DevartOracle()
{
const string providername = "Devart.Data.Oracle";
const string connectionString = "Server=localhost;Sid=XE;Port=1521;Direct=true;User Id=hr;Password=hr;";
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername, 0);
dbReader.Owner = "HR";
var schema = dbReader.ReadAll();
var employees = schema.FindTableByName("EMPLOYEES");
Assert.AreEqual(11, employees.Columns.Count);
var table = dbReader.Table("EMPLOYEES");
Assert.AreEqual(11, table.Columns.Count);
}