问题:
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(MyCon...
可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:
问题:
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.
回答1:
If skill2
field is of type CharVarChar2NChar
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
}
}
}
}
回答2:
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();
回答3:
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)}''";