How to get multiple rows from Datareader

Question

This is the code I have. It works fine but returns only one row. As you can see in the SQL Statement I need 2 rows returned in the datagrid that I use in the form. While the procedure reads 2 rows it only displays one row. NameAddrmark is a constructor for the fields.

    public NameAddrmark GetNameRespCommentData(string respid)
    {
        NameAddrmark cms = new NameAddrmark();
        //var cms = new List<NameAddrmark>();

        SqlConnection connection = new SqlConnection(GeneralData.getConnectionString());

        string sql = "SELECT top 2 * FROM dbo.RESPONDENT_COMMENT WHERE respid = " + GeneralData.AddSqlQuotes(respid) + " and USRNME = " + GeneralData.AddSqlQuotes(UserInfo.UserName) + " order by COMMDATE ASC";
        SqlCommand command = new SqlCommand(sql, connection);

        try
        {
            connection.Open();
            SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult);

            while(reader.Read())
            //if (reader.Read())
            {
                cms.Id = respid;
                cms.Date8 = reader["COMMDATE"].ToString();
                cms.Usrnme = reader["USRNME"].ToString();
                cms.Marktext = reader["COMMTEXT"].ToString();
            }
            //else
            //    cms = null;
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        finally
        {
            connection.Close();
        }
        return cms;
    }

On my form I return cms to a list. Set the datasource of the datagrid to the list. Displays first row correctly but I need two rows displayed. Hope someone can help. Thanks.

You are just setting the cms fields every time, why would it append information? — Egor, Nov 12 '15 at 17:09
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection — marc_s, Nov 12 '15 at 18:20

Tim Schmelter · Accepted Answer · 2021-11-27T10:41:01.497

You can return a List<NameAddrmark>:

public List<NameAddrmark> GetNameRespCommentData(string respid)
{
    List<NameAddrmark> cmsList = new List<NameAddrmark>();
    SqlConnection connection = new SqlConnection("insert connection string");
    string sql = "SELECT top 2 * FROM dbo.RESPONDENT_COMMENT WHERE respid = " + GeneralData.AddSqlQuotes(respid) + " and USRNME = " + GeneralData.AddSqlQuotes(UserInfo.UserName) + " order by COMMDATE ASC";
    SqlCommand command = new SqlCommand(sql, connection);
    try
    {
        connection.Open();
        SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult);
        while (reader.Read())
        {
            NameAddrmark cms = new NameAddrmark();
            cms.Id = respid;
            cms.Date8 = reader["COMMDATE"].ToString();
            cms.Usrnme = reader["USRNME"].ToString();
            cms.Marktext = reader["COMMTEXT"].ToString();
            cmsList.Add(cms);
        }
    } 
    catch (SqlException ex)
    {
        throw; // instead log the exception
    } 
    finally
    {
        connection.Close();
    }
    return cmsList;
}

But you should really use parameters instead of string concatenation to prevent sql injection.

Sorry about the delayed response. Tim's solution worked perfectly. Thanks. You are 'fast and fantastic' Tim. — TonyM, Nov 17 '15 at 19:57

score -1 · Answer 2 · answered Aug 18 '17 at 07:04

private void button2_Click(object sender, EventArgs e)
        {
            //database connection string and opening area
            string oracleDb = @"Data Source="connection string here"";
            OracleConnection conn = new OracleConnection(oracleDb);
            conn.Open();

            //declareing paramater and readning parameter input
            OracleParameter param = new OracleParameter();
            param.OracleDbType = OracleDbType.Decimal;
            param.Value = txtlist.Text;

            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;

            //sendting the parameter to the tabase query 
            cmd.Parameters.Add(param);
            cmd.CommandText = "SELECT NAME, ADDRESS  FROM FRIENDS WHERE age = :1";

            cmd.CommandType = CommandType.Text;
            OracleDataReader dataread = cmd.ExecuteReader();
            dataread.Read();

            if (dataread.HasRows)
            {
                while (dataread.Read())
                {
                    listBox1.Items.Add(dataread.GetString(1) + " from " + dataread.GetString(2));
                }
            }
            else
            {
                listBox1.Text = "Not Found";
                MessageBox.Show("Data Not found", "NOT FOUND", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            conn.Dispose();
        }

This answer doesn't seem to have much to do with the question. Maybe you posted it in the wrong browser tab? — waka, Aug 18 '17 at 07:35

How to get multiple rows from Datareader

2 Answers2

Linked