C# Checking if a query returns empty set not working?

I have been working on trying to check if a record exists in a database table. I have the following code and it is not working.

 MySqlConnection connection = new MySqlConnection("My connection string");
        string check = string.Format("Select COUNT(*) FROM User_Class WHERE user_class_name = '{0}'", TextBox1.Text);
        string query = string.Format("INSERT INTO User_Class (user_class_name) VALUE ('{0}');", TextBox1.Text);
        MySqlCommand cmd = new MySqlCommand(check, connection);
        MySqlCommand cmd2 = new MySqlCommand(query, connection);
        //MySqlDataReader reader;
        connection.Open();
        if (cmd.BeginExecuteNonQuery().Equals(0))//record does not exist
        {
            cmd2.BeginExecuteNonQuery();
            Label1.Text = "User Class Created!";
            Label1.ForeColor = Color.Green;

        }
        else
        {
            Label1.Text = "User Class Already Exists";
            Label1.ForeColor = Color.Red;
        }

This code always goes to the else case. I have also tried doing this using MySqlDataReader but that is also not working. Why is it not returning 0 (or false/null)? What is the best way to check for an empty return?

Jon Skeet
people
quotationmark

There are at least two issues here.

Firstly, you're calling BeginExecuteNonQuery - that doesn't return an integer, it returns an IAsyncResult. I strongly suspect that you don't want to be dealing with the asynchronous API at all.

Secondly, using ExecuteNonQuery isn't appropriate for a query anyway. I suspect you actually want ExecuteScalar:

if ((int) cmd.ExecuteScalar() == 0)

You may need to cast to long instead of int; I don't know offhand what type of value it will return. However, that's a far more sensible call for a "SELECT COUNT" query.

I'd then suggest you use ExecuteNonQuery for the INSERT... after changing your code to use parameterized SQL instead of the SQL-injection-inviting approach you're using now.

people

See more on this question at Stackoverflow