c# - 使用来自checkedListBox的超过1个已检查项目创建查询

Question

我的代码对于选中列表框中的 1 个选中项目运行良好，用户当前只能选择 1 个项目，但我希望用户能够选择多个复选框并循环，所以每个“from“+ SwitchClause +”。都会创建一个新查询并插入日期。

底部有一个我需要实现的目标的示例。

由于每个节点都是一个新数据库，这是我能找到的最简单的方法。

感谢您的帮助。

public String GetQueriesForDay(DateTime Querydate)
{
    string SwitchName = "";
    foreach (DataRowView row in checkedListBox1.CheckedItems)
    {
        SwitchName = String.Format("{0}{1}{2}", SwitchName, (SwitchName == String.Empty) ? "" : "", row["name"]);
    }
    string SwitchClause = "";
    foreach (DataRowView row in checkedListBox1.CheckedItems)
    {
        SwitchClause = String.Format("{0}{1}node{2}", SwitchClause, (SwitchClause == String.Empty) ? "" : "", row["nodeid"]);
    }

    string Query;

    Query = String.Format("select '" + SwitchName + " - " + SwitchClause + "' as Switch_Name ,clngp_digits as Extn, cldp_talk_time, cost " +
                          "from "+ SwitchClause +".cr" + Querydate.ToString("yyyyMMdd") + " where record_type = 'D' or record_type = 'C' " +
                          "union all " +
                          "select '" + SwitchName + " - " + SwitchClause + "' as Switch_Name ,cldp_digits as Extn, cldp_talk_time, cost " +
                          "from " + SwitchClause + ".cr" + Querydate.ToString("yyyyMMdd") + " where record_type = 'D' or record_type = 'B'"
                          );

    return Query;
}
    private void button1_Click(object sender, EventArgs e)
    {

        CalculateDays();
        string SwitchClause = "";
        foreach (DataRowView row in checkedListBox1.CheckedItems)
        {
            SwitchClause = String.Format("{0}{1}node{2}", SwitchClause, (SwitchClause == String.Empty) ? "" : "", row["nodeid"]);
        }

        string SwitchName = "";
        foreach (DataRowView row in checkedListBox1.CheckedItems)
        {
            SwitchName = String.Format("{0}{1}{2}", SwitchName, (SwitchName == String.Empty) ? "" : "", row["name"]);
        }

        string queryStr = "";

        for (DateTime date = Startdate; date.Date <= Enddate.Date; date = date.AddDays(1))
        {
            queryStr = String.Format("{0}{1}{2}", queryStr, (date != Startdate) ? (date.AddDays(1) != Enddate) ? " UNION ALL " : " UNION ALL " : "", GetQueriesForDay(date));
        }


        string dataSource = IPtxt.Text;
        string tigerDatabase = "datasource =  " + IPtxt.Text + ";port=3306;DATABASE= " + SwitchClause + ";Uid=xxx;Pwd=xxx";


        MySqlConnection tigercon = new MySqlConnection(tigerDatabase);
        MySqlCommand callrec = new MySqlCommand("SELECT Switch_Name,Extn, COUNT(*) as Total_Calls, SEC_TO_TIME(SUM(cldp_talk_time/1000)) as Talk_Time, SUM(cost/100000) as 'Total_Cost' from (" + queryStr + ") t group by Extn order by Extn ASC ;", tigercon);

        try
        {
            MySqlDataAdapter ttrkvw = new MySqlDataAdapter();
            ttrkvw.SelectCommand = callrec;
            System.Data.DataTable dbdataset = new System.Data.DataTable();
            ttrkvw.Fill(dbdataset);
            BindingSource bSource = new BindingSource();

            bSource.DataSource = dbdataset;
            dataGridView1.DataSource = bSource;
            ttrkvw.Update(dbdataset);

            MySqlDataAdapter dap = new MySqlDataAdapter(callrec);
            System.Data.DataTable tblItems = new System.Data.DataTable();
            dap.Fill(tblItems);

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

所以下面的例子就是我想要实现的目标。这样，对于每个循环，它会为每天和每个选定的检查项目创建 2 个查询。节点。是选中项的值。另外，如果有帮助的话，NodeX 一词是数据库名称，并且表的格式为 CRYYYYMMDD。

//Currently the next 3 lines is what i get with the current code and node1 selected in my checkbox.     
    select clngp_digits as Extn, cldp_talk_time, cost from node1.cr20140130 where record_type = 'D' or record_type = 'C'
    union all
    select cldp_digits as Extn, cldp_talk_time, cost from node1.cr20140130 where record_type = 'D' or record_type = 'B'
// If I have 2 checkbox selected then I would like the same as above with the other checkbox value I would then like it scaleable so if 10 check boxes are selected it will loop 10 times.
    select clngp_digits as Extn, cldp_talk_time, cost from node2.cr20140130 where record_type = 'D' or record_type = 'C'
    union all
    select cldp_digits as Extn, cldp_talk_time, cost from node2.cr20140130 where record_type = 'D' or record_type = 'B'

Answer 1

你可以使用

where record_type in ('D', 'I', ...)

这只会让你的代码变得更小。我建议创建一个 View ，将您拥有的表分组为一个，这样查询会容易得多。

create view demo
as
select clngp_digits as Extn, cldp_talk_time, cost, record_type from node1.cr20140130
union all
select cldp_digits as Extn, cldp_talk_time, cost, record_type from node1.cr20140130
union all
select clngp_digits as Extn, cldp_talk_time, cost, record_type from node2.cr20140130
union all
select cldp_digits as Extn, cldp_talk_time, cost, record_type from node2.cr20140130

就这样查询吧

select *
from   demo
where  record_type in ('D', 'I', ...)