#region Using directives

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.ProviderBase;
using System.Configuration;
using System.Collections.Specialized;

#endregion

namespace CustomSchemas
{
	class Program
	{
		static void Main(string[] args)
		{
			ConnectionStringSettings s = ConfigurationManager.ConnectionStrings["Pubs"];
			DbProviderFactory f = DbProviderFactories.GetFactory(s.ProviderName);

			using (DbConnection conn = f.CreateConnection())
			{
				//DbConnection conn = f.CreateConnection();
				conn.ConnectionString = s.ConnectionString;
				conn.Open();

				try
				{
					// this gets all the schemas this provider knows about
					DataTable st = conn.GetSchema();
					st.WriteXml("c:\\metadata.xml");

					DataSet ds = new DataSet();
					DataTable[] t = new DataTable[4];

					t[0] = conn.GetSchema("Queues");
					t[1] = conn.GetSchema("Services");
					t[2] = conn.GetSchema("Contracts");
					t[3] = conn.GetSchema("MessageTypes");
					ds.Tables.AddRange(t);
					ds.WriteXml("c:\\broker.xml");

					string[] qres = new string[2];
					qres[0] = "dbo";
					qres[1] = null;   // "ServiceBrokerQueue";
					DataTable qt = conn.GetSchema("Queues", qres);
					qt.WriteXml("c:\\queues.xml");

					string[] sres = new string[2];
					sres[0] = "sa";
					sres[1] = "http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService";
					DataTable st2 = conn.GetSchema("Services", sres);
					st2.WriteXml("c:\\services.xml");

					string[] res = new string[2];
					res[0] = null;
					res[1] = "http://schemas.microsoft.com/SQL/Notifications/PostEventNotification";
					DataTable rt = conn.GetSchema("Contracts", res);
					rt.WriteXml("c:\\contracts.xml");

					string[] mres = new string[3];
					mres[0] = "sa";
					mres[1] = "XML";
					mres[2] = "http://schemas.microsoft.com/SQL/ServiceBroker/Error";
					DataTable mt = conn.GetSchema("MessageTypes", mres);
					mt.WriteXml("c:\\messagestypes.xml");
				}

				catch (Exception ex)
				{
					Console.WriteLine(ex.Message);
					Console.WriteLine(ex.StackTrace);
				}
			}
		}
	}
}
