CODEWITHSUNDEEP
c#mysql
Nathan
Nathan

Reputation: 2513

Counting number of affected rows when using mysql in c#

I have a WinForms project in C# that connects to a MySQL database. I'm trying to count the number of rows in the result, i can't use RecordsAffected as this property only has the correct value after the read is complete and as you can see from the code below I need it before. Any idea's on how to do this?

string sql = "SELECT * FROM Tigers WHERE Link='" + link + "'";

MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())
{
    if (rdr.RecordsAffected == 0)
    {
        //can't find in db               
    }
    else
    {
        //found at least 1 result
    }

Upvotes: 2

Views: 6001

Answers (4)

Nasreddine
Nasreddine

Reputation: 37838

You can use HasRows instead :

string sql = "SELECT * FROM Tigers WHERE Link='" + link + "'";

MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();

if(rdr.HasRows){
    while (rdr.Read())
    {
            //do stuff here
    }
} else {
    //nothing found
}

Edit : if you don't need to read the data you retrieve from the database and just want the count you could use :

cmd.CommandText = "SELECT COUNT(*) FROM Tigers WHERE Link='" + link + "'";
Int32 count = (Int32) cmd.ExecuteScalar();

Upvotes: 7

Rowland Shaw
Rowland Shaw

Reputation: 38130

By using an IDataReader (which MySqlDataReader implements), there is no guarantee that the server knows how many rows are affected until the read completes.

I'd structure your code slightly differently:

string sql = "SELECT * FROM Tigers WHERE Link='" + link + "'";

using( MySqlCommand cmd = new MySqlCommand(sql, conn) )
{
    using( MySqlDataReader rdr = cmd.ExecuteReader() )
    {
        while (rdr.Read())
        {
            //found at least 1 result
        }

        if (rdr.RecordsAffected == 0)
        {
            //can't find in db
        }
    }
}

Upvotes: 0

Rich O'Kelly
Rich O'Kelly

Reputation: 41767

You can use the ExecuteScalar method like so:

MySqlCommand cmd = new MySqlCommand(sql, conn);
int rowCount = cmd.ExecuteScalar();

As an aside your current query may be vulnerable to a Sql Injection attack. You should consider parameterising your query, like so:

string sql = "SELECT * FROM Tigers WHERE Link=?link";

IDbDataParameter param = cmd.CreateParameter();
param.ParameterName = "?link";
param.Value = link;
cmd.Parameters.Add(param);

int rowCount = cmd.ExecuteScalar();

Upvotes: 1

Darin Dimitrov
Darin Dimitrov

Reputation: 1038930

You don't affect much in the database when you perform a SELECT query. You are retrieving records that match your query. If you want to count the number of records that match your query you could use count along with a cmd.ExecuteScalar().

Upvotes: 1

Related Questions