SqlDataReader[i] datatype

Question

I'm trying to read out data from a set query row by row and put each record in an object in a list of objects, but I don't know what the datatype is for a single record in SqlDataReader.

List<Response> responseList = new List<Response>();
for (int i = 0; reader.Read(); i++) //reader is the SqlDataReader
    responseList.Add(new Response(reader[i]));

What datatype do I set the Response constructor's parameter as?

public Response(IDataRecord r) //what's the data type suppose to be?
{

}

Have you tried checking reader[i].GetType() in your immediate window? — user15741
– user15741, Commented Feb 7, 2014 at 19:44

Steve · Accepted Answer · 2014-02-07 19:52:20Z

2

reader[i] is an index to the i-th field of the current record. For example, if your query is

SELECT ID, FNAME, LNAME, ADDRESS FROM CUSTOMERS

then your loop tries to pass to your function the field ID (i=0) of the first record at the first loop, the field FNAME (i=1) of the second record in the second loop, the field LNAME (i=2) of the third record in the third loop and then the field ADDRESS (i=3) of the fourth record, finally (if you have more than four records retrieved) the code crashes with an out of range exception (i=4 but the columns are just 4)

You need to build an object Response and then fill its properties before adding it to the list.
So, still supposing that your object Response has four properties named ID, FirstName, LastName, Address then you write

List<Response> responseList = new List<Response>();
while(reader.Read()) 
{
      responseList.Add(new Response()
      {
           ID = reader[0].ToString(), 
           FirstName = reader[1].ToString(), 
           LastName = reader[2].ToString(),
           Address = reader[3].ToString()
      };
}

edited Feb 7, 2014 at 19:52

answered Feb 7, 2014 at 19:45

Steve

217k22 gold badges242 silver badges296 bronze badges

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

3 Comments

Shmoopy Over a year ago

I'm expecting a response with a massive number of rows and 15 columns. Would reader be the row array and then reader[0] thru reader[14] be the fields?

Steve Over a year ago

Yes, exactly for the fields, but the reader contains just one record, you need to call the Read() method to move it to the next record.

Steve Over a year ago

By the way, this kind of repetitive code (transfer data from db to object and back) is what the ORM are supposed to do. You could spend a little time to learn about them. (I suggest to look at Dapper)

apomene · Accepted Answer · 2014-02-07 19:50:58Z

1

At first change your reading condition, secondly you can create a List <string> for saving your data.

List<string> responseList = new List<string>();

While (reader.Read())
{
  responseList.Add(reader[i]).ToString());
}

take in account that now, all fields are saved in the same List (response List), if you wish to save each field separately: (assuming you selecting 3 fields in your sql query)

List<string> List1 = new List<string>();
List<string> List2 = new List<string>();
List<string> List3 = new List<string>();
 While (reader.Read())
    {
      List1.Add(reader[0]).ToString());///1st field of select
      List2.Add(reader[1]).ToString());///2nd field of select
      List3.Add(reader[2]).ToString());///3rd field of select

    }

edited Feb 7, 2014 at 19:50

answered Feb 7, 2014 at 19:45

apomene

14.4k10 gold badges51 silver badges76 bronze badges

1 Comment

apomene Over a year ago

Looking at Steve's answer, setting the properties of Response match each of your field, is a better approach..

Collectives™ on Stack Overflow

SqlDataReader[i] datatype

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related