如何从API返回所有行 [英] How to return all row from API

问题描述

以下是通过API获取数据的代码，如何返回不仅仅是最后一行的所有行：



我尝试过的方法：



我有以下API，只返回表中的最后记录，如何让它返回多个记录依赖于select语句：

The below is the code for getting data through API, how to return all rows not just last row :

What I have tried:

I have the below API which returns just last records in the Table, how to let it return multiple records dependend on the select statements:

[HttpGet]
[ActionName("GetReservationByID")]
public Reservation Get(int id)
{
string source = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection conn = new SqlConnection(source);
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
string sql = "Select s.Id,a.Room_No ,s.Room_Description,s.Room_Capacity, b.Description, s.Location,s.Start_Date,s.Start_Time,s.End_Time,s.Meeting_Title,s.Reservation_Reason, s.Status,s.Attendance,s.Remarks,s.Mail_To,s.Mail_CC from Reservation s , Rooms a, Room_Type b Where s.Room_No = a.ID And s.Room_Type = b.ID AND s.Id >" + id + "";
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
Reservation emp = null;
conn.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
//read data
emp = new Reservation();
emp.Id = Convert.ToInt32(reader.GetValue(0));
emp.Room_No = reader.GetValue(1).ToString();
emp.Room_Description = reader.GetValue(2) as string;//reader.GetValue(3).ToString();
emp.Room_Capacity = (reader.GetValue(3) as int?) ?? 0; //Convert.ToInt32(reader.GetValue(4));
emp.Room_Type = reader.GetValue(4) as string;//reader.GetValue(5).ToString();
emp.Location = reader.GetValue(5) as string; //reader.GetValue(6).ToString();
emp.Start_Date = reader.GetValue(6) as string; //reader.GetValue(7).ToString();
emp.Start_Time = reader.GetValue(7) as string; //reader.GetValue(8).ToString();
emp.End_Time = reader.GetValue(8) as string; //reader.GetValue(9).ToString();
emp.Meeting_Title = reader.GetValue(9) as string; //reader.GetValue(10).ToString();
emp.Reservation_Reason = reader.GetValue(10) as string; //reader.GetValue(11).ToString();
emp.Status = reader.GetValue(11) as string; //reader.GetValue(12).ToString();
emp.Attendance = (reader.GetValue(12) as int?) ?? 0;//Convert.ToInt32(reader.GetValue(13));
emp.Remarks = reader.GetValue(13) as string;//reader.GetValue(14).ToString();
emp.Mail_To = reader.GetValue(14) as string;//reader.GetValue(15).ToString();
emp.Mail_CC = reader.GetValue(15) as string;//reader.GetValue(16).ToString();
}
conn.Close();
return emp;
}

推荐答案

查看你的代码，

Have a look at your code,

while (reader.Read())
{
//read data
emp = new Reservation();
emp.Id = Convert.ToInt32(reader.GetValue(0));
emp.Room_No = reader.GetValue(1).ToString();
emp.Room_Description = reader.GetValue(2) as string;//reader.GetValue(3).ToString();
emp.Room_Capacity = (reader.GetValue(3) as int?) ?? 0; //Convert.ToInt32(reader.GetValue(4));
emp.Room_Type = reader.GetValue(4) as string;//reader.GetValue(5).ToString();
emp.Location = reader.GetValue(5) as string; //reader.GetValue(6).ToString();
emp.Start_Date = reader.GetValue(6) as string; //reader.GetValue(7).ToString();
emp.Start_Time = reader.GetValue(7) as string; //reader.GetValue(8).ToString();
emp.End_Time = reader.GetValue(8) as string; //reader.GetValue(9).ToString();
emp.Meeting_Title = reader.GetValue(9) as string; //reader.GetValue(10).ToString();
emp.Reservation_Reason = reader.GetValue(10) as string; //reader.GetValue(11).ToString();
emp.Status = reader.GetValue(11) as string; //reader.GetValue(12).ToString();
emp.Attendance = (reader.GetValue(12) as int?) ?? 0;//Convert.ToInt32(reader.GetValue(13));
emp.Remarks = reader.GetValue(13) as string;//reader.GetValue(14).ToString();
emp.Mail_To = reader.GetValue(14) as string;//reader.GetValue(15).ToString();
emp.Mail_CC = reader.GetValue(15) as string;//reader.GetValue(16).ToString();
}
conn.Close();
return emp;

您将返回\\ temp对象，并在每次迭代中返回记录，您正在创建它的新实例，并且您将其值设置为当前记录 - 在迭代结束时，该记录是最后一条记录。这就是为什么，您的API仅返回最后一个。



除此之外，我建议你添加一个通用的List类型并将emp对象存储在该列表中。像这样的东西，

You are returning the "emp" object, and in every iteration of record, you are creating a new instance of it, and you are setting its value to the current record — which, by the end of the iteration is the last record. That is why, your API only returns the last one.

Instead of this, I would recommend that you add a generic List type and store the emp objects in that list. Something like this,

// Somewhere before list
List<Reservation> emps = new List<Reservation>();

while (reader.Read()) {
   emp = new Reservation();

   // Other code in the loop
   // Last line
   emps.Add(emp);
}

// Return emps, not emp
return emps;

要返回emps，您还需要更改签名，

To return the "emps" you will also need to change the signature,

public List<Reservation> Get(int id) {

完成所有这些后，您应该查看SQL注入攻击，您的代码很容易被攻击。永远不要连接SQL查询，总是尝试对它们进行参数化以节省自己。



SQL注入 - 维基百科 [ ^ ]

非常感谢，Solved，但对于SQL注入，我需要从SQL DB获取数据，这是我知道获取数据的唯一方法。真的很感谢你的帮助。



谢谢

Ikrami

Thank you So much, Solved, but regarding to SQL Injection, i need to get the data from SQL DB and this is the only way i know to get the data. Really appreciate your help.

Thanks
Ikrami

这篇关于如何从API返回所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！