ASP.NET Web API - SQL - Retrieving Information

Question

I am having some issues with retrieving information from sql database using ASP.net Web API.

I have tried to use forms, which worked great (using gridview) but when I try to do it using a separate class dedicated to store my specific table information I get this error:

"Index was out of range. Must be non-negative and less than the size of the collection."

This is the code:

  public ActionResult Details()
    {
        List<Employee> employeeList = new List<Employee>();
        string CS = ConfigurationManager.ConnectionStrings["EmployeeContext"].ConnectionString;
        using (var myConn = new SqlConnection(CS))
        {
            SqlCommand cmd = new SqlCommand("select * from tblEmployee", myConn);
            myConn.Open();
            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                int i = 0;
                employeeList[i].PersonID = Convert.ToInt32(rdr["PersonID"]);
                employeeList[i].Name = rdr["Name"].ToString();
                employeeList[i].Gender = rdr["Gender"].ToString();
                employeeList[i].City = rdr["City"].ToString();
                employeeList[i].DepartmentID = Convert.ToInt32(rdr["DepartmentID"]);
                i++;
            }
            return View(employeeList);
        }
    }

This is the Employee class:

[Table("tblEmployee")]
public class Employee
{
    public int PersonID { get; set; }
    public string Name { get; set; }
    public string Gender { get; set; }
    public string City { get; set; }
    public int DepartmentID { get; set; }

} }

I get this error on any of the retrieving information lines.

The table has 5 columns: PersonID(int PK), Name(nvarchar), Gender(nvarchar), City(nvarchar), DepartmentID(int).

I checked many times the columns names to make sure I didn't got those wrong and I double checked the connection string which is also fine (the same code works with gridview using forms API).

Hope someone can help me with this. I didn't find any specific information on that and I guess it's should be easy and I'm doing something wrong here.

Siyual · Accepted Answer · 2016-06-10 14:53:39Z

2

You are trying to populate a List<> object by using an index. To populate a List<> you need to use .Add(). You need to change your code from this:

int i = 0;
employeeList[i].PersonID = Convert.ToInt32(rdr["PersonID"]);
employeeList[i].Name = rdr["Name"].ToString();
employeeList[i].Gender = rdr["Gender"].ToString();
employeeList[i].City = rdr["City"].ToString();
employeeList[i].DepartmentID = Convert.ToInt32(rdr["DepartmentID"]);
i++;

To this:

Employee emp = new Employee();

emp.PersonID     = Convert.ToInt32(rdr["PersonID"]);
emp.Name         = rdr["Name"].ToString();
emp.Gender       = rdr["Gender"].ToString();
emp.City         = rdr["City"].ToString();
emp.DepartmentID = Convert.ToInt32(rdr["DepartmentID"]);

employeeList.Add(emp);

answered Jun 10, 2016 at 14:53

Siyual

17k8 gold badges47 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sgeddes · Accepted Answer · 2016-06-10 14:53:57Z

1

When adding a new item to a list, you should use .Add(). Here's one option:

while (rdr.Read())
{
    employeeList.Add(new Employee {
        PersonID = Convert.ToInt32(rdr["PersonID"]),
        Name = rdr["Name"].ToString(),
        Gender = rdr["Gender"].ToString(),
        City = rdr["City"].ToString(),
        DepartmentID = Convert.ToInt32(rdr["DepartmentID"])
    });
}

You could then access individual items in the list with their index or by using foreach.

answered Jun 10, 2016 at 14:53

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Collectives™ on Stack Overflow

ASP.NET Web API - SQL - Retrieving Information

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related