Is there a generic way to loop through ALL database objects

rgribblergribble Posts: 88
edited August 7, 2006 7:32AM in SQL Toolkit Previous Versions
OK, so all objects whether they are tables, views, stored procedures, whatever, implement/inherit IDatabaseObject and it appears that all of the collections of objects on a Database such as Tables, Views, Roles etc are of type SerializableCollection

So is there a way to register the database, then iterate through every SerializbleCollection it has?

Currently it seems one would have to do:
Database db = new Database();
db.Register(new ConnectionProperties(server, dbname), Options.Default);
foreach (IDatabaseObject item in db.Tables)
{
	// Do something to the object
	Console.WriteLine("{0}\t{1}.{2}", item.ObjectType.ToString(), item.Owner, item.Name);
}

foreach (IDatabaseObject item in db.Roles)
{
	// Do something to the object
	Console.WriteLine("{0}\t{1}.{2}", item.ObjectType.ToString(), item.Owner, item.Name);
}

foreach (IDatabaseObject item in db.Views)
{
	// Do something to the object
	Console.WriteLine("{0}\t{1}.{2}", item.ObjectType.ToString(), item.Owner, item.Name);
}

What im looking for is something on the database object that returns all objects in a single SerializableCollection
foreach (IDatabaseObject item in db.GetAllObjects())
{
	// Do something to the object
	Console.WriteLine("{0}\t{1}.{2}", item.ObjectType.ToString(), item.Owner, item.Name);
}

or a collection of SerializableCollection objects
foreach (ISerializableCollection collection in db.GetAllObjectCollections())
{
	foreach (IDatabaseObject item in collection)
	{
		// Do something to the object
		Console.WriteLine("{0}\t{1}.{2}", item.ObjectType.ToString(), item.Owner, item.Name);
	}
}



If none exists, i suppose i could just implement my own function which returns a hashtable of all SerializableColelctions where the Key is the ObjectType and the value is the SerializableColelction of that object type. Similar to the following
public Hashtable GetAllDBObjects(RedGate.SQLCompare.Engine.Database db)
{
	Hashtable objects = new Hashtable();
	objects.Add(ObjectType.Table, db.Tables);
	objects.Add(ObjectType.Role, db.Roles);
	objects.Add(ObjectType.View, db.Views);

	// ... and so on

	return objects;
}

But no sense re-inventing the wheel if it's already been done and ive just missed it somehow.

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Ryan,

    I did look into this briefly but I got stuck at SerializableCollection. But it's relatively easy to get a list of all of the SerializableCollections that are properties of the Database object:
    using System.Reflection
    ...
    Database db1=new Database();
    			Database db2=new Database();
    
    			db1.Register(new ConnectionProperties(".", "WidgetStaging"), Options.Default);
    			db2.Register(new ConnectionProperties(".", "WidgetProduction"), Options.Default);
    			
    			Type t = db1.GetType();
    			PropertyInfo[] pi=t.GetProperties();
    			foreach (PropertyInfo p in pi) 
    			{
    				if (p.PropertyType.BaseType.Name=="SerializableCollection") Console.WriteLine(p.Name);
    			}
    
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi,

    A bit of expansion on this, to check that members of the SerializableCollection implement IDatabaseObject: (With some help from Richard!)
    Database db1=new Database(); 
    			Database db2=new Database(); 
    
    			db1.Register(new ConnectionProperties(".", "WidgetStaging"), Options.Default); 
    			db2.Register(new ConnectionProperties(".", "WidgetProduction"), Options.Default); 
              
    			Type t = db1.GetType(); 
    			PropertyInfo[] pi=t.GetProperties(); 
    			foreach (PropertyInfo p in pi) 
    			{ 
    				if (p.PropertyType.BaseType.Name=="SerializableCollection") { 
    					SerializableCollection sc = (SerializableCollection) p.GetValue(db1,null);
    					if (sc != null)
    					{
    						foreach (object i in sc) 
    						{
    							IDatabaseObject ido = i as IDatabaseObject;
    							if (ido == null) 
    							{
    								Console.WriteLine("{0} does not implement IDatabaseObject.",p.Name);
    								break;
    							}
    							else 
    							{
    								Console.WriteLine("{0} implements IDatabaseObject.",p.Name);
    								break;
    							}
                }
            }
        }
    }
    
Sign In or Register to comment.