C# winForm 多条件模糊查询实现（主要讲思路，请忽略与数据库（sqlite）相关的代码）

C# winForm 多条件模糊查询实现（主要讲思路，请忽略与数据库（sqlite）相关的代码）

1.窗体设计

 

2.思路

　　通过遍历数组生成查询条件和查询

 

3.代码

        private void BtnSerch_Click(object sender, EventArgs e)
        {//开始查询按钮

            SerOrder();//查询订单

        }

        private void BtnSerClean_Click(object sender, EventArgs e)
        {//清空查询条件按钮

            DgvSer.Rows.Clear();

            TxbSerOrderID.Text = "";
            TxbSerMemberID.Text = "";
            TxbSerCompany.Text = "";
            TxbSerCustomer.Text = "";
            TxbSerPhone.Text = "";
            TxbSerAdd.Text = "";
            CmbSerOpener.Text = "";
            CmbSerInstaller.Text = "";
            TxbSerOrderSum.Text = "";
            CmbSerPrinted.Text = "";

            //清空日历
            DateTimePicker1.Format = DateTimePickerFormat.Custom;
            DateTimePicker1.CustomFormat = " ";

            TxbSerOrderID.Focus();

        }

 

private void SerOrder()
        {//查询订单

            DgvSer.Rows.Clear();//初始化datagridview

            //查询语句参数值
            string[] paras = { TxbSerOrderID.Text.Trim(), DateTimePicker1.Text.Trim(), TxbSerMemberID.Text.Trim(), TxbSerCompany.Text.Trim(), TxbSerCustomer.Text.Trim(), TxbSerPhone.Text.Trim(), TxbSerAdd.Text.Trim(), CmbSerOpener.Text.Trim(), CmbSerInstaller.Text.Trim(), TxbSerOrderSum.Text.Trim(), CmbSerPrinted.Text.Trim() };
            //数据表列名
            string[] columns = { "OrderID", "BillingDate", "MemberID", "CompanyName", "CustomerName", "CellphoneNumber", "CustomerAddress", "Opener", "Installer", "OrderSum", "printed" };
            string sqlStr = "select * from orders ";
            string[] condition = { };//查询语句条件

            List<string> condition2 = condition.ToList();//数组转列表

            using (SQLiteConnection conn = new SQLiteConnection(dbPath))
            {//连接数据库

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                SQLiteCommand cmd = conn.CreateCommand();

                for (int i = 0; i < paras.Length; i++)
                {//生成查询语句&查询条件

                    if (paras[i] == "")
                    {
                        continue;
                    }

                    condition2.Add(columns[i] + " like @" + columns[i]);
                    cmd.Parameters.Add(new SQLiteParameter("@" + columns[i], "%" + paras[i] + "%"));
                }

                condition = condition2.ToArray();

                if (condition.Length > 0)
                {//有查询条件
                    string result = String.Join(" and ", condition);
                    sqlStr += ("where " + result + ";");
                }

                cmd.CommandText = sqlStr;
                SQLiteDataReader reader = cmd.ExecuteReader();
                int cols = DgvSer.ColumnCount;

                if (reader.HasRows)
                {//显示查询结果

                    while (reader.Read())
                    {//遍历行

                        int index = DgvSer.Rows.Add();

                        for (int i = 0; i < cols - 3; i++)
                        {//遍历列

                            if (i == 0)
                            {//行号
                                DgvSer.Rows[index].Cells[i].Value = (index + 1).ToString();
                            }
                            else
                            {
                                DgvSer.Rows[index].Cells[i].Value = reader.GetValue(i).ToString();
                            }

                        }
                    }
                }

                //关闭数据库连接
                reader.Close();
                conn.Close();
            }
        }