How do I obtain a list of Collections which contain Objects which Implement an Interface in C# or VB.net

Question

I am new to .Net Programming and would like to know if Reflection is suitable for traversing an Object to discover Collections within an instance of a class.

I am working with the Microsoft.SqlServer.Management.Smo Namespace.

The plan is to connect to a database, determine what collections exists, then for each of those collections, if the Collection exposes objects which implement the IScriptable Interface, generate a script for the object.

How do i do the following (This is pseudo-code as I am seeking assistance with using reflection or some other method to do the following)

    Imports Microsoft.SqlServer.Management.Smo
    Imports Microsoft.SqlServer.Management.Smo.Agent
    Imports Microsoft.SqlServer.Management.Common

    Dim db as Database
    ...
' 1. How do I determine all Collections in the db Object I have created, which implement the IScriptable Interface.

    For each myCollection as Collection in db.Database.?Collections?
        For each collection_object in myCollection

       If collection_object.GetInterface("IScriptable") IsNot Nothing Then
          ScriptObjectCreate(collection_object, destFolder & "\" & TypeOf(collection_object).toString() & "\", False)
       End If
    Next
Next

So far I am able to connect to the database and script out Tables by doing the following.

For Each obj As Table In db.Tables
                If Not (obj.IsSystemObject) Then
                    ScriptObjectCreate(obj, destFolder & "\" & "Tables\", False)
                End If
Next

The reason I want to change this, is different versions of SQL Server will contain different collections of objects. Instead of writing code for each type.

For each obj as Table
For each obj as StoredProcedure 
For each obj as Trigger
etc...

I would like to enumerate all objects in db with one function if possible

Answer 1

It sounds like all you need is to determine if an object implements a given interface. If so then reflection is not the right tool. Instead you want to use the VB.Net TypeOf operator (it's meant for this very test).

If TypeOf collection_object is IScriptable Then 

Full example

For each myCollection as Collection in db.Database.Collections
   For each collection_object in myCollection
     if TypeOf collection_object is IScriptable Then ..
      ScriptObjectCreate(collection_object, destFolder & "\" & TypeOf(collection_object).toString() & "\", False)
     End If
   Next
Next

Answer 2

I'm afraid this is C# rather than VB.net but hopefully you'll be able to interpret it?

Server server = new Server();
Database database = server.Databases["ReportServer"];

foreach (PropertyInfo propertyInfo in typeof(Database).GetProperties())
{
    if (typeof(SchemaCollectionBase).IsAssignableFrom(propertyInfo.PropertyType))
    {
        SchemaCollectionBase collection = (SchemaCollectionBase)propertyInfo.GetValue(database, null);

        foreach (IScriptable item in collection)
        {
            PropertyInfo isSystemObjectPropertyInfo = item.GetType().GetProperty("IsSystemObject");

            if (isSystemObjectPropertyInfo == null || !(bool)isSystemObjectPropertyInfo.GetValue(item, null))
            {
                Console.WriteLine("{0} is scriptable and not a system object", item);
                // TODO: ScriptObjectCreate(item, destFolder + "\\" + item.GetType() + "\\", false);
            }
        }
    }
}

The key is to ue IsAssignableFrom to find the collection properties and then to only extract the objects in those collections that implement the IScripable interface (since the SMO collections are not generic, it's not possible to find ony those collections that contain scriptable objects).