Tuesday, June 18, 2013

Database Exploration with C# and SQL Server

This is a easy way to explore the items from database.
In this Program 3 Modules having:

module 1:
    Select all database name and load into combo box.
module 2:
    Select all Table name from particular database and load into list box.
module 3:
    Select all the column name from particular table and load into list box.

Note: Here particular database or table means dynamic selection at run time.

namespace db_explore
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        //Variable declaration
                SqlConnection con;
                SqlCommand cmd;
                SqlDataReader dr = null;
                String st;
      
        //For database
        private void Form1_Load(object sender, EventArgs e)
        {
            //DB Connection string
            con = new SqlConnection();
            con.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=master;Integrated Security=True";
            con.Open();
            MessageBox.Show("Successfully Connected."); //message notification for database connection
           
            //select database and load into combo box
            st = "select name from sys.databases";  //database selection query
            cmd = new SqlCommand(st, con);
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                cbox.Items.Add(dr[0].ToString());   //add the database names into combo box
            }
            dr.Close();
        }

        //For Table name
        private void cbox_SelectedIndexChanged(object sender, EventArgs e)
        {
            lbox1.Items.Clear();    // clearing listbox1
            lbox2.Items.Clear();    // clearing listbox2
            String ts;
            ts = "SELECT * FROM " + cbox.SelectedItem + ".sys.Tables";  //table selection query for particular DB
            cmd = new SqlCommand(ts,con);
            dr = cmd.ExecuteReader();   //execute data reader
            while (dr.Read())
            {
                lbox1.Items.Add(dr[0].ToString());  //add the table names into listbox2 for particular DB
            }
            dr.Close();
        }

       //For column name
        private void lbox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            lbox2.Items.Clear();    //clearing listbox2
            String cs;
            //list the column from particular database & table
            cs = "SELECT column_name 'Column Name' FROM " + cbox.SelectedItem + ".INFORMATION_SCHEMA.columns WHERE table_name ='"+ lbox1.SelectedItem +"' ";
          //  MessageBox.Show(cs);    //message for verification of query
            cmd = new SqlCommand(cs, con);
            dr = cmd.ExecuteReader();   //execute data reader
            while (dr.Read())
            {
                lbox2.Items.Add(dr[0].ToString());  //add the column names into listbox2 for particular table
            }
            dr.Close();
        }

        private void btn_can_Click(object sender, EventArgs e)
        {
            this.Close();   //close the application
        }
    }  
}

It is a dynamic. We can select the database dynamically to explore the content for tables available in database and we can select the table name to explore the column names for particular table.

For download full program: db_explorer.rar

Sample output:

db1 db2