Is there a generic way to loop through ALL database objects

rgribble

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.

Brian Donahue

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 Donahue

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