MySql get number of rows

Question

I try to get number of rows from a table with this :

        string commandLine = "SELECT COUNT(*) FROM client";

        using (MySqlConnection connect = new MySqlConnection(connectionStringMySql))
        using (MySqlCommand cmd = new MySqlCommand(commandLine, connect))
        {
            connect.Open();

            int count = (int)cmd.ExecuteScalar();

            return count;
        }

And i get the exception:

Specified cast is not valid.

Any idea how i can fix it?

Answer 1

Try this

using (MySqlCommand cmd = new MySqlCommand(commandLine, connect))
{
    connect.Open();
    return Convert.ToInt32(cmd.ExecuteScalar());
}

Answer 2

using (MySqlCommand cmd = new MySqlCommand(commandLine, connect))
{
        connect.Open();
        return Convert.ToInt32(cmd.ExecuteScalar());
}

EDIT: Make also sure to handle exceptions in your code (E.g. if there is SQL Connection Error). Also, if it's not a COUNT(*) the value returned by ExecuteScalar() can be null (!)

Answer 3

If you wish to use the cast, you can use:

long count = (long)cmd.ExecuteScalar();

As mentioned above, COUNT in MySql returns BIGINT, hence casting with an int fails.