C# SQL Server Compact Database Login

Question

I am creating a desktop application in C#. I am creating a Login Form. I have a table named MyTable with columns username and password.

I want to authenticate the user, so I guess I want to know the number of rows affected from SQL query. But every time I got -1 by executing ExecuteNonQuery() method.

My code:

string Username = username.Text;
string Password = password.Text;
SqlCeCommand commandSelect = new SqlCeCommand("SELECT * FROM MyTable WHERE username=@username AND password=@password", connection);
commandSelect.Parameters.AddWithValue("@username", Username);
commandSelect.Parameters.AddWithValue("@password", Password);
int rows = commandSelect.ExecuteNonQuery();
MessageBox.Show(rows.ToString());

Thanks in advance.

Answer 1

You might be better off changing your SQL to get the count like so:

SELECT COUNT(*) FROM MyTable WHERE username=@username AND password=@password

And then use ExecuteScalar() instead of ExecuteNonQuery. This will be less likely to cause you headaches in the future.