c# - 将 MySql 不同的值传递到 c# 字符串中

Question

我有此查询以从 表列 中获取不同的值，我想将其传递给 string。

代码如下:

MySqlConnection cons = new MySqlConnection(MyConString);

string query = "SELECT DISTINCT(skill2) AS skills FROM agentdetails";

var command = new MySqlCommand(query, cons);

cons.Open();

var reader = command.ExecuteReader();
reader.Read();

string skills = "''" + string.Join("'',''", reader["skills"].ToString()) + "''";

我怎样才能做到这一点？提前致谢...

应用 Dmitry 的答案后编辑

我从中得到的数组，我必须将它作为参数传递给另一个 MySql 查询，如下所示。

MySqlConnection cons = new MySqlConnection(MyConString);
string query = "SELECT DISTINCT(skill2) AS skills FROM agentdetails";
MySqlCommand command = new MySqlCommand(query, cons);
cons.Open();
MySqlDataReader reader = command.ExecuteReader();
HashSet<string> hs = new HashSet<string>();
while (reader.Read())
hs.Add(Convert.ToString(reader[0])); // and add them into a collection
string skills = string.Join(",", hs.Select(item => "'{item}'"));
skills.ToString();
cons.Close();
MySqlConnection con2 = new MySqlConnection(MyConString);
string hcount = "SELECT SUM(headCount) AS THC, date AS date1 from setshrinkage WHERE skill IN @skill AND date BETWEEN " + today1 + " AND " + today2 + " GROUP BY date";
MySqlCommand cmd2 = new MySqlCommand(hcount, con2);
cmd2.Parameters.AddWithValue("@skill", skills);
con2.Open();
MySqlDataReader myread2 = cmd2.ExecuteReader();

当我这样做时，出现如下图所示的错误。

Answer 1

如果 skill2 字段是 Char\VarChar2\NChar 等类型，我们必须枚举记录，例如

using (MySqlConnection cons = new MySqlConnection(MyConString)) {
  cons.Open();

  string query = 
    @"SELECT DISTINCT(skill2) AS skills 
        FROM agentdetails";

  using (new MySqlCommand(query, cons)) {
    using (var reader = command.ExecuteReader()) {
      // collection to store skills
      HashSet<string> hs = new HashSet<string>();

      // Enumerate all records
      while (reader.Read()) 
        hs.Add(Convert.ToString(reader[0])); // and add them into a collection

      // join collection into string
      string skills = string.Join(",", hs.Select(item => $"'{item}'"));

      //TODO: put relevant code here (e.g. return skills)
    } 
  }
}

编辑: $"..." 是一个字符串插值，如果您使用较早版本的 c#/.Net，不支持它(见下面的评论)你可以试试string.Format:

string skills = string.Join(",", hs.Select(item => string.Format("'{0}'", item)));

编辑 2:不要硬编码查询，而是参数化它们；唉，您不能将集合作为单个参数传递到 IN:

using (MySqlConnection con2 = new MySqlConnection(MyConString)) {
  con2.Open();

  string hcount = 
    string.Format( @"SELECT SUM(headCount) AS THC, 
                            date AS date1 
                       FROM setshrinkage 
                      WHERE skill IN ({0}) 
                        AND date BETWEEN @today1 AND @today2 
                   GROUP BY date", skill);

  using(MySqlCommand cmd2 = new MySqlCommand(hcount, con2)) {
    //TODO: provide actual RDBMS type - Add(...) instead of AddWithValue(...)
    cmd2.Parameters.AddWithValue("@today1", today1);    
    cmd2.Parameters.AddWithValue("@today2", today2);    

    using (MySqlDataReader myread2 = cmd2.ExecuteReader()) {
      while (myread2.Read()) {
        //TODO: provide relevant code here
      }
    }
  }
}