#region Using directives

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

#endregion

namespace SimpleSchemaEx
{
	class Program
	{
		static void Main(string[] args)
		{
			GetListOfTables("Pubs");
			GetListOfTables("NWAccess");
			TryRestrictions("Pubs");
		}

		public static void GetListOfTables(string name)
		{
      ConnectionStringSettings s = ConfigurationManager.ConnectionStrings[name];
      DbProviderFactory f = DbProviderFactories.GetFactory(s.ProviderName);

			using (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(s.ProviderName + "schema.xml");

					// this one gets all the tables
					DataTable t = conn.GetSchema("Tables");
					t.WriteXml(s.ProviderName + "tables.xml");
				}

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

		public static void TryRestrictions(string name)
		{
      ConnectionStringSettings s = ConfigurationManager.ConnectionStrings[name];
      DbProviderFactory f = DbProviderFactories.GetFactory(s.ProviderName);

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

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

				t[0] = conn.GetSchema();
				t[0].TableName = "Default";
				t[1] = conn.GetSchema(DbMetaDataCollectionNames.MetaDataCollections);
				t[2] = conn.GetSchema(DbMetaDataCollectionNames.Restrictions);
				t[3] = conn.GetSchema(DbMetaDataCollectionNames.DataSourceInformation);
				t[4] = conn.GetSchema(DbMetaDataCollectionNames.DataTypes);
				t[5] = conn.GetSchema(DbMetaDataCollectionNames.ReservedWords);
				ds.Tables.AddRange(t);
				ds.WriteXml("c:\\default.xml");


				DataTable tv = conn.GetSchema(DbMetaDataCollectionNames.Restrictions);
				DataView v = tv.DefaultView;
				v.RowFilter = "CollectionName = 'Columns'";
				v.Sort = "RestrictionNumber";
				for (int i = 0; i < v.Count; i++)
					Console.WriteLine("{0} (default){1}",
						tv.Rows[i]["RestrictionName"], tv.Rows[i]["RestrictionDefault"]);

				DataSet ds2 = new DataSet();
				// restriction string array
				string[] res = new string[4];
				// all columns, all tables owned by dbo
				res[1] = "dbo";
				DataTable t1 = conn.GetSchema("Columns", res);
				// clear collection
				for (int i = 0; i < 4; i++) res[i] = null;
				// all columns, all tables named "authors", any owner/schema
				res[2] = "authors";
				DataTable t2 = conn.GetSchema("Columns", res);
				// clear collection
				for (int i = 0; i < 4; i++) res[i] = null;
				// columns named au_lname 
				// all tables named "authors", any owner/schema
				res[2] = "authors";
				res[3] = "au_lname";
				DataTable t3 = conn.GetSchema("Columns", res);
				// clear collection
				for (int i = 0; i < 4; i++) res[i] = null;
				// columns named au_lname 
				// any tables, any owner/schema
				res[3] = "name";
				DataTable t4 = conn.GetSchema("Columns", res);
				// need not specify whole 
			}
		}
	}
}
