C#连接SQL多条件复杂查询

 

控件说明:

供应商 CheckedBox   ckbID                        供应商 MasktextBox       mtxtID             

价格 CheckedBox       ckPirce                     价格      MasktextBox        mtxtPrice         价格     ComboBox     cmbPrice

产地 CheckedBox       ckbProd                    产地      ComboBox         cmbProd

品名 CheckedBox       ckbVariety                 品名      ComboBox         cmbVariety

DataGridView               dataGridView1          toolStripStatusLabel      toolStripStatusLabel1

查找按钮 Botton           btnSearch                退出按钮 Botton     btnExit

 源代码

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace 利用选择控件来实现复杂查询
{
    public partial class Form1 : Form
    {
        public string ID;      //存储供应商ID
        public string Prod;    //存储产地
        public string Price;   //存储价格比较结果
        public string Variety; //存储商品名称
        public string strSQL;  //储存完整SQL语句
 
        public Form1()
        {
            InitializeComponent();
        }

        #region 自定义初始连接数据库方法
        //该方法用来对2个ComboBox控件赋值;传递2个参数,1个为对象(那一个控件),1个为字符串(按数据表的那一列查询)
                private void SQLconn(object comboxOBJ,string ROW)
                {                   
                    SqlConnection conn = new SqlConnection("server = (local); uid = sa; database = 销售管理系统");
                    conn.Open();
                    string strSql = "select DISTINCT " + ROW + " FROM 商品信息";
                    SqlCommand cmd = new SqlCommand(strSql, conn);
                    SqlDataReader dr = cmd.ExecuteReader();
                    //此处判断该方法传入的对象参数是否为ComboBox控件
                    if (comboxOBJ.GetType().ToString() == "System.Windows.Forms.ComboBox")
                    {
                        //如果是,则实例化临时对象,用来把数据循环赋值给参数所指定的控件
                        ComboBox cbo = (ComboBox)comboxOBJ;
                        while (dr.Read())
                        {
                            cbo.Items.Add(dr[0].ToString());
                        }
                    }
                    dr.Close();
                    conn.Close();
                }
        #endregion

        #region 自定义SQL语句查询方法
        //该方法传入1个字符串类型的参数,该参数主要为SQL语句
        private void SQLSearch(string strSQL)
        {
            SqlConnection conn = new SqlConnection ("server = (local); uid = sa; database = 销售管理系统");
            conn.Open();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);           
            da.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0].DefaultView;           
            conn.Close();
        }
        #endregion

        #region 初始化界面控件
        private void Form1_Load(object sender, EventArgs e)
                {
                   
                    cmbPirce.Enabled = false;
                    cmbProd.Enabled = false;
                    cmbVariety.Enabled = false;
                    mtxtID.Enabled = false;
                    mtxtPirce.Enabled = false;
                }
        #endregion

 

        #region 当选择时,控件可用,并且对相关控件赋值;
                //供应商 CheckedBox_CheckedChanged事件
                private void ckbID_CheckedChanged(object sender, EventArgs e)
                {
                    if (ckbID.Checked == true)
                    {
                        mtxtID.Enabled = true;
                        //只允许输入4位数字
                        mtxtID.Mask = "0000";
                        mtxtID.Focus();
                    }
                    else
                    {
                        mtxtID.Enabled = false;
                        mtxtID.Text = "";
                    }
                                     
                }
                //价格 CheckedBox_CheckedChanged事件
                private void ckPirce_CheckedChanged(object sender, EventArgs e)
                {
                    if (ckPirce.Checked == true)
                    {
                        mtxtPirce.Enabled = true;
                        mtxtPirce.Mask = "00000";
                        mtxtPirce.Focus();
                        cmbPirce.Enabled = true;
                    }
                    else
                    {
                        mtxtPirce.Enabled = false;
                        mtxtPirce.Text = "";
                        cmbPirce.Enabled = false;
                        cmbPirce.Items.Clear();
                    }
                }
                //产地 CheckedBox_CheckedChanged事件
                private void ckbProd_CheckedChanged(object sender, EventArgs e)
                {
                    if (ckbProd.Checked == true)
                    {
                        cmbProd.Enabled = true;
                        //利用上面自定义的方法,为产地的ComboBox控件赋值
                        SQLconn(cmbProd, " 产地 ");
                        cmbProd.Focus();
                        cmbProd.AutoCompleteMode = AutoCompleteMode.Suggest;
                        cmbProd.AutoCompleteSource = AutoCompleteSource.ListItems;
                    }
                    else
                    {
                        cmbProd.Enabled = false;                       
                        cmbProd.Items.Clear();                       
                    }
                }
                //品名 CheckedBox_CheckedChanged事件
                private void ckbVariety_CheckedChanged(object sender, EventArgs e)
                {
                    if (ckbVariety.Checked == true)
                    {
                        cmbVariety.Enabled = true;
                        //利用上面自定义的方法,为商品名称的ComboBox控件赋值
                        SQLconn(cmbVariety, " 商品名称 ");
                        cmbVariety.Focus();
                        cmbVariety.AutoCompleteMode = AutoCompleteMode.Suggest;
                        cmbVariety.AutoCompleteSource = AutoCompleteSource.ListItems;
                       
                    }
                    else
                    {
                        cmbVariety.Enabled = false;
                        cmbVariety.Items.Clear();
                    }
                }
        #endregion
       
        //toolTip控件相关设置
        private void mtxtID_MaskInputRejected(object sender, MaskInputRejectedEventArgs e)
        {
            toolTip1.ToolTipTitle = "供应商查询";
            toolTip1.Show("只能输入4位数字编号！", mtxtID, mtxtID.Location, 5000);
            mtxtID.SelectAll();
            mtxtID.SelectionStart = 0;
            mtxtID.Focus();
        }

        private void mtxtPirce_MaskInputRejected(object sender, MaskInputRejectedEventArgs e)
        {
            toolTip1.ToolTipTitle = "价格查询";
            toolTip1.Show("请确认输入的为5位整数!", mtxtPirce, mtxtPirce.Location, 5000);
            mtxtPirce.SelectAll();
            mtxtPirce.SelectionStart = 0;
            mtxtPirce.Focus();
        }

        #region 查找按钮单击事件
        private void btnSearch_Click(object sender, EventArgs e)
        {
            if (mtxtID.Text != "")
            {
                ID = "供应商编号 LIKE '%" + mtxtID.Text + "%'";
            }
            else
            {
                //SQL中WHERE子查询的永真(true)语句
                ID = "1=1";
            }
            if (mtxtPirce.Text != "")
            {
                //根据价格下拉框的索引值,建立查询语句
                if (cmbPirce.SelectedIndex != -1)
                {
                    switch (cmbPirce.SelectedIndex)
                    {
                        case 0:
                            Price = "单价 > " + mtxtPirce.Text;
                            break;
                        case 1:
                            Price = "单价 < " + mtxtPirce.Text;
                            break;
                        case 2:
                            Price = "单价 = " + mtxtPirce.Text;
                            break;
                        case 3:
                            Price = "单价 >= " + mtxtPirce.Text;
                            break;
                        case 4:
                            Price = "单价 <= " + mtxtPirce.Text;
                            break;
                        case 5:
                            Price = "单价 <> " + mtxtPirce.Text;
                            break;
                    }
                }
            }
            else
            {
                Price = "1=1";
            }

            //注意:此处要做个判断,要先确认控件激活状态才能进行进一步的判断其值是否为空
            if (cmbProd.Enabled == true)
            {
                if (cmbProd.SelectedItem.ToString() != "")
                {
                    Prod = "产地 LIKE '%" + cmbProd.SelectedItem.ToString() + "%'";
                }
            }
            else
            {
                Prod = "1=1";
            }

            if (cmbVariety.Enabled == true)
            {
                if (cmbVariety.SelectedItem.ToString() != "")
                {
                    Variety = "商品名称 LIKE '%" + cmbVariety.SelectedItem.ToString() + "%'";
                }
            }
            else
            {
                Variety = "1=1";
            }

            strSQL = "SELECT * FROM 商品信息 WHERE " + ID + " and " + Prod + " and " + Price + " and " + Variety;
            SQLSearch(strSQL);
            toolStripStatusLabel1.Text = "查询到 " + Convert.ToString(dataGridView1.Rows.Count-1) + "条数据";
            strSQL = "";
        }
        #endregion

        private void btnExit_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }       

}