C# Check if value is returned from database

Question

 SqlCeConnection sqlCnn = new SqlCeConnection("Data Source=" + Application.StartupPath + "\\mainDB.sdf");
 SqlCeCommand sqlCmd = new SqlCeCommand("SELECT * FROM Accounts WHERE (username = @user AND password = @pass)", sqlCnn);
 sqlCmd.Parameters.Add("@user", textBox1.Text);
 sqlCmd.Parameters.Add("@pass", textBox2.Text);
 sqlCnn.Open();
 SqlCeDataReader reader = sqlCmd.ExecuteReader();
 while (reader.Read())
 {
    // Some code ...
 }

I have this code that reads some values from a database but I want to check if any value is returned from the database. I want to check if the username and password from the database is equal to textBox1 and textBox2 and if not, return a failure message.

Answer 1

Simply use the code like this:

if(reader.Read()){
   //your code
}else {
  //Show message notifying failure
}
//remember to close your reader
reader.Close(); //or use using statement for convenience.

However DataReader is used mainly for reading a set of data (just 1 record is a little overkill). You can try modifying your query a little such as by using If Exists(...)... and use ExecuteScalar() to get the return result. If it's not null then it's OK.

//the modified query
If Exists(SELECT * FROM Accounts WHERE (username = @user AND password = @pass))
SELECT 1 ELSE SELECT 0

var r = sqlCmd.ExecuteScalar();
if(r == null || (int)r == 0){
  //failure...
}

Answer 2

I would "select count(*) from ..."

Then do ExecuteScalar() instead. This will return an int.

SqlCeConnection sqlCnn = new SqlCeConnection("Data Source=" + Application.StartupPath + "\\mainDB.sdf");
 SqlCeCommand sqlCmd = new SqlCeCommand("SELECT count(*) FROM Accounts WHERE (username = @user AND password = @pass)", sqlCnn);
 sqlCmd.Parameters.Add("@user", textBox1.Text);
 sqlCmd.Parameters.Add("@pass", textBox2.Text);
 sqlCnn.Open();
 int recordCount = (int)sqlCmd.ExecuteScalar();

if (recordCount > 0)
{
//dostuff
}

Answer 3

Check if your Datateader has rows with reader.HasRows.

See this SO post How to check if SQLDataReader has no rows for more info.