Returning List from Database

Question

I have a simple problem that I have not been able to find an answer to despite much Googling. Perhaps posting this question up will help the next poor soul who looks for the same answer.

I have a query that returns a collection of distinct ID's.

I am creating an SQLCommand with this query and using ExecuteScalar( ) to run it.

ExecuteScalar( ) appears to return an object and will not allow me to cast it to List.

What is the best practice to fix this and how can I fix this problem?

score 6 · Accepted Answer · edited Jun 19 '12 at 16:26

6

execute scalar returns a single piece of data. think one row, one column. if you want to return a collection you need to call ExecuteReader(). this will return an IDataReader. you can then iterate over the records and return you list.

edited Jun 19 '12 at 16:26

Pranay Rana

175,020
35
237
263

answered Jun 19 '12 at 16:16

Jason Meckley

7,589
1
24
45

score 3 · Answer 2 · answered Jun 19 '12 at 16:16

ExecuteScalar:

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

You need to use SqlDataReader:

SqlCommand command = new SqlCommand(queryString, connection);
IList<int> ints = new List<int>();
using(SqlDataReader reader = command.ExecuteReader()) {
     while (reader.Read())
     {
         ints.add(reader.GetInt32(0)); // provided that first (0-index) column is int which you are looking for
     }
}

Pranay Rana · Answer 3 · 2012-06-20T06:40:55.807

2

ExecuteScalar( ) - return only first row and first column....

Check msdn : SqlCommand.ExecuteScalar

You need to user something like this to get result i.e ExecuteReader

 using (IDataReader reader = 
        command.ExecuteReader(CommandBehavior.CloseConnection))
 {
      List<int> intlist = ReadList(reader);
 }

method to create list

List<int> ReadList(IDataReader reader)
{
    List<int> list = new List<int>();
    int column = reader.GetOrdinal("MyColumn");

    while (reader.Read())
    {
        //check for the null value and than add 
        if(!SqlReader.IsDBNull(column))
             list.Add(reader.GetInt32(column));
    }

    return list;
}

edited Jun 20 '12 at 06:40

answered Jun 19 '12 at 16:16

Pranay Rana

175,020
35
237
263

this will throw if a null db value is returned. – Jason Meckley Jun 19 '12 at 16:50
@JasonMeckley - thanks for pointing that i just improved code ............ – Pranay Rana Jun 19 '12 at 17:47
no problem. I've run into this on more than one occasion – Jason Meckley Jun 19 '12 at 17:55

score 2 · Answer 4 · answered Jun 19 '12 at 16:18

ExecuteScalar method returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

If you want to have a list of items, You should use ExecuteReader and loop thru the reader and read the value needed

List<int> items=new List<int>();
using (IDataReader reader = db.ExecuteReader(dbCommand))
{
      while (reader.Read())
      {
         items.Add(reader.GetInt32(reader.GetOrdinal("YourColumnName"));
      }
}

Returning List from Database

4 Answers4