SqlCommand only returns one row

Question

Trying to only return the first few number of rows because my database was too big, however when I was testing my SQL, I did a select * and was only returned the first row.

SqlCommand sqlCmd = new SqlCommand();
SqlDataReader reader;

sqlCmd.CommandText = "SELECT * FROM Log";
sqlCmd.CommandType = CommandType.Text;
sqlCmd.Connection = myConnection;

myConnection.Open();

reader = sqlCmd.ExecuteReader();

Log logs = null;

while (reader.Read())
{
    logs = new Log();
    logs.Id = Convert.ToInt32(reader.GetValue(0));
}

return logs;
myConnection.Close();

What is wrong with my solution?

what is the value column name at position 0 ? also use the debugger to see what the records count is. does the Log have an `Add` method ? — MethodMan, Aug 10 '16 at 21:09
You're replacing `logs` every iteration of the loop. You're probably looking at add to a `List` or something to that effect. As you have it, you can indeed only get one record. — Glorin Oakenfoot, Aug 10 '16 at 21:09
Also, note that putting `myConnection.Close();` after the `return` is a bug, as that code is unreachable. Use a using or try/finally or just put it before the return. — Glorin Oakenfoot, Aug 10 '16 at 21:14

score 9 · Accepted Answer · edited Aug 10 '16 at 21:20

9

In your loop you constantly re-create the instance of the variable logs thus overwriting the previous one and ending with the value of the last record returned by your query. You need a List<Log>

    List<Log> logs = new List<Log>();
    while (reader.Read())
    {
        Log current = new Log();
        logs.Id = Convert.ToInt32(reader.GetValue(0));
        logs.Add(current);
    }

You can use this list as datasource for a grid or just keep it for your future references...

    foreach(Log log in logs)
       MessageBox.Show(log.Id);

edited Aug 10 '16 at 21:20

Raktim Biswas

4,011
5
27
32

answered Aug 10 '16 at 21:10

Steve

213,761
22
232
286

very nice indeed :) – user1234433222 Aug 10 '16 at 21:12

SqlCommand only returns one row

1 Answers1