C# - Mysql select queries not executing

Question

The program I have been creating takes a SQL query as a string parameter and passes it to a method where it is then executed. I am able to open my mysql connection for each query, but for some reason I am unable to run any select statements (I have tried both ExecuteReader and ExecuteScalar).

However, when I run an ExecuteNonQuery, it runs it fine. I am able to verify the insert statement worked from the ExecuteNonQuery.

I currently have the same database up in a SQLyog, using the exact same connection information. While the select statement is running, I am simultaneously running "SHOW FULL PROCESSLIST" and I do not see the query being run. Here is the code I have:

private void buttonConnect_Click(object sender, EventArgs e)
{
    string tmp = mysqlSelectScalar("select NAME from PRESIDENTS where name like '%trump%';");
    string tmp = mysqlSelectScalar("select COUNT(*) from project.PRESIDENTS;");
    mysqlnonQuery("insert into PRESIDENTS (ID,NAME) VALUES ('66','TEST');");
}

public string mysqlSelectScalar(string query)
{
    string connString = "server=" + textBoxHostname.Text + ";user=" + textBoxUsername.Text + "; password=" + textBoxPW.Text + ";port=" + textBoxPort.Text + ";database=" + textBoxDB.Text + ";RespectBinaryFlags = false;CharSet=utf8;";

    MySqlConnection cnn = new MySqlConnection(connString);
    string result = "";

    try
    {
        MessageBox.Show(query);
        MySqlCommand cmd = new MySqlCommand(query, cnn);

        using (cnn)
        {
            cnn.Open();
            cmd.CommandTimeout = 10;
            result = Convert.ToString(cmd.ExecuteScalar());
        }

        cnn.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show("COULD NOT CONNECT TO DATABASE: " + ex.ToString());
    }

    return result;
}

public void mysqlnonQuery(string query)
{
    string connString = "server=" + textBoxHostname.Text + ";user=" + textBoxUsername.Text + "; password=" + textBoxPW.Text + ";port=" + textBoxPort.Text + ";database=" + textBoxDB.Text + ";RespectBinaryFlags = false;CharSet=utf8;";

    MySqlConnection cnn = new MySqlConnection(connString);
    string result = "";

    try
    {
        MessageBox.Show(query);
        MySqlCommand cmd = new MySqlCommand(query, cnn);

        using (cnn)
        {
            cnn.Open();
            cmd.ExecuteNonQuery();
        }

        cnn.Close();
        connStatus = 0;
    }
    catch (Exception ex)
    {
        MessageBox.Show("COULD NOT CONNECT TO DATABASE: " + ex.ToString());
        connStatus = 1;
    }
}

private void outputTable(string query)
        {
            try
            {
                string connString = "server=" + textBoxHostname.Text + ";user=" + textBoxUsername.Text + "; password=" + textBoxPW.Text + ";port=" + textBoxPort.Text + ";database=" + textBoxDB.Text + ";RespectBinaryFlags = false;CharSet=utf8;";
                MySqlConnection conn = new MySqlConnection(connString);
                MySqlCommand cmd = new MySqlCommand(query, conn);
                MySqlDataAdapter adpt = new MySqlDataAdapter();
                conn.Open();
                cmd.CommandTimeout = 5;
                MySqlDataAdapter sqladapter = new MySqlDataAdapter(query, conn);
                DataSet DS = new DataSet();
                sqladapter.Fill(DS);
                //the above command is what times out. Everything before runs fine
                dataGridViewOutput.DataSource = DS.Tables[0];
                MessageBox.Show("dataGridViewOutput.DataSource = DS.Tables[0];");
                conn.Clone();

            }
            catch (Exception ex)
            {
                richTextBoxOutput.Text = ex.ToString();
            }
        }

The error message I am getting is from a timeout:

System.TimeoutException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond

System.IO.IOException: Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond

I am sure that the connection string is working because I am able to run the mysqlnonQuery method, and when I put print statements in the mysqlSelectScalar method I saw I was able to get past the opening of the connection.

I should also specify that the table I am selecting from is only 45 records, and I am able to run the same select queries from the mysql command line, which complete in about 0.01 seconds.

This code is also being re-purposed from an older project I was working on, with the exact same mysqlSelectScalar method, and was working perfectly.

Any kind of help would be much appreciated.

Since you already have using(cnn) , you do not need to close the connection. It will be taken care of. Do you debug step by step? — Tarik
– Tarik, Commented Apr 18, 2020 at 1:57
Use a common connection string. You can right click on your project in project explorer and click settings. You can then create a connection string that will be stored in app.settings. — Tarik
– Tarik, Commented Apr 18, 2020 at 1:59
Tarik, yes I have debugged step by step. I am able to see that the cnn.Open(); works. The issue lies with the result = Convert.ToString(cmd.ExecuteScalar()); command. Additionally, would I still be able to store the connection in the app settings if the connection information were to change mid-program? I am trying to make it versatile to connect to any mysql database — Andrew Draper
– Andrew Draper, Commented Apr 18, 2020 at 16:01
I have also attempted a different method, by returning the results of the select query into a dataGridView. See the updated code above — Andrew Draper
– Andrew Draper, Commented Apr 18, 2020 at 16:41

maxspan · Accepted Answer · 2020-04-18 01:53:14Z

1

using System;
using MySql.Data.MySqlClient;

namespace RetrieveCars
{
    class Program
    {
        static void Main(string[] args)
        {
            string cs = @"server=localhost;userid=dbuser;password=s$cret;database=testdb";

            using var con = new MySqlConnection(cs);
            con.Open();

            string sql = "SELECT * FROM cars";
            using var cmd = new MySqlCommand(sql, con);

            using MySqlDataReader rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                Console.WriteLine("{0} {1} {2}", rdr.GetInt32(0), rdr.GetString(1), 
                        rdr.GetInt32(2));
            }
        }
    }
}

answered Apr 18, 2020 at 1:53

maxspan

14.3k16 gold badges79 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

C# - Mysql select queries not executing

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related