SQLCommand.ExecuteScalar () - why does it throw a System.NullReferenceException?

Question

Can anyone notice what might be wrong with the following function:

public string Login(string username, string password)
    {
        string result = "";
        string select = "SELECT user_id FROM [user] WHERE username = @username AND password = @password";
        SqlConnection conn = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(select, conn);
        cmd.Parameters.AddWithValue("username", username);
        cmd.Parameters.AddWithValue("password", password);
        int userID = 0;
        try
        {
            conn.Open();
            userID = (int)cmd.ExecuteScalar();
            if(userID > 0)
            {
                result = addSession(userID);
            }
        }
        catch(Exception ex)
        {
            string sDummy = ex.ToString();
        }
        return result;
    }

I don't know why the string `userID = (int) cmd.ExecuteScalar (); throws an exception.

thanks

Answer 1

Most likely there is no row in the table with this user / password. The docs for ExecuteScalar say that it returns null if the result set is empty and you cannot discard the null value in int.

Answer 2

You should consider changing this code segment:

try
{
    conn.Open();
    userID = (int)cmd.ExecuteScalar();
    if(userID > 0)
    {
        result = addSession(userID);
    }
 }
 catch(Exception ex)
 {
    string sDummy = ex.ToString();

 }
 finally // add this to ensure the connection is closed!
 {
     if (conn != null)
       conn.Close();
 }

Answer 3

, , ?

Answer 4

, "@" :

...AddWithValue("@username", username);

Answer 5

SqlCE. , , ( , CORRECTLY... > . > ), ExecuteScalar . ,

Object o = cmd.ExecuteScalar(); 
int id = Convert.ToInt32(o); 

int id = (int) cmd.ExecuteScalar(); 

. , ...

Answer 6

hashbytes, :

, hashbytes SQL . Hashbytes varbinary. , , . SQL hashbytes('SHA2_512,'stuff'), 's', 't', 'f'. '\ 0'. sqlcommand, '\ 0' , SQL . , Encoding , . , addwithvalue .

But you know that exectescalar returns an object. If the query returns null strings, the object will be NULL, and you cannot overlay or convert the NULL object to anything. Therefore, in the if statement we say: "if the returning object is == null, then you are not authorized. Else ..."