#region Using directives

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

#endregion

using System.Data.Common;
using System.Data;
using System.Configuration;

namespace Generic
{
  class Program
  {
    static void Main(string[] args)
    {
      DbConnection conn = null;

      // step 1
      ListProvidersAndSources();

      // you get this one from the configured connect string
      // change the connection string configured and notice change in schema
      conn = GetGenericOpenConnection();
      GetCustomerTableInfo(conn);
      conn.Dispose();
    }

    static void ListProvidersAndSources()
    {
      DataTable t = DbProviderFactories.GetFactoryClasses();
      foreach (DataRow r in t.Rows)
      {
        Console.WriteLine("Provider {0} is installed", r["Name"]);

        // if it's the SqlClient data provider, get the list of SQL Servers
        if (r["Name"].ToString() == "SqlClient Data Provider")
        {
          DbProviderFactory f = DbProviderFactories.GetFactory(r);
          DbDataSourceEnumerator sources = f.CreateDataSourceEnumerator();
          DataTable servers = sources.GetDataSources();
          foreach (DataRow sr in servers.Rows)
          {
            for (int i = 0; i < servers.Columns.Count; i++)
              Console.WriteLine("Column: {0} = {1}", servers.Columns[i].ColumnName, sr[i]);
          }
        }
      }
    }

    static DbConnection GetGenericConnection(string s)
    {
      DbProviderFactory f = null;

      if (s == "")
      {
        DataTable t = DbProviderFactories.GetFactoryClasses();
        f = DbProviderFactories.GetFactory(t.Rows[1]);
      }
      else if (s.Contains("provider="))
      {
        f = DbProviderFactories.GetFactory("System.Data.OleDb");
      }
      else
      {
        f = DbProviderFactories.GetFactory("System.Data.SqlClient");
      }

      DbConnection conn = f.CreateConnection();
      return conn;
    }

    static DbConnection GetGenericOpenConnection()
    {
      DbProviderFactory f = null;

      ConnectionStringSettings setting =
        ConfigurationManager.ConnectionStrings["Northwind"];
      f = DbProviderFactories.GetFactory(setting.ProviderName);

      DbConnection conn = f.CreateConnection();
      conn.ConnectionString = setting.ConnectionString;
      conn.Open();
      return conn;
    }

    static void GetCustomerTableInfo(DbConnection conn)
    {
      DataTable tables = conn.GetSchema("Tables");
      tables.WriteXml("all_tables.xml");

      string[] restrict = new string[3];
      restrict[0] = "northwind";
      restrict[1] = "dbo";
      restrict[2] = "customers";
      DataTable custtab = conn.GetSchema("Tables", restrict);
      custtab.WriteXml("customers_tables.xml");

      restrict[0] = "northwind";
      restrict[1] = "dbo";
      restrict[2] = "customers";
      DataTable custcol = conn.GetSchema("Columns", restrict);
      custcol.WriteXml("customers_columns.xml");
    }
  }
}
