ASP.NET Web API List All Records from SQL Server Table

Question

I'm trying to follow a simple example (link below) to learn Web API and am unable to get it list all records from my underlying table. The following will only list the last record in the table when making the api call.

    <HttpGet>
    Public Function GetEmployees() As Employee
        Dim reader As SqlDataReader = Nothing
        Dim myConnection As SqlConnection = New SqlConnection()
        myConnection.ConnectionString = "myconnectionstring"
        Dim sqlCmd As SqlCommand = New SqlCommand()
        sqlCmd.CommandType = CommandType.Text
        sqlCmd.CommandText = "Select * from tblEmployee"
        sqlCmd.Connection = myConnection
        myConnection.Open()
        reader = sqlCmd.ExecuteReader()
        Dim emp As Employee = Nothing
        While reader.Read()
            emp = New Employee()
            emp.EmployeeId = Convert.ToInt32(reader.GetValue(0))
            emp.Name = reader.GetValue(1).ToString()
            emp.ManagerId = Convert.ToInt32(reader.GetValue(2))
        End While

        Return emp
        myConnection.Close()
    End Function

I've tried changing the function type to the following but get the error "Unable to cast object of type 'Employee' to type 'System.Collections.Generic.IEnumerable"

 Public Function GetEmployees() As IEnumerable(Of Employee)

Credit to original tutorial: http://www.c-sharpcorner.com/UploadFile/97fc7a/webapi-restful-operations-in-webapi-using-ado-net-objects-a/

You need a generic list List<Employee>. Then you would add a new single instance inside your loop. — Sean Lange
– Sean Lange, Commented Dec 14, 2017 at 21:50

Jemes · Accepted Answer · 2017-12-14 22:20:24Z

In the code you provided, you're creating a single a variable "emp" of type "Employee". Your "While" loop executes and keeps resetting the "emp" variable on each iteration. Instead of using a single variable, you need a collection of Employees --

Public Function GetEmployees() As List(Of Employee)
    Dim reader As SqlDataReader = Nothing
    Dim myConnection As SqlConnection = New SqlConnection()
    myConnection.ConnectionString = "myconnectionstring"
    Dim sqlCmd As SqlCommand = New SqlCommand()
    sqlCmd.CommandType = CommandType.Text
    sqlCmd.CommandText = "Select * from tblEmployee"
    sqlCmd.Connection = myConnection
    myConnection.Open()
    reader = sqlCmd.ExecuteReader()
    Dim empList As New List(Of Employee)()
    While reader.Read()
        Dim emp As Employee = New Employee()
        emp.EmployeeId = Convert.ToInt32(reader.GetValue(0))
        emp.Name = reader.GetValue(1).ToString()
        emp.ManagerId = Convert.ToInt32(reader.GetValue(2))
        empList.Add(emp)
    End While

    myConnection.Close()
    Return empList
End Function

To sum up the changes --

Function should return a List of Employee instead of a single Employee object
Create a new Employee on every loop, populate it, then add it to the list
Close your connection before returning
Return the list

Collectives™ on Stack Overflow

ASP.NET Web API List All Records from SQL Server Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related