Get int value from command in c#

Question

string sql = "Select UserId From User where UserName='Gheorghe'";


SqlCommand cmd=new SqlCommand(sql, connection);
cmd.ExecuteScalar(); //this statement return 0

but I want to get the id of user? how can I get it?

Answer 1

You need the SqlDataReader.

SqlDataReader Provides a way of reading a forward-only stream of rows from a SQL Server database.

Sample

string sql = "Select UserId From User where UserName='Gheorghe'";

SqlCommand cmd=new SqlCommand(sql, connection);
SqlDataReader rd = cmd.ExecuteReader(); 
if (rd.HasRows) {
  rd.Read(); // read first row
  var userId = rd.GetInt32(0);
}

More Information

• MSDN - SqlDataReader Class

Answer 2

Simply cast the returned value:

int userId = (Int32)cmd.ExecuteScalar();

But be aware that ExecuteScalar will return null if your query returns an empty result set, and in that case the above code snippet will throw an InvalidCastException.