Red Gate forums :: View topic - Script object without specifying SQL Object type
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK 10
SQL Comparison SDK 10 forum

Script object without specifying SQL Object type

Search in SQL Comparison SDK 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
gr8nash



Joined: 16 Jul 2012
Posts: 1

PostPosted: Mon Jul 16, 2012 10:23 pm    Post subject: Script object without specifying SQL Object type Reply with quote

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

Smile
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6581

PostPosted: Wed Jul 18, 2012 3:52 pm    Post subject: Reply with quote

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.
Code:
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:
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();
        }
    }
}
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group