如何从Datareader中获取多行数据

Question

这是我现有的代码。它可以正常工作，但只返回一行数据。从 SQL 语句中可以看到，在我使用表单中的 datagrid 时，需要返回 2 行数据。虽然该程序读取了 2 行数据，但只显示了一行。NameAddrmark 是字段的构造函数。

    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;
    }

在我的表单中，我将cms返回到一个列表中。将数据网格的数据源设置为该列表。它可以正确显示第一行，但我需要显示两行。希望有人可以帮忙。谢谢。

- TonyM

---

为什么它会追加信息？你每次只是设置CMS字段。 - Egor

SQL注入警报 - 您不应将SQL语句串联在一起 - 而应使用参数化查询来避免SQL注入。 - marc_s

谢谢，我已将其更改为参数化查询。 - TonyM

Answer 1

你可以返回一个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;
}

但是，为了防止 SQL 注入，您应该使用参数而不是字符串串联。

- Tim Schmelter

---

抱歉回复晚了。Tim的解决方案完美地解决了问题。谢谢你，Tim，你真是“快速而出色”的人。 - TonyM

Answer 2

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();
        }

- Delushaan

---

这个回答似乎与问题没有太大关系。也许你把它发布在了错误的浏览器标签页中？ - waka