locked
Asp.net web api - how to retrieve all data from store procedure and return as list RRS feed

  • 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

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