Pass MySql distinct values into a c# string

Question

I have this query to fetch the distinct values from a table column which I want to pass to a string.

Below is the code:

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

Edit After applying Dmitry's answer

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.

If the query is that simple you can use `GROUP_CONCAT` to get a delimited string directly [as shown here](https://stackoverflow.com/questions/3083499/mysql-distinct-on-a-group-concat) — Panagiotis Kanavos, Mar 19 '19 at 12:12
Please, have a look at your query text - `"SELECT DISTINCT(skill2) AS skills FROM agentdetails"` it *doesn't have* any parameter(s) - (let alone `@grpm`). But you *provide* it - `command.Parameters.AddWithValue("@grpm", username);`. So RDBMS *complains* (what shall I do with the parameter's value?) — Dmitry Bychenko, Mar 20 '19 at 08:03
@DmitryBychenko Please leave that part, I forgot to remove it when I edited the question, I use it in my query to filter from a vast amount of data, which is not needed here. — prkash, Mar 20 '19 at 08:06

Dmitry Bychenko · Accepted Answer · 2019-03-20T08:30:47.630

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

Edit: $"..." 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)));

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

When I try your method I get an error, CS1056: Unexpected character '$' — prkash, Mar 20 '19 at 07:32
I've edited the question after applying your answer and I removed the '$' sign and I get the above error — prkash, Mar 20 '19 at 08:00
`@prkash:` the line `command.Parameters.AddWithValue("@grpm", username);` looks erroneous - your query *doesn't have* any parameters — Dmitry Bychenko, Mar 20 '19 at 08:05
In my actual code I have values for that but I avoided that part from the query here, I've just edited the question again. That parameter is not necessary. I've removed it — prkash, Mar 20 '19 at 08:08

Steve · Answer 2 · 2019-03-19T13:00:36.617

1

Use a StringBuilder and loop over the results. Calling Read serves just to position the DataReader on a current record. If you have more than one record you need to call Read again until it return false (no more records)

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

edited Mar 19 '19 at 13:00

answered Mar 19 '19 at 12:06

Steve

203,265
19
210
265

2

I doubt `string.Join("'',''", reader["skills"].ToString())` will work. Even if `reader["skills"]` returned an array somehow, the call to `ToString()` would convert it into a single string – Panagiotis Kanavos Mar 19 '19 at 12:10
I have to pass the value I get from sb to another query and when I do that I get an error: (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '_binary '\'\'IN Berrio\'\' \'\'IN Nemo\'\' \'\'IN Primary Phone-EH\'\' \'\'IN' at line 1) – prkash Mar 19 '19 at 12:17
Then we need to add the proper separator – Steve Mar 19 '19 at 13:01
When I use your method, it returns "'IN Berrio'\r\n'IN Nemo'\r\n'IN Primary Phone-EH'\r\n'IN Prime Phone'\r\n'RS IN'\r\n". But What I need is, 'IN Berrio','IN Nemo','IN Primary Phone-EH' and so on. – prkash Mar 20 '19 at 08:46
Well, if you this text in a IN sql statement the correct text is IN('value1', 'value2', 'value3'). Do you use this text to create an IN sql statement? – Steve Mar 20 '19 at 08:52

score 0 · Answer 3 · answered Mar 19 '19 at 12:20

If you have multiple skills, your query will return multiple rows. This means, you cannot get all the skills with a single reader.Read(). You need to call reader.Read() until you read all the skills and then join the skills into string:

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

3 Answers3

Linked