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