Question

C#连接mysql数据库如何实现多条件查询

如题，在一个文本框里输入比如姓名或者学号的查询条件，通过一个按钮来进行查询，代码如何实现

Answer 1

给你一个稍微复杂一点的查询，我设计的

看上图我把第一列标签后面的控件命名有规律，比如计划工厂后面文本框是"计划工厂t"，Exapt命名为"计划工厂c"，后面还隐藏一个listbox，命名为"计划工厂l"。

计划工厂  这个名称本身就是我要查询的表里面包含的字段。

利用这种界面，我要完成一些复杂点的查询：点文本框里的小图标按钮，弹出工具，可以输入多条件，条件可以成立为模糊条件（用*代替不认识部分），也可以成立为否决条件。

选中Exapt全部否定所成立条件；点击执行按钮，有条件的成立条件，无条件的不成立。

所以这种东西贯穿软件我就不能每个都去做，就只能写一个类文件：

    public class conditionset
    {
        public void load_condit(Panel p)
        {
            foreach (Control ctl in p.Controls)
            {
                if (ctl is SkinTextBox)
                {
                    SkinTextBox sktxt = (SkinTextBox)p.Controls[ctl.Name];
                    ListBox list = (ListBox)p.Controls[sktxt.Name.Substring(0, sktxt.Name.Length - 1) + "l"];
                    sktxt.IconClick += (Object sen, EventArgs ed) =>
                    {
                        ctl.condition toolform = new ctl.condition();
                        if (list.Items.Count > 0)
                        {
                            sktxt.Text = "┅";
                            sktxt.ReadOnly = true;
                        }
                        foreach (var sm in list.Items)
                        {
                            toolform.dv.Rows.Add(sm.ToString());
                        }
                        toolform.Show();
                        toolform.cleari += (Object send, EventArgs er) =>
                         {
                             sktxt.Text = "";
                             sktxt.ReadOnly = false;
                         };
                        toolform.subm += (Object send, EventArgs er) =>
                        {
                            list.Items.Clear();
                            foreach (DataGridViewRow dvr in toolform.dv.Rows)
                            {
                                if (Convert.ToString(dvr.Cells[0].Value) != "")
                                {
                                    list.Items.Add(Convert.ToString(dvr.Cells[0].Value));
                                }
                            }
                            sktxt.Text = "┅";
                            sktxt.ReadOnly = true;
                        };
                    };
                }
            }
        }
        public string condit(Panel p)
        {
            string master_condition = "";
            foreach (Control ctl in p.Controls)
            {
                //遍历panel查找条件
                #region 
                string condition = "";
                if (ctl is SkinTextBox)
                {
                    //文本框条件组合
                    #region
                    SkinTextBox sktxt = (SkinTextBox)p.Controls[ctl.Name];
                    string ziduan_name = sktxt.Name.Substring(0, sktxt.Name.Length - 1);
                    SkinCheckBox skck = (SkinCheckBox)p.Controls[ctl.Name.Substring(0, ctl.Name.Length - 1) + "c"];
                    ListBox list = (ListBox)p.Controls[ctl.Name.Substring(0, ctl.Name.Length - 1) + "l"];
                    if (sktxt.Text != "")
                    {
                        if (sktxt.Text == "┅")
                        {
                            string blur_str = "", blur = "";
                            foreach (var itm in list.Items)
                            {
                                if (itm.ToString().Contains("*"))
                                {
                                    if (skck.Checked == true)
                                    {
                                        blur += " and " + ziduan_name + " not like '" + itm.ToString().Replace("*", "%") + "'";
                                    }
                                    else
                                    {
                                        blur += " or " + ziduan_name + " like '" + itm.ToString().Replace("*", "%") + "'";
                                    }
                                }
                                else
                                {
                                    blur_str += "'" + Convert.ToString(itm) + "',";
                                }
                            }
                            string blur_sql = (blur == "") ? "" : blur.Substring(4, blur.Length - 4).Trim();
                            string in_condition = "", like_condition = "";
                            if (skck.Checked == true)
                            {
                                in_condition = ziduan_name + " not in ";
                                like_condition = " and ";
                            }
                            else
                            {
                                in_condition = ziduan_name + " in ";
                                like_condition = " or ";
                            }
                            string contains_sql = (blur_str == "") ? "" : in_condition + "(" + blur_str.Substring(0, blur_str.Length - 1) + ")";
                            condition = contains_sql + blur_sql;
                            if (blur_sql != "" && contains_sql != "")
                            {
                                condition = contains_sql + like_condition + blur_sql;
                            }
                            else
                            {
                                condition = contains_sql + blur_sql;
                            }
                        }
                        else
                        {
                            if (!sktxt.Text.Contains("*"))
                            {
                                if (skck.Checked == true)
                                {
                                    condition = ziduan_name + "<>'" + sktxt.Text + "'";
                                }
                                else
                                {
                                    condition = ziduan_name + "='" + sktxt.Text + "'";
                                }
                            }
                            else
                            {
                                if (skck.Checked == true)
                                {
                                    condition = ziduan_name + " not like '" + sktxt.Text.Replace("*", "%") + "'";
                                }
                                else
                                {
                                    condition = ziduan_name + " like '" + sktxt.Text.Replace("*", "%") + "'";
                                }
                            }
                        }
                    }
                    #endregion
                }
                if (ctl is SkinDateTimePicker)
                {
                    //日期条件组合
                    #region
                    if (ctl.Name.Substring(ctl.Name.Length - 1, 1) != "t")
                    {
                        SkinDateTimePicker skdate_sta = (SkinDateTimePicker)p.Controls[ctl.Name];
                        SkinDateTimePicker skdate_end = (SkinDateTimePicker)p.Controls[ctl.Name + "t"];
                        if (skdate_sta.text != "")
                        {
                            if (skdate_end.text == "")
                            {
                                condition = skdate_sta.Name + "='" + skdate_sta.text + "'";
                            }
                            else
                            {
                                condition = skdate_sta.Name + ">='" + skdate_sta.text + "' and " + skdate_sta.Name + "<='" + skdate_end.text + "'";
                            }
                        }
                    }
                    #endregion
                }
                master_condition += (condition == "") ? "" : "(" + condition + ") and ";
                #endregion //遍历panel查找条件
            }
            string condition_sql = (master_condition == "") ? "" : master_condition.Substring(0, master_condition.Length - 5);
            return condition_sql;
        }
    }

工具用一个窗体代替：

    public partial class condition : Form
    {
        public condition()
        {
            InitializeComponent();
        }
        public event EventHandler subm;
        public event EventHandler cleari;
        private void submit_Click(object sender, EventArgs e)
        {
            subm(sender, e);
            this.Dispose();
        }
        private void clearit_Click(object sender, EventArgs e)
        {
            this.Dispose();
            dv.Rows.Clear();
            cleari(sender, e);
        }
    }

当我们执行多条件的时候就等于拼接条件

Answer 2

根据一个输入框应该还不够，你应该至少再提供一个按钮，或者是下拉框进行选择是看什么条件来进行查询。不然程序怎么知道你输入的值是拿来做什么条件的。
假设你又加了一个下拉框，然后在程序后台，点击按钮之后，你首先要对下拉框的当前内容进行判定
whereSql = "";
if(按照姓名查询)
whereSql = string.Format(" where 学生姓名='{0}'", textBox.Text);
else if(按照学号查询)
whereSql = string.Format(" where 学生学号='{0}'", textBox.Text);
string sql = "select * from 学生信息" + whereSql;
这个是查询语句的写法了，后面就是改下这个部分new MySqlCommand(sql, mycon);
其实你查询这段的写法可以封装成一个通用数据访问类，这样便于以后直接使用。

Answer 3

or 关键字