C# Populate datagridview with SQL Server and MySQL tables

Question

I'm trying to populate a datagridview with SQL Server and MySQL tables to map data between the two tables. The problem I'm facing is getting the column from MySQL table on the same row as that on SQL Server table. Populating the datagridview individually is working fine.

Here's what I need after selecting a table from combobox.Sample result

Here's my code for querying:

string colName, dataType, charMaxLeng, isNull;

---

1. SQL Database.

---

     private void SQLMapping()
    {
        try
        {
            int i = 0;
            string charMax;
            dtgMappings.Rows.Clear();
            Conn.Close();
            Conn.Open();
            CMD = new SqlCommand("SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE FROM INFORMATION_SCHEMA. COLUMNS WHERE TABLE_NAME='" + cboTables.Text + "';", Conn);
             Dr = CMD.ExecuteReader();       
            while (Dr.Read())
            {
                if(Dr.IsDBNull(2))
                {
                    charMax = null;
                }
                else
                {
                    charMax = Dr.GetInt32(2).ToString();
                }
                MySQLMapping();
                i += 1;
                dtgMappings.Rows.Add(null, i, Dr.GetString(0).ToString(), Dr.GetString(1).ToString(), charMax, Dr.GetString(3).ToString(), colName, colDataType.Items.Add(Convert.ToString(dataType)), charMaxLeng, colNull.Items.Add(Convert.ToString(isNull)));
            }
            Dr.Close();
            Conn.Close();
            groupBox2.Text = "Columns available for Mapping(" + dtgMappings.Rows.Count + ")";
        }
        catch (Exception ex)
        {
            Conn.Close();
            MessageBox.Show(ex.Message, Application.ProductName);
        }
    }

---

2. MySQL Database.

---

      private void MySQLMapping()
    {
        try
        {
            MyConn.Close();
            MyConn.Open();
            MyCMD = new MySqlCommand("SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='" + cboTables.Text.ToLower() + "';", MyConn);
            MyDr = MyCMD.ExecuteReader();
            while (MyDr.Read())
            {
                if (MyDr.IsDBNull(2))
                {
                    charMaxLeng = null;
                }
                else
                {
                    charMaxLeng = MyDr.GetInt32(2).ToString();
                }
                colName = MyDr.GetString(0).ToString();
                dataType = MyDr.GetString(1).ToString();
                isNull = MyDr.GetString(3).ToString();                 
            }
            MyDr.Close();
            MyConn.Close();
        }
        catch(Exception ex)
        {
            MyConn.Close();
            MessageBox.Show(ex.Message, Application.ProductName);
        }
    }

---

Anyone to help me to populate the other columns (MySQL Column, Data Type, Length, Null) from MySQL Database

Answer 1

Something like this perhaps;

//use AS to rename all the columns other than COLUMN_NAME
var sda = new SqlDataAdapter("SELECT COLUMN_NAME, DATA_TYPE AS SQLS_DATA_TYPE, ... WHERE table_name = @t", "connstr");
sda.Parameters.AddWithValue("@t", tablenameCombo.SelectedValue); //generally avoid AddWithValue on SQLS but it doesn't matter so much here
var sdt = new DataTable();
sda.Fill(sdt);

//use AS to rename all the columns other than COLUMN_NAME
var mda = new MySqlDataAdapter("SELECT COLUMN_NAME, DATA_TYPE AS MYSQL_DATA_TYPE, ... WHERE table_name = @t", "connstr");
mda.Parameters.AddWithValue("@t", tablenameCombo.SelectedValue); // AddWithValue isn't a problem on MySQL
var mdt = new DataTable();
mda.Fill(mdt);

sdt.PrimaryKey = new[]{sdt.Columns["COLUMN_NAME"]};
mdt.PrimaryKey = new[]{mdt.Columns["COLUMN_NAME"]};

sdt.Merge(mdt);

datagridviewX.DataSource = sdt;

By making two tables that are like:

COLUMN_NAME,
SQLS_DATA_TYPE,
SQLS_IS_NULLABLE,
...

And

COLUMN_NAME,
MYSQL_DATA_TYPE,
MYSQL_IS_NULLABLE,
...

By setting the COLUMN_NAME columns as PK and then telling them to Merge you get:

COLUMN_NAME,
SQLS_DATA_TYPE,
SQLS_IS_NULLABLE,
MYSQL_DATA_TYPE,
MYSQL_IS_NULLABLE,
...

If I misunderstood the intention to map on column name and instead you want to map on an incrementing number, it might be easiest just to put a ROW_NUMBER() OVER(...) into the sql to create an incrementing number column and make that DataTable PK instead

---

Fix your SQL Injection vulnerability - those queries are easily parameterizable