Using SQL datareader to extract 1 to many custom object in c#

Question

I'm having issues with the SQL reader class.

I have a custom object named Airport on the database. But im having trouble using the datareader correctly. When i try to extract all Airports into a list(see method 2 below) it seems to jump of the while(_reader.Read()) loop before adding the object to the list.

Any suggestions?

To extract the object do i use 3 methods:

To find a specific object:

public Airport FindAirportByCode(string _airportCode)
{
    con.Open();
    string query = "SELECT * from Airport WHERE airportCode = '" + _airportCode + "'";
    SqlCommand cmd = new SqlCommand(query, con);
    SqlDataReader _reader = cmd.ExecuteReader();

    Airport retAirport = BuildAirport(_reader);
    _reader.Close();
    con.Close();
    return retAirport;
}

To get all Airports into a list

public List<Airport> SelectAll()
{
    con.Open();
    List<Airport> airports = new List<Airport>();
    string query = "SELECT * from Airport";
    SqlCommand cmd = new SqlCommand(query, con);

    SqlDataReader _reader = cmd.ExecuteReader();

    while (_reader.Read())
    {
        Airport temAirport = new Airport();
        temAirport = BuildAirport(_reader);
        airports.Add(temAirport); // It seems to skip this step and only add the last Airport from BuildAirport to the list.
    }

    _reader.Close();
    con.Close();
    return airports;
}

To build it into an object in C#

    private Airport BuildAirport(SqlDataReader _reader)
{

    Airport temAirport = new Airport();
    while (_reader.Read())
    {
        temAirport.airportCode = (string) _reader["airportCode"];
        temAirport.airportName = (string) _reader["airportName"];
        temAirport.country = (string) _reader["country"];
        temAirport.city = (string) _reader["city"];
    }
    _reader.Close();



    return temAirport;
}

your select query with parameter is prone to sql injection attack..Use SqlParameter — Anirudha
– Anirudha, Commented Nov 7, 2013 at 14:26

gzaxx · Accepted Answer · 2013-11-07 14:29:30Z

1

In third step you are enumarating reader again and that is why you get only one airport when you want to retrieve all of them.

private Airport BuildAirport(SqlDataReader _reader)
{

    Airport temAirport = new Airport();
    temAirport.airportCode = (string) _reader["airportCode"];
    temAirport.airportName = (string) _reader["airportName"];
    temAirport.country = (string) _reader["country"];
    temAirport.city = (string) _reader["city"];
    return temAirport;
}

And now we have to change your first method because changing BuildAirport breaks it. We have to read one row in FindAirportByCode now.

public Airport FindAirportByCode(string _airportCode)
{
    con.Open();
    string query = "SELECT * from Airport WHERE airportCode = @airportCode";
    SqlCommand cmd = new SqlCommand(query, con);
    cmd.Parameters.AddWithValue("@airportCode", _airportCode);

    SqlDataReader _reader = cmd.ExecuteReader();

    Airport retAirport = null;
    if (_reader.Read())
    {
         retAirport = BuildAirport(_reader);
    }

    _reader.Close();
    con.Close();
    return retAirport;
}

Use parametrized queries for security.

edited Nov 7, 2013 at 14:29

answered Nov 7, 2013 at 14:22

gzaxx

17.6k2 gold badges40 silver badges55 bronze badges

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

3 Comments

Tim Over a year ago

Why pass the reader when you could pass only the current row?

Simon Nordahl Over a year ago

Could you it explain it a little bit mate ?

gzaxx Over a year ago

@SimonNordahl you were traversing _reader in both second and third method. That resulted in only one airport added to list.

Collectives™ on Stack Overflow

Using SQL datareader to extract 1 to many custom object in c#

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related