Answered by:
Asp.net web api - how to retrieve all data from store procedure and return as list

Question
-
User-1121304814 posted
I am building a web service api that gets data via Stored Procedures from a db and provides the result.
so my xamarin(android/iOS) mobile app can get the data from my web api current i able to get single row,
but i didn't know how to pull all data .
here the code can anyone help me with this and improve.
this my first time doing web api and totally new to me so hope you guys can show me some eg.
public class sEmployeeRecords
{
public string sName { get; set; }
public string sPay { get; set; }
}public List<sEmployeeRecords> Get(int id,string iname)
{
List<sEmployeeRecords> employeeRecords = new List<sEmployeeRecords>();var con1 = ConfigurationManager.ConnectionStrings["EmployeeDB"];
string strConn = con1.ConnectionString;
using (SqlConnection con = new SqlConnection(strConn))
{
con.Open();
SqlCommand cmd = new SqlCommand("vEmployeeData", con);cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Mode", "VIEW BY ID");
cmd.Parameters.AddWithValue("@ID", id);
cmd.Parameters.AddWithValue("@Name", "");cmd.ExecuteNonQuery();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
sEmployeeRecords f = new sEmployeeRecords();
f.sName = reader["FirstName"].ToString();
f.sPay = reader["Salary"].ToString();
employeeRecords.Add(f);
}}
reader.Close();
con.Close();
}//END USING
return employeeRecords;
}Thank You.
Monday, October 29, 2018 9:44 PM
Answers
-
User1724605321 posted
Hi sky_091006,
if you have a lot of 1000-2000 rows , you should use paging /sorting to to limit the amount of records returned per request :
http://www.sqlservergeeks.com/t-sql-paging-stored-procedure/
https://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch
Best Regards,
Nan Yu
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Tuesday, November 6, 2018 1:47 AM
All replies
-
User1724605321 posted
Hi sky_091006,
Firstly i would suggest you debug your web api , and check whether you get correct records from db according to your conditions . Please debug step by step on below lines to confirm the result :
if (reader.HasRows) { while (reader.Read()) { sEmployeeRecords f = new sEmployeeRecords(); f.sName = reader["FirstName"].ToString(); f.sPay = reader["Salary"].ToString(); employeeRecords.Add(f); } }
Best Regards,
Nan Yu
Tuesday, October 30, 2018 6:17 AM -
User-1121304814 posted
Hi Nan Yu,
i able to pull all the information from database when using the while loop method the only thing that i was wonder is that able to pull all data without using while loop since the sql query is (SELECT * FROM [TableName])
or this is only method to pull data cause if the table have 1000-2000 row of data will it be slow when get data from api ?
Thank You
Monday, November 5, 2018 1:24 PM -
User1724605321 posted
Hi sky_091006,
if you have a lot of 1000-2000 rows , you should use paging /sorting to to limit the amount of records returned per request :
http://www.sqlservergeeks.com/t-sql-paging-stored-procedure/
https://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch
Best Regards,
Nan Yu
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Tuesday, November 6, 2018 1:47 AM