#region Using directives

using System;
using System.Collections.Generic;
using System.Text;

#endregion

// SMO and broker
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo.Broker;

// for scripter
using System.Collections.Specialized;

namespace SMOLab
{
  class Program
  {
    static void Main(string[] args)
    {
      //GetProperties();
      //CreateServiceBrokerObjects();
      AdvScripter();
    }

    static void GetProperties()
    {
      Server svr = new Server();

      // set to true - fetch all fields of objects of type Table
      //svr.SetDefaultInitFields(typeof(Table), false);

      StringCollection coll = svr.GetDefaultInitFields(typeof(Table));
      foreach (String s in coll)
          Console.WriteLine(s);

      Database db = svr.Databases["pubs"];
      Table tab = db.Tables["jobs"];

      Console.WriteLine(db.Tables[0].Properties.Count);
      SparsePrintProperties(db.Tables[0]);
      Console.WriteLine(tab.CreateDate);
      PrintProperties(tab);
      Console.WriteLine(tab.DataSpaceUsed);
      PrintProperties(tab);
    }

    static void CreateServiceBrokerObjects()
    {
      try
      {
        Server srv = new Server();
        Database db = new Database(srv, "TestBroker");
        db.Create();

        // three message types - XML validation only
        MessageType[] mt = new MessageType[3];
        mt[0] = new MessageType(db.ServiceBroker, "http://www.SQLskills.com/messages/Invoice");
        mt[0].MessageTypeValidation = MessageTypeValidation.Xml;
        mt[0].Create();

        mt[1] = new MessageType(db.ServiceBroker, "http://www.SQLskills.com/messages/Payment");
        mt[1].MessageTypeValidation = MessageTypeValidation.Xml;
        mt[1].Create();

        mt[2] = new MessageType(db.ServiceBroker, "http://www.SQLskills.com/messages/NonPayment");
        mt[2].MessageTypeValidation = MessageTypeValidation.Xml;
        mt[2].Create();

        // Service Contract using three message types
        ServiceContract contract = new ServiceContract(db.ServiceBroker, "http://www.SQLskills.com/contracts/InvoiceContract/v1.0");

        MessageTypeMapping[] mtm = new MessageTypeMapping[3];

        mtm[0] = new MessageTypeMapping(contract, "http://www.SQLskills.com/messages/Invoice");
        mtm[0].MessageSource = MessageSource.Initiator;
        contract.MessageTypeMappings.Add(mtm[0]);

        mtm[1] = new MessageTypeMapping(contract, "http://www.SQLskills.com/messages/Payment");
        mtm[1].MessageSource = MessageSource.Target;
        contract.MessageTypeMappings.Add(mtm[1]);

        mtm[2] = new MessageTypeMapping(contract, "http://www.SQLskills.com/messages/NonPayment");
        mtm[2].MessageSource = MessageSource.Target;
        contract.MessageTypeMappings.Add(mtm[2]);

        contract.Create();

        // Two ServiceQueues
        ServiceQueue q = new ServiceQueue(db.ServiceBroker, "InvoiceQueue");
        q.Create();
        ServiceQueue q1 = new ServiceQueue(db.ServiceBroker, "SendInvoiceQueue");
        q1.Create();

        // Two Services, each one has a queue and contract
        BrokerService s = new BrokerService(db.ServiceBroker, "http://www.SQLskills.com/services/InvoiceService/v1.0");
        s.QueueName = "InvoiceQueue";
        ServiceContractMapping cmap = new ServiceContractMapping(s, "http://www.SQLskills.com/contracts/InvoiceContract/v1.0");
        s.ServiceContractMappings.Add(cmap);
        s.Create();

        BrokerService s1 = new BrokerService(db.ServiceBroker, "http://www.SQLskills.com/services/SendInvoiceService/v1.0");
        s1.QueueName = "SendInvoiceQueue";
        ServiceContractMapping cmap1 = new ServiceContractMapping(s1, "http://www.SQLskills.com/contracts/InvoiceContract/v1.0");
        s1.ServiceContractMappings.Add(cmap1);
        s1.Create();

        // example: see which properties available for the Service
        foreach (BrokerService service in db.ServiceBroker.Services)
          PrintProperties(service);

        // script the objects
        StringCollection coll;
        ScriptingOptions opts = new ScriptingOptions();
        opts.WithDependencies = false;
        foreach (BrokerService service in db.ServiceBroker.Services)
        {
          coll = service.Script(opts);
          foreach (string str in coll)
            Console.WriteLine(str);
        }
        
        db.Drop();
      }
      catch (Exception e)
      {
        Console.WriteLine(e.Message);
        if (e.InnerException != null)
          Console.WriteLine(e.InnerException.Message);
        Console.WriteLine(e.StackTrace);
      }
    }


    static void AdvScripter()
    {
      Server srv = new Server(".");
      Scripter scr = new Scripter(srv);
      StringCollection coll;

      scr.Options.IncludeDatabaseContext = true;
      scr.Options.IncludeHeaders = true;
      Urn[] urns = new Urn[1];

      // this MUST be the exact server name
      // localhost or '.' will not work
      urns[0] = new Urn("Server[@Name='ZMV17']/Database[@Name='pubs']/Table[@Name='titleauthor' and @Schema='dbo']");
      DependencyTree dt = scr.DiscoverDependencies(urns, DependencyType.Children);

      DependencyCollection dc = null;
      DependencyTreeNode dtn = null;

      dtn = dt.FirstChild;
      while (dtn.FirstChild != null)
        dtn = dtn.FirstChild;

      // remove last one
      dt.Remove(dtn);

      dc = scr.WalkDependencies(dt);
      scr.Options.DriForeignKeys = true;
      scr.Options.DriPrimaryKey = true;

      // remove last element
      dc.RemoveAt(dc.Count - 1);

      coll = scr.ScriptWithList(dc);

      foreach (string s in coll)
        Console.WriteLine(s);
    }

    static void SparsePrintProperties(SqlSmoObject obj)
    {
      foreach (Property p in obj.Properties)
      {
        Console.WriteLine("{0,-20}\t Expensive={1,-5}\t Retrieved={2}", p.Name, p.Expensive, p.Retrieved);
      }
    }

    static void PrintProperties(SqlSmoObject obj)
    {
      foreach (Property p in obj.Properties)
      {
        
        Console.WriteLine("{0,-20}\t Expensive={1,-5}\t Value={2}", p.Name, p.Expensive, p.Value);
      }
    }
  }
}
