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

Question

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?

check this link msdn.microsoft.com/en-us/library/ca56w9se%28v=vs.110%29.aspx msdn.microsoft.com/en-us/library/7b6f9k7k%28v=vs.110%29.aspx This a method to show the async data — pankeel
– pankeel, Commented Jan 30, 2014 at 18:49

Jon Skeet · Accepted Answer · 2014-01-30 18:56:36Z

3

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.

edited Jan 30, 2014 at 18:56

answered Jan 30, 2014 at 18:49

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

6 Comments

Sriram Sakthivel Over a year ago

Note: ExecuteScalar may return null or DBNull; in that case casting to int will fail

Jon Skeet Over a year ago

@SriramSakthivel: Will it return either of those for a SELECT COUNT query? (It can return plenty of types of value, but I'd expect it to return an integer for a SELECT COUNT...)

Andy Over a year ago

@JonSkeet Thank you for the very informative answer! I understand my mistake and have fixed it!

Jon Skeet Over a year ago

@Andy: with parameterized SQL you don't have to sanitize the inputs. Get into the habit of always using parameterized SQL, however early you are in the process. Personally I think it ends up as simpler code anyway...

Brian Over a year ago

I feel like half the SO users who are told to use Parameterized SQL are asking why their code fails to handle either weird strings (e.g., containing quotes) or dates. Parameterized SQL makes things easier for mere mortals, not just for Jon.

|

Richard Seal · Accepted Answer · 2014-01-30 18:52:43Z

2

A couple of points - you are not calling the BeginExecuteNonQuery() command properly - I don't think you meant to all the Asynchronous method.

Also, you are calling the check with ExecuteNonQuery - that will just execute the query and return a result status - which unless the SQL fails will always be success, You should be using:

if ((long)(cmd.ExecuteScalar() ?? 0) == 0)

That will give you the results of the Count(*)

Finally - writing your embedded SQL like that leaves you open to hack - Look at this: http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html

answered Jan 30, 2014 at 18:52

Richard Seal

4,26616 silver badges30 bronze badges

1 Comment

Andy Over a year ago

Thanks for the answer! I look forward to reading that article

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related