MySql的传递不同的值到C＃字符串(Pass MySql distinct values into

我有这个疑问来从一个表列 ，我想传递给不同值 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()) + "''";

我怎样才能得到这个工作？ 提前致谢...

编辑应用德米特里的回答后

该阵列我从这个搞定了，我有如下图所示把它作为参数传递给另一个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 不支持它（见下面的评论），你可以尝试ealier版本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
      }
    }
  }
}

Answer 2:

使用过的结果一个StringBuilder和循环。 调用read用来只是定位在当前记录DataReader的。 如果你有一个以上的记录，你需要再次调用Read，直到它返回false（没有更多的记录）

StringBuilder sb = new StringBuilder();
using(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();

     // according to your comment, the result should be used as 
     // arguments for an IN clause.
     while(reader.Read())
       sb.AppendLine("'" + reader["skills"].ToString() + "',");
}
if(sb.Length > 0)
    sb.Length --; // To remove the last '
return sb.ToString();

Answer 3:

如果你有多个技能，你的查询将返回多行。 这意味着，你不能得到所有单一技能reader.Read() 你需要调用reader.Read()直到你读所有的技能，然后加入技能转化的字符串：

List<string> skillsList = new List<string>();
using (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();
   while (reader.Read())
   {
      skillsList.Add(reader["skill"].ToString());
   }
}
string skills = $"''{string.Join("'',''", skillsList)}''";

文章来源: Pass MySql distinct values into a c# string