Parse data from a C#database?

Asked

Viewed 62 times

2

Precise deserialize the information of a database in C#, I have the following SQL in C#:

select 
      Id, 
      Email, 
      Name, 
      Login 
FROM usuarios 
WHERE Login = @login
      and Senha = CONVERT(VARCHAR(2000), HASHBYTES('MD5', @password) ,2)

And to deserialize I’m doing the following:

while (reader.Read())
{
    toReturn.Id = int.Parse(reader["Id"].ToString());
    toReturn.Email = reader["Email"].ToString();
    toReturn.Name = reader["Name"].ToString();
    toReturn.UserName = reader["Login"].ToString();
}

However, it never returns any value and does not play any Exception. What I am doing wrong?

Query in this way:

select 
      Id, 
      Email, 
      Name, 
      Login 
FROM usuarios 
WHERE Login = 'usuario'
      and Senha = CONVERT(VARCHAR(2000), HASHBYTES('MD5', '123') ,2)

It returns the correct user.

To run, I’m using this code:

cmd.CommandText = "select Id, Email, Name, Login FROM [vw_usuarios] WHERE 
Login = '@login' and Senha = CONVERT(VARCHAR(2000), HASHBYTES('MD5', '@password') ,2)";

cmd.Parameters.AddWithValue("@login", username);
cmd.Parameters.AddWithValue("@password", password);
cmd.CommandType = System.Data.CommandType.Text;

reader = cmd.ExecuteReader();

Remarks:

  • I already tested it in the database and it worked.
  • When I remove the parameters, it returns all the values.
c# database

  • Which parameters do you remove? Apparently the problem is how do you send the query, but you didn’t post that part.

  • I edited the question

  • The values of username and password at the time of this execution is as? Note that in DB is doing in the table, and in C# is doing in view, this can make a difference

  • are filled with the values that are in the bank. 'username' and '123'. When I stop using the parameters and concateno with the query works, but I would like to use with the parameters.

  • In your query it is different, so the problem should be this, it seems not a programming error but a data confusion.

1 answer

1


The error is as it is sent the information in the parameter, it does not need simple quotes in the SQL, is like this:

cmd.CommandText = "select Id, Email, Name, Login FROM [vw_usuarios] WHERE
 Login = '@login' and Senha = CONVERT(VARCHAR(2000), HASHBYTES('MD5', '@password') ,2)";

change to: 

cmd.CommandText = "select Id, Email, Name, Login FROM [vw_usuarios] WHERE
 Login = @login and Senha = CONVERT(VARCHAR(2000), HASHBYTES('MD5', @password) ,2)";

What is the difference:

The removal of single quotes in @login and @password does not need, own Parameters takes charge of placing the quotation marks bearing in mind that the data are two texts.

Complete and corrected code:

cmd.CommandText = "select Id, Email, Name, Login FROM [vw_usuarios] WHERE 
        Login = @login and
        Senha = CONVERT(VARCHAR(2000), HASHBYTES('MD5', @password) ,2)";

cmd.Parameters.AddWithValue("@login", username);
cmd.Parameters.AddWithValue("@password", password);
cmd.CommandType = System.Data.CommandType.Text;
reader = cmd.ExecuteReader();

if (reader.Read())
{
    toReturn.Id = int.Parse(reader["Id"].ToString());
    toReturn.Email = reader["Email"].ToString();
    toReturn.Name = reader["Name"].ToString();
    toReturn.UserName = reader["Login"].ToString();
}

Tip: in that link there’s an example you can see the difference.

References

Browser other questions tagged c# database

You are not signed in. Login or sign up in order to post.