/// <summary>The get db object model.</summary>
/// <param name="connection">The connection.</param>
/// <returns></returns>
public override DbModelInstance GetDbObjectModel(string connection)
{
_connection = connection;
DbModelInstance model = new DbModelInstance();
DbProviderFactory factory = DbProviderFactories.GetFactory(ProviderName);
using (DbConnection dbConn = factory.CreateConnection())
{
dbConn.ConnectionString = connection;
dbConn.Open();
QueryTableNames(dbConn, model);
Dictionary <string, DbModelType> dbTypes = GetDbTypes(dbConn);
model.Types = dbTypes;
model.ProviderName = ProviderName;
model.ConnectionString = _connection;
// build all table info
foreach (DbModelTable table in model.Tables)
{
DataTable schemaTableKeyInfo = GetTableKeyInfo(dbConn, null, table.Name);
GetColumnsForTable(table, schemaTableKeyInfo, dbTypes);
}
// build FK relationships
foreach (DbModelTable table in model.Tables)
{
GetForeignKeyReferencesForTable(dbConn, table);
ProcessForeignKeyReferencesForTable(dbConn, table);
}
return(model);
}
}
/// <summary>The get db object model.</summary>
/// <param name="connection">The connection.</param>
/// <returns></returns>
public override DbModelInstance GetDbObjectModel(string connection)
{
_connection = connection;
DbModelInstance model = new DbModelInstance();
DbProviderFactory factory = DbProviderFactories.GetFactory(ProviderName);
using (DbConnection dbConn = factory.CreateConnection())
{
dbConn.ConnectionString = connection;
dbConn.Open();
QueryTableNames(dbConn, model);
Dictionary<string, DbModelType> dbTypes = GetDbTypes(dbConn);
model.Types = dbTypes;
model.ProviderName = ProviderName;
model.ConnectionString = _connection;
// build all table info
foreach (DbModelTable table in model.Tables)
{
DataTable schemaTableKeyInfo = GetTableKeyInfo(dbConn, null, table.Name);
GetColumnsForTable(table, schemaTableKeyInfo, dbTypes);
}
// build FK relationships
foreach (DbModelTable table in model.Tables)
{
GetForeignKeyReferencesForTable(dbConn, table);
ProcessForeignKeyReferencesForTable(dbConn, table);
}
return model;
}
}
public void TestSetup()
{
_service = new SqlCeSchemaService {ProviderName = _providerName};
_model = _service.GetDbObjectModel(_connStr);
_sqlSW = new StringWriter();
_sqlWriter = new SqlWriter();
}
public void nvelocity_with_dbmodel2()
{
DbModelInstance model = new DbModelInstance();
model.ConnectionString = "conn str";
model.ProviderName = "sql.foo";
DbModelTable table = new DbModelTable {Name = "MyTable"};
model.Add(table);
table.Add(new DbModelColumn {Name = "ID"});
table.Add(new DbModelColumn {Name = "FirstName"});
Dictionary<string, object> items = new Dictionary<string, object>();
items.Add("model", model);
string template =
@"Template Test ($num):
ConnectionString: ""$model.ConnectionString""
ProviderName: ""$model.ProviderName""
#foreach ($table in $model.Tables)
$table.Name
#foreach ($c in $table.Columns)
* $c.Name
#end
#end
";
string s = _formatter.Format(template, items);
Console.WriteLine(s);
Assert.That(s.Length, Is.GreaterThan(0));
}
/// <summary>Initializes a new instance of the <see cref="DbModelDependencyWalker"/> class.</summary>
/// <param name="model">The database model instance.</param>
public DbModelDependencyWalker(DbModelInstance model)
{
if (model == null)
{
throw new ArgumentNullException("model");
}
_model = model;
}
/// <summary>Initializes a new instance of the <see cref="DbModelDependencyWalker"/> class.</summary>
/// <param name="model">The database model instance.</param>
public DbModelDependencyWalker(DbModelInstance model)
{
if (model == null)
{
throw new ArgumentNullException("model");
}
_model = model;
}
/// <summary>The get table or view by name.</summary>
/// <param name="model">The model.</param>
/// <param name="tableName">The table name.</param>
/// <returns></returns>
protected DbModelTable GetTableOrViewByName(DbModelInstance model, string tableName)
{
DbModelTable tableOrView = model.FindTable(tableName);
if (tableOrView == null)
{
// check the views
tableOrView = model.FindView(tableName);
}
return tableOrView;
}
/// <summary>The query table names.</summary>
/// <param name="dbConn">The db conn.</param>
/// <param name="model">The model.</param>
private void QueryTableNames(DbConnection dbConn, DbModelInstance model)
{
using (var cmd = dbConn.CreateCommand())
{
cmd.CommandText = "SELECT table_name FROM information_schema.tables WHERE TABLE_TYPE = N'TABLE'";
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DbModelTable table = new DbModelTable();
table.Name = (string)reader["table_name"];
model.Add(table);
}
}
}
}
/// <summary>The query table names.</summary>
/// <param name="dbConn">The db conn.</param>
/// <param name="model">The model.</param>
private void QueryTableNames(DbConnection dbConn, DbModelInstance model)
{
using (var cmd = dbConn.CreateCommand())
{
cmd.CommandText = "SELECT table_name FROM information_schema.tables WHERE TABLE_TYPE = N'TABLE'";
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DbModelTable table = new DbModelTable();
table.Name = (string)reader["table_name"];
model.Add(table);
}
}
}
}
public override DbModelInstance GetDbObjectModel(string connection)
{
//_connection = connection;
DbModelInstance model = new DbModelInstance();
DbProviderFactory factory = DbProviderFactories.GetFactory(ProviderName);
using (DbConnection dbConn = factory.CreateConnection())
{
dbConn.ConnectionString = connection;
dbConn.Open();
DataTable tables = dbConn.GetSchema("Tables");
Dictionary <string, DbModelType> dbTypes = GetDbTypes(dbConn);
model.Types = dbTypes;
model.ProviderName = ProviderName;
model.ConnectionString = connection;
if (tables == null)
{
return(model);
}
DataView tablesDV = new DataView(tables, "", "OWNER, TABLE_NAME", DataViewRowState.CurrentRows);
foreach (DataRowView row in tablesDV)
{
string schemaName = SafeGetString(row.Row, "OWNER");
string tableName = SafeGetString(row.Row, "TABLE_NAME");
DbModelTable dbTable = new DbModelTable {
Schema = schemaName, Name = tableName
};
model.Add(dbTable);
DataTable schemaTableKeyInfo = GetTableKeyInfo(dbConn, schemaName, tableName);
GetColumnsForTable(dbTable, schemaTableKeyInfo, dbTypes);
}
DataTable views = dbConn.GetSchema("Views");
DataView viewsDV = new DataView(views, "", "OWNER, VIEW_NAME", DataViewRowState.CurrentRows);
foreach (DataRowView row in viewsDV)
{
string schemaName = SafeGetString(row.Row, "OWNER");
string tableName = SafeGetString(row.Row, "VIEW_NAME");
DbModelView dbTable = new DbModelView {
Schema = schemaName, Name = tableName
};
model.Add(dbTable);
DataTable schemaTableKeyInfo = GetTableKeyInfo(dbConn, schemaName, tableName);
GetColumnsForTable(dbTable, schemaTableKeyInfo, dbTypes);
}
// build FK relationships
if (model.Tables != null)
{
foreach (DbModelTable table in model.Tables)
{
GetForeignKeyReferencesForTable(dbConn, table);
ProcessForeignKeyReferencesForTable(dbConn, table);
}
}
// build FK relationships
if (model.Views != null)
{
foreach (DbModelView view in model.Views)
{
GetForeignKeyReferencesForTable(dbConn, view);
ProcessForeignKeyReferencesForTable(dbConn, view);
}
}
}
return(model);
}
/// <summary>Gets a database object model that represents the items defined by the <paramref name="connection"/>.</summary>
/// <param name="connection">The connection string.</param>
/// <returns>An instance of <see cref="DbModelInstance"/> describing the database.</returns>
public virtual DbModelInstance GetDbObjectModel(string connection)
{
_connection = connection;
DbModelInstance model = new DbModelInstance();
DbProviderFactory factory = DbProviderFactories.GetFactory(ProviderName);
try
{
using (DbConnection dbConn = factory.CreateConnection())
{
dbConn.ConnectionString = connection;
dbConn.Open();
DataTable tables = dbConn.GetSchema("Tables");
Dictionary <string, DbModelType> dbTypes = GetDbTypes(dbConn);
model.Types = dbTypes;
model.ProviderName = ProviderName;
model.ConnectionString = _connection;
if (tables == null)
{
return(model);
}
DataView tablesDV = new DataView(tables, "TABLE_TYPE='TABLE' OR TABLE_TYPE='BASE TABLE'", "TABLE_SCHEMA, TABLE_NAME", DataViewRowState.CurrentRows);
foreach (DataRowView row in tablesDV)
{
string schemaName = SafeGetString(row.Row, "TABLE_SCHEMA");
string tableName = SafeGetString(row.Row, "TABLE_NAME");
DbModelTable dbTable = new DbModelTable {
Schema = schemaName, Name = tableName
};
model.Add(dbTable);
DataTable schemaTableKeyInfo = GetTableKeyInfo(dbConn, schemaName, tableName);
GetColumnsForTable(dbTable, schemaTableKeyInfo, dbTypes);
}
DataView viewsDV = new DataView(tables, "TABLE_TYPE='VIEW'", "TABLE_SCHEMA, TABLE_NAME", DataViewRowState.CurrentRows);
foreach (DataRowView row in viewsDV)
{
string schemaName = SafeGetString(row.Row, "TABLE_SCHEMA");
string tableName = SafeGetString(row.Row, "TABLE_NAME");
DbModelView dbTable = new DbModelView {
Schema = schemaName, Name = tableName
};
model.Add(dbTable);
DataTable schemaTableKeyInfo = GetTableKeyInfo(dbConn, schemaName, tableName);
GetColumnsForTable(dbTable, schemaTableKeyInfo, dbTypes);
}
// build FK relationships
if (model.Tables != null)
{
foreach (DbModelTable table in model.Tables)
{
GetForeignKeyReferencesForTable(dbConn, table);
ProcessForeignKeyReferencesForTable(dbConn, table);
}
}
// build FK relationships
if (model.Views != null)
{
foreach (DbModelView view in model.Views)
{
GetForeignKeyReferencesForTable(dbConn, view);
ProcessForeignKeyReferencesForTable(dbConn, view);
}
}
}
}
catch (Exception)
{
// catch all for providers that are not implementing the schema info.
return(model);
}
return(model);
}
/// <summary>The exec load database details.</summary>
/// <returns>The exec load database details.</returns>
private bool ExecLoadDatabaseDetails()
{
bool populate = false;
string connection = string.Empty;
bool success = false;
try
{
_hostWindow.SetPointerState(Cursors.WaitCursor);
if (_metaDataService == null)
{
_metaDataService = DatabaseMetaDataService.Create(_services.Settings.ConnectionDefinition.ProviderName);
}
connection = _metaDataService.GetDescription();
populate = true;
}
catch (Exception exp)
{
string msg = string.Format(
"{0}\r\n\r\nCheck the connection and select 'Reset Database Connection'.",
exp.Message);
_hostWindow.DisplaySimpleMessageBox(_hostWindow.Instance, msg, "DB Connection Error");
_hostWindow.SetStatus(this, exp.Message);
}
finally
{
_hostWindow.SetPointerState(Cursors.Default);
}
if (populate)
{
try
{
_hostWindow.SetPointerState(Cursors.WaitCursor);
_model = _metaDataService.GetDbObjectModel(_services.Settings.ConnectionDefinition.ConnectionString);
}
finally
{
_hostWindow.SetPointerState(Cursors.Default);
}
BuildTreeFromDbModel(connection);
_hostWindow.SetStatus(this, string.Empty);
success = true;
}
else
{
_populated = false;
DatabaseTreeView.CollapseAll();
}
return success;
}
/// <summary>Gets a database object model that represents the items defined by the <paramref name="connection"/>.</summary>
/// <param name="connection">The connection string.</param>
/// <returns>An instance of <see cref="DbModelInstance"/> describing the database.</returns>
public virtual DbModelInstance GetDbObjectModel(string connection)
{
_connection = connection;
DbModelInstance model = new DbModelInstance();
DbProviderFactory factory = DbProviderFactories.GetFactory(ProviderName);
using (DbConnection dbConn = factory.CreateConnection())
{
dbConn.ConnectionString = connection;
dbConn.Open();
DataTable tables = dbConn.GetSchema("Tables");
Dictionary<string, DbModelType> dbTypes = GetDbTypes(dbConn);
model.Types = dbTypes;
model.ProviderName = ProviderName;
model.ConnectionString = _connection;
DataView tablesDV = new DataView(tables, "TABLE_TYPE='TABLE' OR TABLE_TYPE='BASE TABLE'", "TABLE_SCHEMA, TABLE_NAME", DataViewRowState.CurrentRows);
foreach (DataRowView row in tablesDV)
{
string schemaName = SafeGetString(row.Row, "TABLE_SCHEMA");
string tableName = SafeGetString(row.Row, "TABLE_NAME");
DbModelTable dbTable = new DbModelTable {Schema = schemaName, Name = tableName};
model.Add(dbTable);
DataTable schemaTableKeyInfo = GetTableKeyInfo(dbConn, schemaName, tableName);
GetColumnsForTable(dbTable, schemaTableKeyInfo, dbTypes);
}
DataView viewsDV = new DataView(tables, "TABLE_TYPE='VIEW'", "TABLE_SCHEMA, TABLE_NAME", DataViewRowState.CurrentRows);
foreach (DataRowView row in viewsDV)
{
string schemaName = SafeGetString(row.Row, "TABLE_SCHEMA");
string tableName = SafeGetString(row.Row, "TABLE_NAME");
DbModelView dbTable = new DbModelView {Schema = schemaName, Name = tableName};
model.Add(dbTable);
DataTable schemaTableKeyInfo = GetTableKeyInfo(dbConn, schemaName, tableName);
GetColumnsForTable(dbTable, schemaTableKeyInfo, dbTypes);
}
// build FK relationships
foreach (DbModelTable table in model.Tables)
{
GetForeignKeyReferencesForTable(dbConn, table);
ProcessForeignKeyReferencesForTable(dbConn, table);
}
// build FK relationships
foreach (DbModelView view in model.Views)
{
GetForeignKeyReferencesForTable(dbConn, view);
ProcessForeignKeyReferencesForTable(dbConn, view);
}
}
return model;
}
