Getting MySQL record count with C#

I would like to know how can I get record count of a query with C#.

Here is the code that I u开发者_开发知识库se..

    MySqlDataReader recordset = null;
    query = new MySqlCommand("SELECT * FROM test ORDER BY type_ID ASC", this.conn);
    recordset = query.ExecuteReader();


    while (recordset.Read())
    {
        result.Add(recordset["type_ID"].ToString());

    }
    return result;

I was using a SELECT COUNT(*) and expected an int to be returned. You may need this to get a usable value:

mysqlint = int.Parse(query.ExecuteScalar().ToString());

A couple of things...

The SQL statement you would use is:

SELECT COUNT(*) FROM test

However, when using the MySQL Connector/Net to connect to MySQL through C# there is some care to be given when handling query results.

For example, as cited in this question and on Microsoft Connect int.Parse("0") equivalently known as Int32.Parse("0") can throw a FormatException on some machines.

I have found that Convert.ToInt32 handles this case nicely.

So your code will be something like this:

using (var conn = new MySqlConnection(cs))
{
    conn.Open();
    using (var cmd = new MySqlCommand("SELECT COUNT(*) FROM test", conn))
    {
         int count = Convert.ToInt32(cmd.ExecuteScalar());
         return count;
    }
}

Remember to make use of using statements in order to ensure that the MySQL objects get disposed of properly.

You're adding a new element in result for each row. Depending on the type of result you should be able to do something like result.Count after the while loop completes.

You could run another query first to get the count :

query = new MySqlCommand("SELECT count(*) as theCount FROM test ORDER BY type_ID ASC", this.conn);

but in truth, you are probably best changing the problem so you wont need the count until after you have populated the list.