The same query works fine in SQL Server. I need it to return all rows read from the DB table in c#.
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandText = @"SELECT TypeName, Sum(HitNo) FROM TypeHit GROUP BY TypeName";
SqlDataReader sdr = cmd1.ExecuteReader();
sdr.Read();
if (sdr.HasRows)
{
while (sdr.Read())
{
TextBox1.Text = sdr.GetString(0) +" at " + sdr.GetInt32(1);
}
}
Why are you using sdr.Read(), that will advance the reader to the next block of data?
You're also overwriting the TextBox'-Text property for every record in the reader since while (sdr.Read()) is a loop.
using(SqlDataReader sdr = cmd1.ExecuteReader())
{
if (sdr.HasRows)
{
while (sdr.Read())
{
TextBox1.Text += sdr.GetString(0) + " at " + sdr.GetInt32(1) + Environment.NewLine;
}
}
else
{
TextBox1.Text = "No rows found.";
}
}
You need to append result in loop:
TextBox1.Text = TextBox1.Text+sdr.GetString(0) +" at " + sdr.GetInt32(1);
Also, from performance point of view, it's better to use StringBuilder:
StringBuilder bld = new StringBuilder();
if (sdr.HasRows)
{
while (sdr.Read())
{
bld.Append(sdr.GetString(0));
bld.Append(" at ");
bld.Append(sdr.GetInt32(1));
}
}
TextBox1.Text = bld.ToString();
In each loop you replace the last TextBox1.Text with the data fetched.
So you end up with only the last line. If you need to see all the results you need something like this.
StringBuilder sb = new StringBuilder();
while (sdr.Read())
{
sp.AppendLine(sdr.GetString(0) +" at " + sdr.GetInt32(1));
}
TextBox1.Text = sb.ToString();
also change the TextBox MultiLine property to True and resize the TextBox1 to show more than one line
ouch ... missed the bogus sdr.Read() before loop....
