C# - Fill Generic List From Sqldatareader

How can I add values that a SqlDataReader returns to a generic List? I have a method where I use SqlDataReader to get CategoryID from a Category table. I would like to add all the

Solution 1:

Try like this, it's better, safer, uses lazy loading, less code, working, ...:

public IEnumerable<int> GetIds()
{
    using (var connection = new SqlConnection(connectionString))
    using (var cmd = connection.CreateCommand())
    {
        connection.Open();
        cmd.CommandText = "select CategoryID from Categories";
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                yieldreturn reader.GetInt32(reader.GetOrdinal("CategoryID"));
            }
        }
    }
}

and then:

List<int> catIds = GetIds().ToList();

Solution 2:

Your current code should work, assuming catID is really declared before the try block, otherwise this won't compile.

Solution 3:

AS BrokenGlass explained this is the demonstration

SqlConnectionconnection=null;
        SqlDataReader dr= null;
        SqlCommandcmd=null;
List<int> catID = newList<int>();
        try
        {
            connection = newSqlConnection(connectionString);
            cmd = newSqlCommand("select CategoryID from Categories", connection );

            connection.Open();



            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                catID.Add(Convert.ToInt32(dr["CategoryID"].ToString()));
            }


        }
        finally
        {
            if (connection  != null)
                connection.Close();
        }
        return catID;

as well as you change the declaration

SqlDataReaderreader=null;

to

SqlDataReader dr= null; // Because you are using dr in the code not reader

Solution 4:

This should work but I suggest you to use using with your connections

SqlConnectionconnection=null;
    SqlDataReaderreader=null;
    SqlCommandcmd=null;
    List<int> catID = newList<int>();
    try
    {
        connection = newSqlConnection(connectionString);
        cmd = newSqlCommand("select CategoryID from Categories", connection );

        connection.Open();



        dr = cmd.ExecuteReader();
        while (dr.Read())
        {   
            catID.Add(Convert.ToInt32(dr["CategoryID"].ToString()));
        }


    }
    finally
    {
        if (connection  != null)
            connection.Close();
    }
    return catID;

Solution 5:

        List<int> s = newList<int>();
        conn.Open();
        SqlCommandcommand2= conn.CreateCommand();
        command2.CommandText = ("select turn from Vehicle where Pagged='YES'");
        command2.CommandType = CommandType.Text;
        SqlDataReaderreader4= command2.ExecuteReader();
        while (reader4.Read())
        {
            s.Add(Convert.ToInt32((reader4["turn"]).ToString()));
        }
        conn.Close();