c# - 如何将MySQL查询结果存储在一个字符串中

Question

是否可以在字符串变量中获取 MySQL 查询结果。考虑以下场景，让我知道是否可以将 MySQL 查询结果存储到字符串中。我的数据库如下

Article ID       Article Name    Article Quantity
1                Article1         15 Units
2                Article2         20 Units
3                Article3         18 Units

此外，我正在尝试使用 for 循环逐行访问(不知道我这样做是对还是错)。但我现在主要关心的是将 MySQL 查询结果转换为字符串。

private void send_serial_data(string port_name)
{
    MySqlConnection MyConn2 = new MySqlConnection(MyConnection2);
    SerialPort sp = new SerialPort(port_name, 9600, Parity.None, 8, StopBits.One);
    MyConn2.Open();
    sp.Open();
    string variable; //In which I want to store Query
    int j = 0; //To keep track of null rows
    {
        for (int i = 1; i < 20; i++) //I is defined to select new row each time
        {
            String Query = "";
            Query = "SELECT * FROM warehouse_data.display where Article_ID= i";
            MySqlCommand cmd = new MySqlCommand(Query, MyConn2);
            // variable = (string)command.ExecuteScalar();    Tried this to save query but doesn't work.
            int a = cmd.ExecuteNonQuery();
            if (a == 0)
            {
                j++;
                if (j >= 10)
                {
                    MessageBox.Show("Data Transmitted Successfully");
                    break;
                }
            }
            else
            {
                sp.Write(variable); //variable in which I want to store MySQL query
                System.Threading.Thread.Sleep(5000);
            }
        }
    }
}

基本上我试图将 MySQL 数据发送到串行端口(一次一行)。关于如何将查询保存到定义的字符串“变量”以及如何使用 for 循环来实现我的目标或任何其他方式来有效地逐行访问的任何建议？非常感谢您的帮助。提前致谢

Answer 1

您的代码中存在很多问题，首先，ExecuteNonQuery 不返回行。您需要使用 ExecuteReader，读取一行，将每个字段转换为字符串，发送然后重复。但在此之前，您需要修复该查询，因为 i 变量不是用于过滤查询的值。但是真的没有必要调用20次相同的查询通过ID过滤，只需要使用合适的WHERE条件

private void send_serial_data(string port_name)
{
    using (MySqlConnection MyConn2 = new MySqlConnection(MyConnection2))
    using (SerialPort sp = new SerialPort(port_name, 9600, Parity.None, 8, StopBits.One))
    {
        MyConn2.Open();
        sp.Open();

        // A query that returns all records with an ID lower than 20
        String Query = @"SELECT * FROM warehouse_data.display where Article_ID < 20";
        using (MySqlCommand cmd = new MySqlCommand(Query, MyConn2))
        using (MySqlDataReader reader = cmd.ExecuteReader())
        {
            // Read one record 
            while (reader.Read())
            {

                StringBuilder sb = new StringBuilder();
                // Accumulate the fields values in the stringbuilder
                // separating each one with a comma
                for (int x = 0; x < reader.FieldCount; x++)
                {
                    if (reader.IsDBNull(x))
                        sb.Append(",")
                    else
                        sb.Append(reader[x].ToString() + ",");
                }
                // trim away the last comma
                sb.Length--;

                // And probably you need something to separate a record
                // from the following one. For example a newline
                sb.Append(Environment.NewLine);

                // send it along the wire
                sp.Write(sb.ToString());
            }
        }
    }
}

还要注意另外两件事，我用逗号分隔了每个字段，否则你写入串行端口的接收者将无法重建从表中读取的字段，而且每个一次性对象都应该包含在适当的使用语句