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>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>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;
}