将MySql不同值传递到C＃字符串中 [英] Pass MySql distinct values into a c# string

问题描述

我有这个查询要从表列中获取不同的值，我想将其传递给字符串。

I have this query to fetch the distinct values from a table column which I want to pass to a 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()) + "''";

如何完成此任务？预先感谢...

How can I get this done? Thanks in advance...

应用Dmitry的答案后编辑

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

The array I get from this, I have to pass it as parameter to another MySql query as shown below.

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

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

When I do the this I get error as shown in the below Pic.

推荐答案

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

If skill2 field is of type Char\VarChar2\NChar etc. we have to enumerate records e.g.

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 代替：

$"..." is a string interpolation, if you use ealier version of c# / .Net which doesn't support it (see comment below) you can try string.Format instead:

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

编辑2：不要硬编码查询，但参数设置； las，您不能将集合作为单个参数传递到 IN 中：

Edit 2: Do not hardcode queries, but parametrize them; alas, you can't pass a collection into IN as a single parameter:

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

这篇关于将MySql不同值传递到C＃字符串中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！