Connect to different databases

Postgres and C#

This code sample demonstrates how to connect to postgres database, load available table names and load the selected table data into a datagridview. Before you start, you should reference (Add Reference in the Project menu of VisualStudio) the Postgres.Net connector(Npqsql.dll), and insert a line: 'using NpgSql;' at the beginning.

 NpgsqlConnectionStringBuilder cnsb = new NpgsqlConnectionStringBuilder();
        public Form1()
        {
            InitializeComponent();
            cnsb.Database = "postgres";
            cnsb.Username = "postgres";
            cnsb.Password = "root";
            cnsb.Host = "localhost";
        }

        private void bttnConnect_Click(object sender, EventArgs e)
        {
            DataTable dt = selectTableNames();
            comboBox1.DataSource = dt;
            comboBox1.DisplayMember = dt.Columns[0].ToString();
            comboBox1.ValueMember = dt.Columns[0].ToString();
        }

        DataTable selectTableNames()
        {
            using (NpgsqlConnection cnn = new NpgsqlConnection(cnsb))
            {
                DataTable dt = new DataTable();
                using (NpgsqlCommand cmd = new NpgsqlCommand("select table_name from information_schema.tables where table_schema='public'", cnn))
                {
                    cnn.Open();
                    NpgsqlDataReader dr = cmd.ExecuteReader();
                    dt.Load(dr);
                    return dt;
                }

            }
        }


        DataTable loadTable(string tableName)
        {
            DataTable dt = new DataTable();

            using (NpgsqlConnection cnn = new NpgsqlConnection(cnsb.ConnectionString))
            {
                cnn.Open();
                string colNames = getColumnNames(tableName, cnn);

                string sqlCommand = "SELECT " + colNames + " FROM " + tableName;
                using (NpgsqlCommand cmd = new NpgsqlCommand(sqlCommand, cnn))
                {
                    NpgsqlDataAdapter da = new NpgsqlDataAdapter(sqlCommand,cnn); 
                    da.Fill(dt); 
                }
            }
            return dt;
        }

MySql and C#

This code sample demonstrates how to connect to a MySql database, load available table names and load the selected table data into a datagridview. Before you start, you should reference (Add Reference in the Project menu of VisualStudio) the MySql.net connector connector, and insert a line: 'using MySql.Data.MySqlClient;' at the beginning.

 MySqlConnectionStringBuilder cnsB = new MySqlConnectionStringBuilder();
        protected void Page_Load(object sender, EventArgs e)
        {
            cnsB.Server = "localhost";
            cnsB.UserID = "username";
            cnsB.Password = "password";
            cnsB.Database = "databaseName";
            MySqlConnection cnn = new MySqlConnection(cnsB.ConnectionString);
            DataTable dt = getTableNames(cnn);
            ddlTabNames.Items.Clear();
            foreach (DataRow row in dt.Rows)
            {
                ddlTabNames.Items.Add(row[0].ToString());
            }
        }


        private void loadTable(string SqlCommand, MySqlConnection cnn)
        {
            using (cnn)
            {
                cnn.ConnectionString = cnsB.ConnectionString;
                cnn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = cnn;
                cmd.CommandText = SqlCommand;
                MySqlDataAdapter da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
        }

        private DataTable getTableNames(MySqlConnection cnn)
        {
            using (cnn)
            {
                DataTable dt = new DataTable();
                cnn.Open();
                MySqlDataAdapter da=new MySqlDataAdapter("select table_name from information_schema.tables where table_schema='" + cnsB.Database + "'", cnn);
                da.Fill(dt);
                return dt;
            }
        }

        protected void ddlTabNames_SelectedIndexChanged(object sender, EventArgs e)
        {
            MySqlConnection cnn = new MySqlConnection(cnsB.ConnectionString);
            loadTable("select * from " + ddlTabNames.SelectedValue, cnn);
        }
    }
	

Sql server and C#

This code sample demonstrates how to connect to a Sql Server database, load the selected table data into a datagridview. Before you start, you should insert a line: 'using System.Data;' and 'using System.Data.SqlClient;' at the beginning.

 SqlConnectionStringBuilder cnsb = new SqlConnectionStringBuilder();

        protected void Page_Load(object sender, EventArgs e)
        {
                cnsb.UserID = "userid";
                cnsb.Password = "password";
                cnsb.InitialCatalog = "initialcatalog";
                cnsb.DataSource = "datasource";
                fillTableNames();
        }


        protected void fillTableNames()
        {
            using (SqlConnection cnn = new SqlConnection(cnsb.ConnectionString))
            {
                cnn.Open();
                SqlDataAdapter da = new SqlDataAdapter("select * from groups", cnn);  //necessary table name are in the 'groups' table
                DataTable dt = new DataTable();
                da.Fill(dt);
                ddlTables.DataSource = dt;
                ddlTables.DataTextField= dt.Columns[1].ToString();
                ddlTables.DataValueField = dt.Columns[0].ToString();
                ddlTables.DataBind();
            }
        }


        protected DataTable getTableData(string sqlCmd)
        {
            using (SqlConnection cnn = new SqlConnection(cnsb.ConnectionString))
            {
                cnn.Open();
                SqlDataAdapter da = new SqlDataAdapter(sqlCmd, cnn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
        }