Return a List after reading from the Database

Question

I am trying to retrieve all Names, from the Names tables in the database. I am unable to retrieve the data and return it as a lIst. how can i do it ?

public List<SelectListItem> getNames()
        {
             try
            {
                using (SqlCommand com = new SqlCommand("SELECT * FROM Names", con))
                {
                    con();
                    SqlDataReader dr = com.ExecuteReader();

                    return ?? // How to return the items that was returned 

                }
            }
 .......

possible duplicate http://stackoverflow.com/questions/1464883/how-can-i-easily-convert-datareader-to-listt — bas, Feb 18 '13 at 19:45

score 1 · Answer 1 · answered Feb 18 '13 at 19:47

You can iterate over all rows returned as follows:

var items = new List<SelectListItem>();
while (dr.Read()) 
{
  var valueInColumn1 = dr[1];
  var valueInNamedColumn = dr["ColumnName"];
  ...
  items.Add(new SelectListItem { Text = valueInColumn1.ToString(), Value = valueInNamedColumn.ToString()); 
}
return items;

rene · Answer 2 · 2013-02-18T20:27:40.690

First instantiate the list to hold your items (you could also leave it null but that depends on what your callers expect) and then iterate over the datareader by calling Read() until it returns false, which means no more records are available.

When the datareader has records you can fetch a column by calling one of the methods GetString, GetInt, GetLong etc supplying it the column you want to fetch as a parameter.

Construct the type you want to store in your list and add the retrieved values to its properties, add the new type to the List.

public List<SelectListItem> getNames()
{
    var list = new List<SelectListItem>();
    try
    {
       using (SqlCommand com = new SqlCommand("SELECT * FROM Names", con))
       {
           con();
           SqlDataReader dr = com.ExecuteReader();
           while (dr.Read())
           {
               list.Add(new SelectListItem { 
                          Value = dr.GetString(0), // first column, depends on your table 
                          Text = dr.GetString(1)  // second column, depends on your table
                }); 
           }
    catch(Exception e)
    {
        Trace.WriteLine(r.Message);
    }
    return list;
}

It says that SqlDataReader doesn't contain a definition for ReadString — user1315906, Feb 18 '13 at 20:18
I didn't had a compiler stand-by and looked up the wrong msdn-doc. sorry..fixed it... — rene, Feb 18 '13 at 20:29
If i use `GetInt ` then i have to replace `Value` with something else ? — user1315906, Feb 18 '13 at 20:44
Value is of type string so you could do dr.GetInt(0).ToString() — rene, Feb 18 '13 at 21:11

score 0 · Answer 3 · answered Feb 18 '13 at 19:46

See my code example:

  public static List<ActionItem> GetAllActions()
        {
            var actionItems = new List<ActionItem>();
            SqlDataReader actionsReader = CatalogDB.GetAllActions();

            try
            {
                while (actionsReader.Read())
                {
                    actionItems.Add(new ActionItem
                                        {
                                            Id = (int)actionsReader["Id"],
                                            Name = actionsReader["Name"] != DBNull.Value ? (string)actionsReader["Name"] : null,
                                            Description = (string)actionsReader["Description"],
                                            CreationDate = (DateTime)actionsReader["CreationDate"]                                                
                                        }
                        );
                }
            }
            finally
            {
                actionsReader.Close();
            }

            return actionItems;
        }

Doesn't matter. It can be any type. It's just an example. Or you aren't able to write your code following the example? — algreat, Feb 18 '13 at 20:26

score 0 · Answer 4 · answered Feb 18 '13 at 19:48

There are a couple of different ways, but this is probably the most straight forward.

public List<SelectListItem> getNames()
{
    var list = new List<SelectedListItem>();
    try
    {
        using (SqlCommand com = new SqlCommand("SELECT * FROM Names", con))
        {
            con();
            SqlDataReader dr = com.ExecuteReader();

            while (dr.Read())
            {
                var item = new SelectedListItem();
                item.Value = dr[0];
                list.Add(item);
            }
        }
    }
    catch(Exception ex)
    {
        // ...
    }

    return list;
}

Return a List after reading from the Database

4 Answers4