Script object without specifying SQL Object type

Comments

• Options

  Brian Donahue Posts: 6,590 Bronze 1

  July 18, 2012 10:52AM

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

  0 · Share on Twitter