How to return all row from API

Question

1.50/5 (2 votes)

See more:

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;
}

Posted 10-Jun-17 6:56am

Member 4773987

Updated 10-Jun-17 12:56pm

RickZeeland

v2

Add a Solution

Comments

RickZeeland 10-Jun-17 14:50pm

It seems your query looks at id: AND s.Id > id, could that be the problem ?

Afzaal Ahmad Zeeshan 10-Jun-17 16:36pm

Although I have provided the code for this solution, but the thing is you need to reconsider your approach to building the Web APIs, the item found at "id" must always be a single, scalar value, and should not be a list or container.

Please see Solution 1.

2 solutions

Solution 2

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

Posted 10-Jun-17 12:56pm

Member 4773987

Comments

Richard Deeming 12-Jun-17 15:53pm

1) If you want to reply to a solution, use the "Have a Question or Comment?" button under that solution. DO NOT post your comment as a new solution.

2) You avoid SQL Injection by using a properly parameterized query:

const string query = "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 INNER JOIN Rooms a ON a.ID = s.Room_No INNER JOIN Room_Type ON b.ID = s.Room_Type Where s.Id > @id";

using (SqlConnection conn = new SqlConnection(source))
using (SqlCommand cmd = new SqlCommand(query, conn))
{
    cmd.Parameters.AddWithValue("@id", id);
    
    conn.Open();
    using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
    {
        while (reader.Read())
        {
            ...
        }
    }
}

Richard Deeming 12-Jun-17 15:53pm

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Afzaal Ahmad Zeeshan · Accepted Answer · 2017-06-10T10:35:00

Have a look at your code,

C#

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;

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,

C#

// 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;

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

C#

public List<Reservation> Get(int id) {

After all of this, you should look into the SQL Injection hack, which your code can easily be attacked with. Never concatenate the SQL queries, always try to parametrize them to save yourself.

SQL injection - Wikipedia[^]