Script object without specifying SQL Object type

gr8nashgr8nash Posts: 2
Hi all,

I am working on a migration tool using your awesome API. I am however stuck because every example i can find uses something like:

' Retrieve the script for a particular table
Dim regions As Regions = work.ScriptObject(widgetStaging.Tables(0), Options.Default)

I really would like to NOT have to look up an object type and pass that in. In the command line version there is an option "/Include:ALL" How can i do something similar with SQL Compare API?

I hope that question is clear enough

:)

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hello,

    You can script all database objects without knowing the specific type of object using a bit of .NET Reflection magic. It's a matter of getting the list of properties from the Database object and checking to see if they implement SerializableDatabaseObjectCollection and then trying to convert them into individual IDatabaseObject objects from their respective IEnumerable collections.
    Imports System.Collections.Generic
    Imports System.Linq
    Imports System.Text
    Imports RedGate.SQLCompare.Engine
    Imports System.Reflection
    Imports System.Collections
    Module Module1
    
        Sub Main()
            Dim d As New Database()
            d.Register(New ConnectionProperties("ps-briand\sql2008r2", "WidgetDev"), Options.[Default])
            Dim wrk As New Work()
            Dim t As Type = GetType(Database)
            For Each [property] As PropertyInfo In t.GetProperties()
                Dim baseType As Type = [property].PropertyType.BaseType
                If baseType IsNot Nothing AndAlso baseType.Name.StartsWith("SerializableDatabaseObjectCollection") Then
                    Dim rawObject As Object = [property].GetValue(d, Nothing)
                    For Each rawDBObject As Object In TryCast(rawObject, IEnumerable)
                        Dim dbObject As IDatabaseObject = TryCast(rawDBObject, IDatabaseObject)
                        Dim rgns As Regions = wrk.ScriptObject(dbObject, Options.[Default])
                        Console.WriteLine(rgns.ToString())
                    Next
    
                End If
            Next
            d.Dispose()
            Console.ReadLine()
        End Sub
    
    End Module
    
    Note I'm not a VB programmer and I can't vouch that the VB version of this works; I had just used a utility to convert it. Here is the original C# code:
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using RedGate.SQLCompare.Engine;
    using System.Reflection;
    using System.Collections;
    
    namespace SDKScriptAllObjects
    {
        class Program
        {
    
            static void Main(string[] args)
            {
                Database d = new Database();
                d.Register(new ConnectionProperties("ps-briand\\sql2008r2", "WidgetDev"),Options.Default);
                Work wrk = new Work();
                Type t = typeof(Database);
                foreach (PropertyInfo property in t.GetProperties())
                {
                    Type baseType = property.PropertyType.BaseType;
                    if (baseType != null && baseType.Name.StartsWith("SerializableDatabaseObjectCollection"))
                    {
                        object rawObject = property.GetValue(d,null);
                        foreach (object rawDBObject in (rawObject as IEnumerable))
                        {                  
                            IDatabaseObject dbObject = rawDBObject as IDatabaseObject;
                            Regions rgns=wrk.ScriptObject(dbObject, Options.Default);
                            Console.WriteLine(rgns.ToString());
                        }
                    }
                    
                }
                d.Dispose();
                Console.ReadLine();
            }
        }
    }
    
Sign In or Register to comment.