string sql = "Select UserId From User where UserName='Gheorghe'";
SqlCommand cmd=new SqlCommand(sql, connection);
cmd.ExecuteScalar(); //this statement return 0
我想获取用户的id,该怎么做呢?
您需要使用 SqlDataReader
。
SqlDataReader 提供了一种从 SQL Server 数据库中读取单向行流的方法。
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);
}
只需将返回的值进行转换:
int userId = (Int32)cmd.ExecuteScalar();
但请注意,如果您的查询返回一个空结果集,ExecuteScalar将返回null,在这种情况下,上面的代码片段将抛出InvalidCastException异常。
try with select TOP 1 and ExecuteScalar
string sql = "Select TOP 1 UserId From User where UserName='Gheorghe'";
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using(SqlCommand cmd = new SqlCommand(sql, conn))
{
var result = (Int32)cmd.ExecuteScalar();
}
}