C# -Display .NET Data Providers,databases,tables

In this snippet i will show you how to display .NET Data Providers installed on your machine
I will show you how to list all databases and tables foreach one of them

/*

This snippet is part of an Windows Form Project
It includes the following controls :
comboBox1 : It will list all data providers (access,mysql,oracle,sql server....etc)
comboBox3 : It will list all databases of the selected data provider
comboBox2 : It will all tabes of the selected databases
GridView : that will list the data of the selected table
openFileDialog1 : required to browse for db file with access or sqlite

*/

using System;
using System.Data;
using System.Data.Common;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace ListDataProviders
{
    public partial class Form1 : Form
    {
        String cstr;
        String user, pwd="";
        DataTable db = new DataTable();
        DataTable providers = new DataTable();
        DataTable tables = new DataTable();
        DataTable select;
        DbProviderFactory fact;
        DbConnection cnx;
        DbCommand cmd;
        DbDataReader reader;
        public Form1()
        {
            try
            {
                InitializeComponent();
                // SHOW DATA PROVIDERS
                providers = DbProviderFactories.GetFactoryClasses();
                foreach (DataRow row in providers.Rows)
                {
                    if (!comboBox1.Items.Contains(row[2]))
                    {
                        comboBox1.Items.Add(row[2]);
                    }
                }
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message);
            }
        }

        // when the data provider is changed we will list all its databases

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                comboBox3.Text = "";
                comboBox3.Items.Clear();
                fact = DbProviderFactories.GetFactory(comboBox1.SelectedItem.ToString());
                cnx = fact.CreateConnection();
                // if it is mysql
                if (comboBox1.SelectedItem.ToString()=="MySql.Data.MySqlClient")
                {
                    user = Microsoft.VisualBasic.Interaction.InputBox("Please Input Your User", "User", "");
                    pwd = Microsoft.VisualBasic.Interaction.InputBox("Please Input Your Password", "Password", "");
                    cstr = "Server=localhost;Uid=" + user + ";Pwd=" + pwd+";";
                    cnx.ConnectionString = cstr;
                    cnx.Open();
                    cmd = cnx.CreateCommand();
                    cmd.CommandText = "SHOW DATABASES;";
                    reader = cmd.ExecuteReader();
                    db = new DataTable();
                    db.Load(reader);
                    foreach (DataRow row in db.Rows)
                    {
                        comboBox3.Items.Add(row["Database"]);
                    }
                    cnx.Close();
                }

                // if it is Oracle then its a special case : we will directly show all tables
                // because in oracle we have one database per user

                if (comboBox1.SelectedItem.ToString() == "System.Data.OracleClient")
                {
                    comboBox2.Text = "";
                    comboBox2.Items.Clear();
                    user=Microsoft.VisualBasic.Interaction.InputBox("Please Input Your User","User", "");
                    pwd = Microsoft.VisualBasic.Interaction.InputBox("Please Input Your Password", "Password", "");
                    cstr = "Data Source = localhost; User Id = " + user + "; Password = " + pwd + ";Integrated Security = no;";
                    cnx.ConnectionString = cstr;
                    cnx.Open();
                    cmd = cnx.CreateCommand();
                    cmd.CommandText = "select table_name from user_tables";
                    reader = cmd.ExecuteReader();
                    tables = new DataTable();
                    tables.Load(reader);
                    foreach (DataRow row in tables.Rows)
                    {
                        comboBox2.Items.Add(row[0]);
                    }
                    cnx.Close();
                }
                // if it is access then we will browse for the file
                if (comboBox1.SelectedItem.ToString()=="System.Data.OleDb")
                {
                    openFileDialog1.Filter = "Database files (*.mdb, *.accdb)|*.mdb;*.accdb";
                    openFileDialog1.Title = "Choose an access file (accdb,mdb)";
                    if (openFileDialog1.ShowDialog() == DialogResult.OK)
                    {
                        String accdblocation = openFileDialog1.InitialDirectory + openFileDialog1.FileName;
                        comboBox2.Items.Clear();
                        fact = DbProviderFactories.GetFactory(comboBox1.SelectedItem.ToString());
                        cnx = fact.CreateConnection();
                        cstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + accdblocation;
                        cnx.ConnectionString = cstr;
                        cnx.Open();
                        comboBox2.Items.Clear();
                        string[] restrictions = new string[4];
                        restrictions[3] = "Table";
                        tables = new DataTable();
                        tables = cnx.GetSchema("Tables",restrictions);
                        foreach (DataRow row in tables.Rows)
                        {
                            comboBox2.Items.Add(row[2]);
                        }
                    }
                }

                // if it is sqlite then we will browse for file

                if (comboBox1.SelectedItem.ToString()=="System.Data.SQLite.EF6")
                {
                    openFileDialog1.Filter = "Database files (*.db, *.sqlite)|*.db;*.sqlite";
                    openFileDialog1.Title = "Choose a db file";
                    if (openFileDialog1.ShowDialog() == DialogResult.OK)
                    {
                        cnx.ConnectionString = "Data Source=" + openFileDialog1.InitialDirectory + openFileDialog1.FileName + ";Version=3;";
                        cnx.Open();
                        comboBox2.Items.Clear();
                        tables = new DataTable();
                        tables = cnx.GetSchema("Tables");
                        foreach (DataRow row in tables.Rows)
                        {
                            comboBox2.Items.Add(row[2]);
                        }
                    }
                }
                // if it is sql server
                if (comboBox1.SelectedItem.ToString()=="System.Data.SqlClient")
                {
                    cnx.ConnectionString = "Server=.\\SQLEXPRESS;Trusted_Connection=True";
                    cnx.Open();
                    cmd = cnx.CreateCommand();
                    cmd.CommandText = "SELECT name FROM sys.databases";
                    reader = cmd.ExecuteReader();
                    db = new DataTable();
                    db.Load(reader);
                    foreach (DataRow row in db.Rows)
                    {
                        comboBox3.Items.Add(row[0]);
                    }
                }
            }
            catch (Exception ex2)
            {
                MessageBox.Show(ex2.Message);
            }
        }

        // when the database is selected we will list its tables

        private void comboBox3_SelectedIndexChanged(object sender, EventArgs e) // when
        {
            try
            {
                comboBox2.Text = "";
                comboBox2.Items.Clear();
                fact = DbProviderFactories.GetFactory(comboBox1.SelectedItem.ToString());
                cnx = fact.CreateConnection();
                // if it is mysql
                if (comboBox1.SelectedItem.ToString()=="MySql.Data.MySqlClient")
                {
                    cstr = "Server=localhost;Uid=" + user + ";Pwd=" + pwd+";";
                    cnx.ConnectionString = cstr + "Database=" + comboBox3.SelectedItem.ToString() + ";";
                    cnx.Open();
                    tables = new DataTable();
                    tables = cnx.GetSchema("Tables");
                    foreach (DataRow row in tables.Rows)
                    {
                        comboBox2.Items.Add(row[2]);
                    }
                    cnx.Close();
                }
                // SI LE SGBD EST SQL SERVER
                if (comboBox1.SelectedItem.ToString()=="System.Data.SqlClient")
                {
                    cnx.ConnectionString = "Server=.\\SQLEXPRESS;Trusted_Connection=True;Database="+comboBox3.SelectedItem.ToString();
                    cnx.Open();
                    cmd = cnx.CreateCommand();
                    tables = new DataTable();
                    tables = cnx.GetSchema("Tables");
                    foreach (DataRow row in tables.Rows)
                    {
                        comboBox2.Items.Add(row[2]);
                    }
                    cnx.Close();
                }
            }
            catch (Exception e3)
            {
                MessageBox.Show(e3.Message);
            }
        }

        // when the table is selected we will show its data

        private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                fact = DbProviderFactories.GetFactory(comboBox1.SelectedItem.ToString());
                cnx = fact.CreateConnection();
                // SI LE SGBD EST MYSQL
                if (comboBox1.SelectedItem.ToString()=="MySql.Data.MySqlClient")
                {
                    cstr = "Server=localhost;Uid=" + user + ";Pwd=" + pwd+";";
                    cnx.ConnectionString = cstr + "Database=" + comboBox3.SelectedItem.ToString() + ";";
                    cnx.Open();
                    cmd = cnx.CreateCommand();
                    cmd.CommandText = "SELECT * FROM " + comboBox2.SelectedItem.ToString();
                    reader = cmd.ExecuteReader();
                    select = new DataTable();
                    select.Load(reader);
                    dataGridView1.DataSource = select;
                    cnx.Close();
                }
                // SI LE SGBD EST ACCESS
                if (comboBox1.SelectedItem.ToString()=="System.Data.OleDb")
                {
                    cstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + openFileDialog1.InitialDirectory + openFileDialog1.FileName;
                    cnx.ConnectionString = cstr;
                    cnx.Open();
                    cmd = cnx.CreateCommand();
                    cmd.CommandText = "SELECT * FROM " + comboBox2.SelectedItem.ToString();
                    DbDataReader reader = cmd.ExecuteReader();
                    select = new DataTable();
                    select.Load(reader);
                    dataGridView1.DataSource = select;
                    cnx.Close();
                }
                // SI LE SGBD EST SQLITE
                if (comboBox1.SelectedItem.ToString()=="System.Data.SQLite.EF6")
                {
                    cstr = "Data Source="+openFileDialog1.InitialDirectory + openFileDialog1.FileName + ";Version=3;";
                    cnx.ConnectionString = cstr;
                    cnx.Open();
                    cmd = cnx.CreateCommand();
                    cmd.CommandText = "SELECT * FROM " + comboBox2.SelectedItem.ToString();
                    DbDataReader reader = cmd.ExecuteReader();
                    select = new DataTable();
                    select.Load(reader);
                    dataGridView1.DataSource = select;
                    cnx.Close();
                }
                // SI LE SGBD EST SQL SERVER
                if (comboBox1.SelectedItem.ToString()=="System.Data.SqlClient")
                {
                    cnx.ConnectionString = "Server=.\\SQLEXPRESS;Trusted_Connection=True;Database=" + comboBox3.SelectedItem.ToString();
                    cnx.Open();
                    cmd = cnx.CreateCommand();
                    cmd.CommandText = "SELECT * FROM " + comboBox2.SelectedItem.ToString();
                    reader = cmd.ExecuteReader();
                    select = new DataTable();
                    select.Load(reader);
                    dataGridView1.DataSource = select;
                    cnx.Close();
                }
                // SI LE SGBD EST ORACLE
                if (comboBox1.SelectedItem.ToString() == "System.Data.OracleClient")
                {
                    cstr = "Data Source = localhost; User Id = " + user + "; Password = " + pwd + ";Integrated Security = no;";
                    cnx.ConnectionString = cstr;
                    cnx.Open();
                    cmd = cnx.CreateCommand();
                    cmd.CommandText = "SELECT * FROM " + comboBox2.SelectedItem.ToString();
                    reader = cmd.ExecuteReader();
                    select = new DataTable();
                    select.Load(reader);
                    dataGridView1.DataSource = select;
                    cnx.Close();
                }
            }
            catch (Exception e4)
            {
                MessageBox.Show(e4.Message);
            }
        }
        /*
        the following events were handled to avoid some exceptions that occured when using keyboard with comboBox
        they are not necessary
        */
        private void comboBox1_KeyDown(object sender, KeyEventArgs e)
        {
            e.Handled = (e.KeyCode == Keys.Up || e.KeyCode == Keys.Down);
        }
        private void comboBox3_KeyDown(object sender, KeyEventArgs e)
        {
            e.Handled = (e.KeyCode == Keys.Up || e.KeyCode == Keys.Down);
        }
        private void comboBox2_KeyDown(object sender, KeyEventArgs e)
        {
            e.Handled = (e.KeyCode == Keys.Up || e.KeyCode == Keys.Down);
        }
    }
}

and please don't forget to like our facebook page for funny memes and jokes
http://adf.ly/1jcdRJ
thanxs :)