c# - 动态地将参数传递给mysql命令并关联列

Question

我有3个表1>disease_table [columns=] (disease_id,disease_name)2>symptom_table [columns=] (symptom_id,symptom_name)3>disease_symptom [columns=] (disease_id,symptom_id)

我希望它根据用户通过复选框输入的症状显示疾病名称，复选框可以是任意数量的症状。所以我知道disease_id将是disease_table中的主键，但我不明白如何在disease_symptom表中关联disease_id，symptom_id。我也有这样的想法

select disease_name,disease_id 
from disease_symptom ds
InnerJoin symptom_table s on ds.symptom_id=s.symptom_id
InnerJoin disease_table d on d.disease_id=s.disease_id
where s.symptom_name in('checkbox1.text','checkbox2.text',.... and so on)

所以基本上我想知道的是如何关联疾病症状表中的疾病 ID、症状 ID 以及如何传递动态调整的参数数量(用户可以选择 3 个复选框，有时是 4 ,5 个等)...谢谢

Answer 1

正确的SQL:

select
  d.name
  , d.disease_id 
from Disease d
inner join DiseaseSympton ds on ds.disease_id = d.disease_id
inner join Sympton s on s.sympton_id = ds.sympton_id
where s.name in ('Fever')

SQL Fiddle

动态获取复选框值(我无法测试 MySQL Conns):

using System;
using System.Text;
using System.Web.UI.WebControls;
using System.Linq;


public class Program
{
    public static void Main()
    {
        var chBoxList= new CheckBoxList();
        chBoxList.Items.Add(new ListItem("Headache", "1"));
        chBoxList.Items.Add(new ListItem("Fever", "2"));
        chBoxList.Items.Add(new ListItem("Nause", "3"));

        chBoxList.Items[1].Selected = true;
        chBoxList.Items[2].Selected = true;

        string symptons = String.Join(", ", chBoxList.Items.Cast<ListItem>()
                                      .Where(i => i.Selected)
                                      .Select(i => i.Text));

        Console.WriteLine(symptons);


        using (MySqlConnection connection = new MySqlConnection(...))
        {
            connection.Open();

            using (MySqlCommand cmd = new MySqlCommand("select d.name , d.disease_id from Disease d inner join DiseaseSympton ds on ds.disease_id = d.disease_id inner join Sympton s on s.sympton_id = ds.sympton_id where s.name in ('@pSymptons');", connection))
            {
                cmd.Parameters.AddWithValue("@pSymptons", symptons);

                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    StringBuilder sb = new StringBuilder();

                    while (reader.Read())
                    {
                        ...
                    }
                }
            }
        }
    }
}

Net Fiddle