多条件搜索的使用winform程序

有sqlhelper文件如下：

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace 电话本
{
    
        public static class SqlHelper
        {
            private static readonly string constr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;

            public static int ExecuteNonQuery(string sql, params SqlParameter[] pms)
            {
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand(sql, con))
                    {
                        if (pms != null)
                        {
                            cmd.Parameters.AddRange(pms);
                        }
                        con.Open();
                        return cmd.ExecuteNonQuery();
                    }
                }
            }
            public static object ExecuteScalar(string sql, params SqlParameter[] pms)
            {
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand(sql, con))
                    {
                        if (pms != null)
                        {
                            cmd.Parameters.AddRange(pms);
                        }
                        con.Open();
                        return cmd.ExecuteScalar();
                    }
                }

            }

            public static SqlDataReader ExecuteDataReader(string sql, params SqlParameter[] pms)
            {
                SqlConnection con = new SqlConnection(constr);
                try
                {
                    using (SqlCommand cmd = new SqlCommand(sql, con))
                    {
                        if (pms != null)
                        {
                            cmd.Parameters.AddRange(pms);
                        }
                        con.Open();
                        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    }
                }
                catch
                {
                    if (con != null)
                    {
                        con.Dispose();
                    }
                    throw;
                }


            }


            public static DataTable ExecuteDataTable(string sql, params SqlParameter[] pms)
            {
                DataTable dt = new DataTable();
                using (SqlDataAdapter sda = new SqlDataAdapter(sql, constr))
                {
                    if (pms != null)
                    {
                        sda.SelectCommand.Parameters.AddRange(pms);
                    }
                    sda.Fill(dt);
                }
                return dt;
            }

        }
    }

接着是app.config文件的内容

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="sql" connectionString="Data Source=(local);Initial Catalog=PhoneNumManager;Integrated Security=True"/>
  </connectionStrings>
</configuration>

然后是form1的内容：

在接着是form1里面的代码：

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

namespace 电话本
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            comboBox1.SelectedIndex = 0;
            //禁用自动生成列
            dataGridView1.AutoGenerateColumns = false;
            DataTable dt = SqlHelper.ExecuteDataTable("select pid,pname,pcellphone,ptypeid  from phonenum");
            dataGridView1.DataSource = dt;
        }

        private void dataGridView1_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            //获取当前行的数据
            string name = dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString();
            string pcellphone = dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString();
            txtName.Text = name;
            txtCellPhone.Text = pcellphone;
        }

        private void btBD_Click(object sender, EventArgs e)
        {
            //执行多条件搜索
            //拼接sql语句
            #region 全表都会对比的SQL多条件查询
            //StringBuilder sql = new StringBuilder("select * from PhoneNum where 1=1");
            //if (comboBox1.SelectedIndex != 0)
            //{
            //    sql.Append(" and ptypeid=" + comboBox1.Text.Split('|')[0]);
            //}
            //if (txtSerachName.Text.Trim().Length > 0)
            //{
            //    sql.Append("and pname like '" + txtSerachName.Text.Trim() + "%'");

            //}
            //if (txtSerachCell.Text.Trim().Length > 0)
            //{
            //    sql.Append(" and pcellphone like '%" + txtSerachCell.Text.Trim() + "%'");
            //}
            //MessageBox.Show(sql.ToString());
            #endregion


            #region 多条件搜索时，使用list集合来拼接条件


            //StringBuilder sql = new StringBuilder("select * from PhoneNum");
            //List<string> wheres = new List<string>();
            //if (comboBox1.SelectedIndex != 0)
            //{
            //   // sql.Append(" and ptypeid=" + comboBox1.Text.Split('|')[0]);
            //    wheres.Add("ptypeid=" + comboBox1.Text.Split('|')[0]);

            //}
            //if (txtSerachName.Text.Trim().Length > 0)
            //{
            //   // sql.Append("and pname like '" + txtSerachName.Text.Trim() + "%'");
            //    wheres.Add(" pname like '" + txtSerachName.Text.Trim() + "%'");

            //}
            //if (txtSerachCell.Text.Trim().Length > 0)
            //{
            //    //sql.Append(" and pcellphone like '%" + txtSerachCell.Text.Trim() + "%'");
            //    wheres.Add(" pcellphone like '%" + txtSerachCell.Text.Trim() + "%'");
            //}
            判断用户是否选择了条件
            //if (wheres.Count>0)
            //{
            //    string wh = string.Join(" and ",wheres.ToArray());
            //    sql.Append(" where "+wh);
            //}




            #endregion
            #region 多条件搜索使用带参数的sql语句




            StringBuilder sql = new StringBuilder("select * from PhoneNum");
            List<string> wheres = new List<string>();
            List<SqlParameter> listParamter = new List<SqlParameter>();

            if (comboBox1.SelectedIndex != 0)
            {
                // sql.Append(" and ptypeid=" + comboBox1.Text.Split('|')[0]);
                // wheres.Add("ptypeid=" + comboBox1.Text.Split('|')[0]);
                wheres.Add("ptypeid=@typeid");
                listParamter.Add(new SqlParameter("@typeid", comboBox1.Text.Split('|')[0]));
            }
            if (txtSerachName.Text.Trim().Length > 0)
            {
                // sql.Append("and pname like '" + txtSerachName.Text.Trim() + "%'");
                // wheres.Add(" pname like '" + txtSerachName.Text.Trim() + "%'");
                wheres.Add("pname like @pname");
                listParamter.Add(new SqlParameter("@pname", "%" + txtSerachName.Text.Trim() + "%"));

            }
            if (txtSerachCell.Text.Trim().Length > 0)
            {
                //sql.Append(" and pcellphone like '%" + txtSerachCell.Text.Trim() + "%'");
                //wheres.Add(" pcellphone like '%" + txtSerachCell.Text.Trim() + "%'");
                wheres.Add("pcellphone like @cellphone");
                listParamter.Add(new SqlParameter("@cellphone","%"+ txtSerachCell.Text.Trim()+"%"));
            }
            //判断用户是否选择了条件
            if (wheres.Count > 0)
            {
                string wh = string.Join(" and ", wheres.ToArray());
                sql.Append(" where " + wh);
            }


            SqlHelper.ExecuteDataTable(sql.ToString(), listParamter.ToArray());

            #endregion

            //MessageBox.Show(sql.ToString());

        }
    }
}