C# and mySQL - returning results from a search

[Clinical]

Hi everyone,

I've currently got a C# application running, using a mySQL database. I've managed to be able to open, read and display the database and query it too. What I want to be able to do is query the database for a username, and if it does not exist, return "false", or say have a "rows returned" integer. This is so that I can call other functions depending on the results returned. I'd like it to work something like this:

string searchData = "SELECT * FROM player_info WHERE idname =  @idname";
MySqlCommand myCommand = new MySqlCommand(searchData);
myCommand.Parameters.Add("?idname", MySqlDbType.VarChar, 20, data);

//--and then depending on if the name exists, call other functions:

if(rowsReturned == 0)
{ 
    addusernametoDB();
}
else
{
   dosomethingelse();
}

I hope this makes sense to someone - I've been racking my brains for days on this and I've made not a bit of progress.

Thanks in advance

[jmcilhinney]

You should never execute "SELECT *" unless you specifically want every column. In your case at most you want the idname column, but you don't event need that. Use this query:

SELECT COUNT(*) FROM player_info WHERE idname = ?idname

Also, your parameter is wrong. If you're assigning a literal value then you don't need the type or size because that will be inferred from the value. Just use:

myCommand.Parameters.AddWithValue("?idname", data);

where I'm assuming that 'data' is the value that you're looking for. If AddWithValue is not supported just use Add but with the same argument list.

Finally, you call ExecuteScalar on your command. It will return an Object reference containing an int that represents the number of matching rows. If the value is zero then you know that that idname value is not in the table.