MySQL DataReader only read first row and nothing else

Question

I've been trying to read data from my table Employee (7 columns) with 3 employees registered, and DataReader just read first row (7 fields instead the total, 21 fields)

[HttpGet]
[Route("GetEmployees")]
public List<clsEmployee> GetEmployees()
{
    MySqlDataReader reader;
    List<clsEmployee> employees = new List<clsEmployee>();

    string strSQL = "SELECT e.EmployeeID, e.FirstName, e.LastName, e.email, e.dni, e.phone, e.SpecialityID from Employee e";

    reader = CmdDatabase.GetReader(strSQL, configuration.GetConnectionString("da_adminsys"));

    while (reader.Read())
    {
        clsEmployee emp = new clsEmployee
        {
            EmployeeID = Convert.ToInt32(reader[0]),
            FirstName = reader["firstname"].ToString(),
            LastName = reader["lastname"].ToString(),
            Email = reader["email"].ToString(),
            Phone = reader["phone"].ToString(),
            DNI = reader["dni"].ToString(),
            SpecialityID = Convert.ToInt32(reader["SpecialityID"]),
        };

        employees.Add(emp);
    }

    foreach (var item in employees)
    {
        clsSpeciality speciality = GetSpeciality(item.SpecialityID);
        item.Speciality.Add(speciality);
    }

    reader.Close();

    return employees;
}

And GetReader from my class CmdDatabase:

public static MySqlDataReader GetReader(string strQuery, string CN)
{
    MySqlConnection  oConnection = new MySqlConnection(CN);
    MySqlCommand oCommand = new MySqlCommand(strQuery, oConnection);
    oConnection.Open();
    MySqlDataReader oReader;

    oReader = oCommand.ExecuteReader(CommandBehavior.CloseConnection);

    oCommand.Dispose();
    return oReader;
}

At the while loop its just counting 7 FieldCount, and totally in the table are 21 fields

Answer 1

You are doing this wrong, as you are disposing the command before finshing reading from the reader.

You need to have the conenction, command and reader all in using.

public List<clsEmployee> GetEmployees()
{
    var employees = GetEmployeesFromDb(configuration.GetConnectionString("da_adminsys"));

    foreach (var item in employees)
    {
        clsSpeciality speciality = GetSpeciality(item.SpecialityID);
        item.Speciality.Add(speciality);
    }

    return employees;
}

public static List<clsEmployee> GetEmployeesFromDb(string connectionString)
{
    const string strSQL = @"
SELECT
  e.EmployeeID,
  e.FirstName,
  e.LastName,
  e.email,
  e.dni,
  e.phone,
  e.SpecialityID
from Employee e;
";

    using var oConnection = new MySqlConnection(connectionString);
    using var oCommand = new MySqlCommand(strQuery, oConnection);
    oConnection.Open();
    using var oReader = oCommand.ExecuteReader();
    var employees = new List<clsEmployee>();

    while (reader.Read())
    {
        clsEmployee emp = new clsEmployee
        {
            EmployeeID = Convert.ToInt32(reader[0]),
            FirstName = reader["firstname"].ToString(),
            LastName = reader["lastname"].ToString(),
            Email = reader["email"].ToString(),
            Phone = reader["phone"].ToString(),
            DNI = reader["dni"].ToString(),
            SpecialityID = Convert.ToInt32(reader["SpecialityID"]),
        };

        employees.Add(emp);
    }
    return employees;
}

I have no idea what your GetSpeciality does, but if it's a database call then you are much better off using a single database call with a join.

---

Also consider using async especially given that this is in the middle of an ASP.Net HTTP request. That would look like this

public async Task<List<clsEmployee>> GetEmployees()
{
    var employees = await GetEmployeesFromDb(configuration.GetConnectionString("da_adminsys"));

    foreach (var item in employees)
    {
        clsSpeciality speciality = GetSpeciality(item.SpecialityID);
        item.Speciality.Add(speciality);
    }

    return employees;
}

public static async Task<List<clsEmployee>> GetEmployeesFromDb(string connectionString)
{
    const string strSQL = @"
SELECT
  e.EmployeeID,
  e.FirstName,
  e.LastName,
  e.email,
  e.dni,
  e.phone,
  e.SpecialityID
from Employee e;
";

    using var oConnection = new MySqlConnection(connectionString);
    using var oCommand = new MySqlCommand(strQuery, oConnection);
    await oConnection.OpenAsync();
    using var oReader = await oCommand.ExecuteReaderAsync();
    var employees = new List<clsEmployee>();

    while (await reader.ReadAsync())
    {
        clsEmployee emp = new clsEmployee
        {
            EmployeeID = Convert.ToInt32(reader[0]),
            FirstName = reader["firstname"].ToString(),
            LastName = reader["lastname"].ToString(),
            Email = reader["email"].ToString(),
            Phone = reader["phone"].ToString(),
            DNI = reader["dni"].ToString(),
            SpecialityID = Convert.ToInt32(reader["SpecialityID"]),
        };

        employees.Add(emp);
    }
    return employees;
}