C# and Mysql

[rottmanj]

I am working on a project that requires me to connect to a mysql server. In order to create a more dynamic application, I am working on a way to pull the column names back into c# dynamically. At this point I have everything written except for the code where I get a result on the columns from the database, this is where I am stuck at.

I can't seem to be able to find a way to output the result I get back from mysql.

Any help with this is greatly appreciated.

public void sqlFields(string tblName)
        {
            MySqlConnection cnn = sqlConnection();
            MySqlCommand cmd = new MySqlCommand();
 
            string qryString = "SHOW COLUMNS FROM " + tblName;
 
            try
            {
                cmd.Connection = cnn;
                cmd.CommandText = qryString;
                cmd.ExecuteNonQuery();
                MySqlDataReader reader = cmd.ExecuteReader();
                reader.Read();
 
                while (reader.Read())
                {
                    Debug.Write(reader);
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message.ToString());
            }
        }

[Plater]

I am a little curious why you execute it twice?
cmd.ExecuteNonQuery();
MySqlDataReader reader = cmd.ExecuteReader();

I realize that the execution methods are different, but if you are running the exact same stored procedure, it seems wasteful to run it twice.

Have you looked at the MySqlDataAdapter class? In particular its .Fill(DataSet) function?

[rottmanj]

It is just a bit of left over code. I was working a few different test ideas. I have not looked at the MySqlDataAdapter, but I will now.

[rottmanj]

This is what I ended up using. It returns a string collection.

        public StringCollection sqlFields(string tblName)
        {
            MySqlConnection cnn = sqlConnection();
            MySqlCommand cmd = new MySqlCommand();
 
            string qryString = "SHOW COLUMNS FROM " + tblName;
            StringCollection fldCol = new StringCollection();
 
            try
            {                
                cmd.Connection = cnn;
                cmd.CommandText = qryString;
                MySqlDataReader reader = cmd.ExecuteReader();
 
 
                while (reader.Read())
                {
                    fldCol.Add(reader.GetValue(0).ToString());
                }
 
                return fldCol;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message.ToString());
                return fldCol;
            }
        }
 

[vanc]

Try reader["columnName"].ToString() for a particular column. Cheers.