Returning a List<> using a DataReader, skips first item.

philthy · June 11th, 2009, 09:18 AM

I'm building a search module, using the TheBeerHouse architecture. I've noticed something odd.

Code:

public static List<ProfilSearch> GetProfiles(string _search)
        {
            using (SqlConnection conn = new SqlConnection(_connString))
            {

                SqlCommand cmd = new SqlCommand(_search, conn);
                cmd.CommandType = CommandType.Text;
                conn.Open();

                IDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                    return GetProfilesCollectionFromReader(reader);
                else
                    return null;
            }
        }

        public static ProfilSearch GetProfileFromReader(IDataReader reader)
        {
            ProfilSearch _Profil = new ProfilSearch(
                (int)reader["profil_id"],
                reader["profil_type"].ToString(),
                (reader["profil_active"] == DBNull.Value ? 0 : (int)reader["profil_active"]),
                reader["profil_email"].ToString(),
            reader["profil_firstname"].ToString(),
            reader["profil_lastname"].ToString(),
            reader["profil_gender"].ToString(),
            (reader["profil_areaid"] == DBNull.Value ? 0 : (int)reader["profil_areaid"]),
            (reader["profil_lastupdate"] == DBNull.Value ? DateTime.Now : (DateTime)reader["profil_lastupdate"]),
            reader["profil_imageUrl"].ToString());
            return _Profil;
        }

        public static List GetProfilesCollectionFromReader(IDataReader reader)
        {
            List _profiles = new List();
            while (reader.Read())
                _profiles.Add(GetProfileFromReader(reader));
            return _profiles;
        }

The GetProfiles method is called from the codebehind, just like we would in TheBeerHouse.

Code:

List<ProfilSearch> _profiles = null;

_profiles = ProfilSearchManager.GetProfiles(search);

search is the query string which is built in codebehind, depending on the values picked by the user.If I run that query directly in SQL Server,it retrieves, say 200 rows, and if I write out _profiles.Count, the number is 199 items (rows).

I just looked through my entire application, and in all the places I
return a List using a DataReader, the first item is missing.

Have any of you come across this in your work on TheBeerHouse, or anywhere else?
If so, do you have a solution for it?

Thnx

philip_cole · June 11th, 2009, 02:20 PM

Hi Philthy,
You are calling reader.Read() in GetProfiles(), which will set the pointer to the first item.
Then in GetProfilesCollectionFromReader, you are using while(reader.Read()), so the first time this is run, the pointer will be moved to the second item.
To fix this, you could simply change the while to a do..while:

Code:

do {
    _profiles.Add(GetProfileFromReader(reader));
} while (reader.Read());

Phil

philthy · June 11th, 2009, 03:38 PM

Thanks man, that works