如何修复代码以在 C# Web API 中从 SqlDataReader 返回完整列表而不是一行

Question

我正在构建一个连接到SQL的Web API，并尝试从数据库中返回"StartDateTime"值等于今天日期的所有行。出于某种原因，我只得到一行，而不是符合定义的所有行。我需要更改什么？

public class EventsController : ApiController
{
public List<tblEventDate> Get()
{
using (CalendarEntities entities = new CalendarEntities())
{
tblEventDate singleEvent = new tblEventDate();
List<tblEventDate> eventList = new List<tblEventDate>();
string strcon = ConfigurationManager.ConnectionStrings["DbConnCalendar"].ConnectionString;
SqlConnection con = new SqlConnection(strcon);
con.Open();
string comm = "SELECT [dbo].[tblEventDates].[EventID], [dbo].[tblEventDates].[StartDateTime], WHERE (CONVERT(date, dbo.tblEventDates.StartDateTime) = CONVERT(date, GETDATE()))";
SqlCommand cmd = new SqlCommand(comm, con);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.HasRows)
{
while (reader.Read())
{
singleEvent.StartDateTime = (DateTime)(reader["StartDateTime"]);
singleEvent.EventID = (long)reader["EventID"];
eventList.Add(singleEvent);
}
reader.NextResult();
}
con.Close();
}
return eventList;
}
}
}

Answer 1

试试这个：

conn.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
if (reader.HasRows)
{
singleEvent.StartDateTime = (DateTime)(reader["StartDateTime"]);
singleEvent.EventID = (long)reader["EventID"];
eventList.Add(singleEvent);
}
}