CODEWITHSUNDEEP
c#sql-server
user3407039
user3407039

Reputation: 1335

Reading multiple rows with SQL reader

So here is the code I currently have, when reading one result from a table works fine,

SqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
    newValue = reader.GetString(0);
    newPageID = reader.GetInt32(1);
}

However, the problem comes when there are multiple rows that are being returned. The way I tried to do this was like so,

int counter = 0;
List<Trigger> TriggerValues = new List<Trigger>();

while (reader.Read())
{
    TriggerValues.Add(new Trigger(reader.GetString(counter), reader.GetInt32(counter+1)));
    counter++;

}

But this does not work, which I think is because reader will only return one row. Is there a simple way to modify what I have here to read in new objects row by row? Thanks.

Upvotes: 1

Views: 6734

Answers (2)

entropic
entropic

Reputation: 1683

Everytime you execute reader.Read(), you are loading a new row. The indices you are supplying to the .GetString() and .GetInt32() functions is a column index, not a row index.

You simply need to call 

List<Trigger> TriggerValues = new List<Trigger>();
while (reader.Read())
{
    TriggerValues.Add(new Trigger(reader.GetString(0), reader.GetInt32(1)));
}

Upvotes: 4

Ilia Maskov
Ilia Maskov

Reputation: 1898

Look at GetString(0) GetInt32(1), 0 and 1 are the column index, not row. So you should leave it and remove counter

List<Trigger> TriggerValues = new List<Trigger>();

while (reader.Read())
{
    TriggerValues.Add(new Trigger(reader.GetString(0), reader.GetInt32(1)));
}

Upvotes: 8

Related Questions