c# - 如何使用 sqlCommand.ExecuteReader(); 返回数据？

Question

我有 Sqlite 方法来进行 SELECT 查询:

try {
 myConn.Open();

 using(SQLiteCommand sqCommand = new SQLiteCommand(sql, myConn)) {
  sqCommand.CommandText = sql;
  SQLiteDataReader reader = sqCommand.ExecuteReader();
  return reader.GetString(0);
 }
} catch (Exception e) {
 // do exception handling
}

我尝试获取最后插入的 ID:

sql = 'SELECT id FROM Pacients ORDER BY id DESC LIMIT 1';

我尝试这样做:

return reader.GetString(0);

它让我因异常“没有当前行”而感到沮丧

Answer 1

调用 ExecuteReader 后，您需要调用 Read 来定位数据集的第一条记录。 Read 返回 true/false 来通知您是否有记录要读取。所以你的代码更改为

try {
 myConn.Open();

 using(SQLiteCommand sqCommand = new SQLiteCommand(sql, myConn)) {
  sqCommand.CommandText = sql;
  SQLiteDataReader reader = sqCommand.ExecuteReader();
  if(reader.Read())
     return reader.GetString(0);
  else
     return ""; // or whatever you want to return if no records are present
 }
} catch (Exception e) {
 // do exception handling
}

也就是说，请记住，如果您只想从查询中的单行中检索一列，那么最好使用 ExecuteScalar 而不是 ExecuteReader

try {
 myConn.Open();

 using(SQLiteCommand sqCommand = new SQLiteCommand(sql, myConn)) {
  sqCommand.CommandText = sql;
  object result = sqCommand.ExecuteScalar();
  return result != null ? result.ToString() : "";
 }
} catch (Exception e) {
 // do exception handling
}