如何从SQL Server中的存储过程中获取数据

I read data from my stored procedure. When I insert a breakpoint I get this situation:

我从我的存储过程中读取数据。当我插入断点时，我得到这种情况：

The stored procedure returns one row with 8 columns. You can see this on a picture. But when I am trying to read one value:

存储过程返回一行，包含8列。你可以在图片上看到这个。但是当我试图读取一个值时：

var post_id = reader[4].ToString();

I got this exception:

我有这个例外：

Invalid attempt to read when no data is present.

没有数据时读取的尝试无效。

My model:

我的模特：

  public class Alarm
{
    public long Id { get; set; }
    public DateTime StartDate { get; set; }
    public int Snoozes { get; set; }
    public bool Repeat { get; set; }
    public DateTime AlarmUpdateTime { get; set; }

    public virtual User User { get; set; }
    public virtual List<FacebookNotificationStatus> StatusUpdates { get; set; }
}

public class FacebookStatusUpdate
{
    public long Id { get; set; }
    public DateTime FacebookUpdateTime { get; set; }
    public string PostId { get; set; }
    public DateTime? FacebookPostTime { get; set; }
    public DateTimeOffset ClientTime { get; set; }
    public int Offset { get; set; }

    public virtual FacebookNotificationStatus Status { get; set; }
    public virtual Alarm Alarm { get; set; }
}

Can somebody help me?

有人能帮助我吗？

3 个解决方案

#1

after executing command.ExecuteReader() you can loop through data with next code (just sample):

在执行command.ExecuteReader（）之后，您可以使用下一个代码循环数据（只是示例）：

var rdr = command.ExecuteReader();
while(rdr.Read()) 
{
  var obj = new MyClass();
  obj.Id = (int)rdr["Id"];
  if (rdr["Name"] != DBNull.Value)
  {
    obj.name = (string)rdr["Name"];
  }
}
rdr.Close();

this sample reads all fetched data from DB row-by-row.

此示例逐行读取DB中所有获取的数据。

Also don't forget to read read SqlDataReader manual for more information how to work with readers.

另外，请不要忘记阅读SqlDataReader手册，了解有关如何与读者合作的更多信息。

#2

You need to call the reader.Read() before reading the records.

您需要在读取记录之前调用reader.Read（）。

Example:

例：

 String column1="";
 String column2="";
 while(reader.Read())
    {
      column1 = reader["column1"].ToString();
      column2 = reader["column2"].ToString();
    }

#3

Dealing with all the subtleties of ADO.NET is not fun; frankly, I suspect you might find it easier to use something like "dapper", which allows:

处理ADO.NET的所有细微之处并不好玩;坦率地说，我怀疑你可能会发现使用像“dapper”这样的东西更容易，它允许：

using(var conn = ...)
{
    return conn.Query<FacebookStatusUpdate>("GetPostList"),
       commandType: CommandType.StoredProcedure).ToList();
} 

(assuming that the column names are a direct match to the property names on FacebookStatusUpdate)

（假设列名与FacebookStatusUpdate上的属性名称直接匹配）

And for passing parameters:

并传递参数：

string region = ...
DateTime minDate = ...
using(var conn = ...)
{
    return conn.Query<FacebookStatusUpdate>("GetPostList"),
       new { region, minDate },
       commandType: CommandType.StoredProcedure).ToList();
}