public void RunSqlite()
{
using (var connection = new System.Data.SQLite.SQLiteConnection("Data Source=:memory:"))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "CREATE TABLE Test(Value);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Test VALUES(1);";
cmd.ExecuteNonQuery();
}
using (var cmd = connection.CreateCommand())
{
cmd.CommandText =
"SELECT [type], [name], [tbl_name], [rootpage], [sql], [rowid] FROM [main].[sqlite_master] WHERE [type] LIKE 'table'";
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
System.Console.WriteLine(dr[2].ToString());
}
}
}
var databaseReader = new DatabaseSchemaReader.DatabaseReader(connection);
var tableList = databaseReader.TableList();
Assert.IsTrue(tableList.Count > 0);
}
}
public void AdventureWorksTest()
{
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);
DatabaseSchema schema = null;
try
{
schema = dbReader.ReadAll();
}
catch (SqlException exception)
{
Assert.Inconclusive("Cannot access database " + exception.Message);
}
var directory = TestHelper.CreateDirectory("AdventureWorks");
const string @namespace = "AdventureWorks.Domain";
var settings = new CodeWriterSettings { Namespace = @namespace, CodeTarget = CodeTarget.Poco, WriteStoredProcedures = true };
var codeWriter = new CodeWriter(schema, settings);
codeWriter.Execute(directory);
var procedures = directory.GetDirectories("Procedures").FirstOrDefault();
if (procedures == null)
Assert.Fail("Could not find Procedures subdirectory for stored procedures");
var files = procedures.GetFiles("*.cs");
var category = files.First(f => f.Name == "uspLogError.cs");
var cs = File.ReadAllText(category.FullName);
var ok = cs.Contains("public virtual DbCommand CreateCommand(int? errorLogId)");
Assert.IsTrue(ok, "Should contain the uspLogError stored procedure (in standard AdventureWorks db)");
}
public void SqlTypeTest()
{
var dr = new DatabaseReader("Dummy", SqlType.SqlServer);
Assert.AreEqual("System.Data.SqlClient", dr.DatabaseSchema.Provider);
//the other types will fail if they aren't installed
}
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);
}
static void Main()
{
const string providername = "System.Data.SqlClient";
const string connectionString = @"Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=Northwind";
Console.WriteLine("Reading Northwind");
var dr = new DatabaseReader(connectionString, providername);
var schema = dr.ReadAll();
Console.WriteLine("Schema read");
var directory = CreateDirectory();
Console.WriteLine("Writing to directory " + directory.FullName);
if (WriteNHibernate(directory, schema) &&
WriteFluentNHibernate(directory, schema) &&
WriteCodeFirst(directory, schema) &&
BuildRunner(directory))
{
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("All done");
}
else
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Build failed");
Console.ResetColor();
Console.ReadKey();
}
}
private static DatabaseTable LoadCategoriesFromNorthwind()
{
const string providername = "System.Data.SqlClient";
var connectionString = ConnectionStrings.Northwind;
ProviderChecker.Check(providername, connectionString);
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();
return schema.FindTableByName("Categories");
}
/// <summary>
/// Gets the SqlServer NorthWind reader.
/// </summary>
/// <returns></returns>
public static DatabaseReader GetNorthwindReader()
{
const string providername = "System.Data.SqlClient";
var connectionString = ConnectionStrings.Northwind;
ProviderChecker.Check(providername, connectionString);
var northwindReader = new DatabaseReader(connectionString, providername);
northwindReader.Owner = "dbo";
return northwindReader;
}
private DatabaseTable LoadTable()
{
if (_table != null) return _table;
ProviderChecker.Check(ProviderName, ConnectionString);
var dbReader = new DatabaseReader(ConnectionString, ProviderName);
dbReader.DataTypes(); //ensure we have datatypes (this doesn't hit the database)
_table = dbReader.Table("STAFF"); //this hits database for columns and constraints
return _table;
}
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);
}
private DatabaseTable LoadCountryFromSakila()
{
if (_categoriesTable != null) return _categoriesTable;
ProviderChecker.Check(ProviderName, _connectionString);
var dbReader = new DatabaseReader(_connectionString, ProviderName);
dbReader.DataTypes(); //ensure we have datatypes (this doesn't hit the database)
_categoriesTable = dbReader.Table("country"); //this hits database for columns and constraints
return _categoriesTable;
}
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);
}
private DatabaseTable LoadRegionsFromHr()
{
if (_regionsTable != null) return _regionsTable;
ProviderChecker.Check(ProviderName, ConnectionString);
var dbReader = new DatabaseReader(ConnectionString, ProviderName);
dbReader.Owner = "HR";
dbReader.DataTypes(); //ensure we have datatypes (this doesn't hit the database)
_regionsTable = dbReader.Table("REGIONS"); //this hits database for columns and constraints
return _regionsTable;
}
private DatabaseTable LoadTable()
{
if (_table != null) return _table;
ProviderChecker.Check(ProviderName, _connectionString);
var dbReader = new DatabaseReader(_connectionString, ProviderName);
dbReader.Owner = "public"; //otherwise you have "postgres" owned tables and views
dbReader.DataTypes(); //ensure we have datatypes (this doesn't hit the database)
_table = dbReader.Table("country"); //this hits database for columns and constraints
return _table;
}
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 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 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 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);
DiscoverProviderFactory.Discover(connectionString, providername);
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();
Assert.IsTrue(schema.Tables.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 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);
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 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);
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 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);
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 static void MakeHbm()
{
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();
var directory = new DirectoryInfo("C:\\Temp\\Models.NorthwindIB.NH");
var settings = new CodeWriterSettings
{
// or CodeTarget.PocoNHibernateFluent or CodeTarget.PocoEntityCodeFirst
CodeTarget = CodeTarget.PocoNHibernateHbm,
Namespace = "Models.NorthwindIB.NH"
};
var codeWriter = new CodeWriter(schema, settings);
codeWriter.Execute(directory);
}
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);
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 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);
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 OracleOdp()
{
const string providername = "Oracle.DataAccess.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 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
}
private DatabaseTable LoadCategoriesFromNorthwind()
{
if (_categoriesTable != null) return _categoriesTable;
if (!File.Exists(_databaseFile))
Assert.Inconclusive("SQLite database file not found: " + _databaseFile);
ProviderChecker.Check(ProviderName, _connectionString);
var dbReader = new DatabaseReader(_connectionString, ProviderName);
dbReader.DataTypes(); //ensure we have datatypes (this doesn't hit the database)
_categoriesTable = dbReader.Table("Categories"); //this hits database for columns and constraints
if (_categoriesTable == null)
Assert.Inconclusive("Could not load Categories table from SQLite file");
return _categoriesTable;
}
public ImportResults <T> Import <T>(string[] parameters)
{
string databaseType = parameters[0];
string connectionString = parameters[1];
System.Data.Common.DbConnection connection;
switch (databaseType)
{
case "sqlserver":
connection = new SqlConnection(connectionString); //@"Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=Northwind");
break;
case "mysql":
connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString); //(@"Server=localhost;Uid=root;Pwd=Password12!;Database=sakila;Allow User Variables=True;");
break;
default:
throw new Exception("Database Type " + databaseType + " not recognised");
}
var app = new App();
app.Modules = new List <Module>();
var allEntities = new List <Entity>();
try
{
connection.Open();
app.Name = connection.Database;
var databaseReader = new DatabaseSchemaReader.DatabaseReader(connection);
//Then load the schema (this will take a little time on moderate to large database structures)
var schema = databaseReader.ReadAll();
foreach (var table in schema.Tables)
{
var module = app.Modules.FirstOrDefault(findModule => findModule.Name == table.SchemaOwner);
if (module == null)
{
module = new Module();
module.Name = table.SchemaOwner;
module.Entities = new List <Entity>();
app.Modules.Add(module);
}
var entity = new Entity();
entity.PropertyGroups = new List <PropertyGroup>();
module.Entities.Add(entity);
allEntities.Add(entity);
entity.Name = table.Name;
var propertyGroup = new PropertyGroup();
propertyGroup.Properties = new List <Property>();
entity.PropertyGroups.Add(propertyGroup);
propertyGroup.Name = "Details";
var keyPropertyGroup = new PropertyGroup();
entity.PropertyGroups.Add(keyPropertyGroup);
keyPropertyGroup.Name = "Keys";
foreach (var column in table.Columns)
{
var property = new Property();
property.Name = column.Name;
if (column.Length.HasValue)
{
property.MaxLength = column.Length.Value;
}
switch (column.DataType.NetDataType)
{
case "System.Byte":
property.DataType = DataType.Integer;
property.DataTypeFormat = DataTypeFormat.Byte;
break;
case "System.Boolean":
property.DataType = DataType.Boolean;
break;
case "System.Int32":
property.DataType = DataType.Integer;
property.DataTypeFormat = DataTypeFormat.Integer;
break;
case "System.Long":
property.DataType = DataType.Integer;
property.DataTypeFormat = DataTypeFormat.LongInteger;
break;
case "System.DateTime":
property.DataType = DataType.DateTime;
break;
case "System.DateTimeOffset":
property.DataType = DataType.TimeSpan;
break;
case "System.Decimal":
case "System.Numeric":
property.DataType = DataType.Number;
break;
case "System.Guid":
property.DataType = DataType.Guid;
break;
case "System.String":
property.DataType = DataType.String;
break;
default:
property.DataType = DataType.String;
break;
}
if (column.IsPrimaryKey || column.IsForeignKey)
{
keyPropertyGroup.Properties.Add(property);
}
else
{
propertyGroup.Properties.Add(property);
}
}
}
foreach (var table in schema.Tables)
{
var childEntity = allEntities.FirstOrDefault(findEntity => findEntity.Name == table.Name);
var propertyGroup = childEntity.PropertyGroups.FirstOrDefault(findPropertyGroup => findPropertyGroup.Name == "Details");
foreach (var foreignKey in table.ForeignKeys)
{
//var parentEntity = AllEntities.FirstOrDefault(findEntity => findEntity.Name == foreignKey.TableName);
var parentProperty = new Property();
propertyGroup.Properties.Add(parentProperty);
parentProperty.Name = foreignKey.Name;
parentProperty.DataType = DataType.Parent;
parentProperty.ParentName = foreignKey.RefersToSchema + "." + foreignKey.RefersToTable;
parentProperty.ForeignKeyName = foreignKey.Columns.First();
}
}
}
catch (Exception exception)
{
throw exception;
}
finally
{
connection.Close();
}
var importResults = new ImportResults <T>();
T t;
t = (T)Convert.ChangeType(app, typeof(T));
importResults.Result = t;
importResults.FilePath = "";
return(importResults);
}
