c# - Fill generic list from SqlDataReader

Question

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 CategoryID a generic List.

This dose not work because it returns only one categoryID and that is the last one. I want to add all the categoryID to the list and then return them.

How do I do that?

SqlConnection connection = null;
SqlDataReader reader = null;
SqlCommand cmd = null;

try
{
    connection = new SqlConnection(connectionString);
    cmd = new SqlCommand("select CategoryID from Categories", connection );

    connection.Open();

    List<int> catID = new List<int>();
    dr = cmd.ExecuteReader();
    while (dr.Read())
    {
        catID.Add(Convert.ToInt32(dr["CategoryID"].ToString()));
    }
}
finally
{
    if (connection  != null)
        connection.Close();
}
return catID;

Use using statements around the connection, command and the reader! — RQDQ
– RQDQ, Commented Apr 10, 2011 at 16:51

Darin Dimitrov · Accepted Answer · 2011-04-10 16:50:28Z

15

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())
            {
                yield return reader.GetInt32(reader.GetOrdinal("CategoryID"));
            }
        }
    }
}

and then:

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

answered Apr 10, 2011 at 16:50

Darin Dimitrov

1.0m275 gold badges3.3k silver badges3k bronze badges

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

4 Comments

BrokenGlass Over a year ago

@Darim: While I like this code that would make it the responsibility of the consumer though to call ToList() - otherwise you have an open DB connection. I'm not convinced lazy is the right approach here.

nawfal Over a year ago

@BrokenGlass no. the connection, the reader, all that stuff get closed once foreach is exited. Apart from the merit of this, a drawback would be that the caller will have to re-read the data from database if you have to re-use the objects in the IEnumerable and if you haven't stored all of them somewhere already. I mean in case you do not "foreach" fully. With ToList() you already have them somewhere..

Scott Chamberlain Over a year ago

GetOrdnal really is not necessary, especially where the query is a single column like this, reader.GetOrdinal("CategoryID") can just be replaced with 0

Jesuraja Over a year ago

Can we do it for List<T>?

BrokenGlass · Accepted Answer · 2011-04-10 16:47:31Z

3

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

answered Apr 10, 2011 at 16:47

BrokenGlass

161k28 gold badges297 silver badges339 bronze badges

2 Comments

Erik Over a year ago

catID is declared. When I test my code I only get back one CategoryID instead of four that I have in my table.

BrokenGlass Over a year ago

@Erik: Can you update your sample code with the real code you are running, you might be new-ing out the list every time, but above code is clearly not what you are using currently.

anishMarokey · Accepted Answer · 2011-04-10 16:53:24Z

AS BrokenGlass explained this is the demonstration

SqlConnection connection = null;
        SqlDataReader dr= null;
        SqlCommand cmd = null;
List<int> catID = new List<int>();
        try
        {
            connection = new SqlConnection(connectionString);
            cmd = new SqlCommand("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

SqlDataReader reader = null;

to

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

Bastardo · Accepted Answer · 2011-04-10 17:01:02Z

0

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

    SqlConnection connection = null;
    SqlDataReader reader = null;
    SqlCommand cmd = null;
    List<int> catID = new List<int>();
    try
    {
        connection = new SqlConnection(connectionString);
        cmd = new SqlCommand("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;

edited Apr 10, 2011 at 17:01

answered Apr 10, 2011 at 16:54

Bastardo

4,1529 gold badges43 silver badges60 bronze badges

Comments

Asanga · Accepted Answer · 2013-08-14 04:23:35Z

0

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

answered Aug 14, 2013 at 4:23

Asanga

1

Collectives™ on Stack Overflow

c# - Fill generic list from SqlDataReader

5 Answers 5

4 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related