三层架构多条件查询

Part1-表示层

public partial class formSearch : Form
    {
        public formSearch()
        {
            InitializeComponent();
            dataGridView1.Columns[4].Visible = false;//指定查询到的某些列不显示在datagridview中
            dataGridView1.Columns[5].Visible = false;
            dataGridView1.Columns[6].Visible = false;
            dataGridView1.Columns[8].Visible = false;
        }
        CustomerBLL bll = new CustomerBLL();//调用业务逻辑层之前先将类实例化
        public void btnSelect_Click(object sender, EventArgs e)
        {
            string name = txtName1.Text.Trim();//按照名字查询
            string phone = txtPhone1.Text.Trim();//按照电话查询
            string idcard = txtIDcard.Text.Trim();//按照身份证查询
            string address = txtAddress.Text.Trim();//按照地址查询

            if (name != "" || phone != "" || idcard != "" || address != "")
            {
                CustomerSearchVO vo = new CustomerSearchVO();
                vo.Name = name;
                vo.Phone = phone;
                vo.IDCard = idcard;
                vo.Address = address;

                IList<Customer> list = bll.GetByCondition(vo);//调用业务逻辑层的方法
                this.dataGridView1.AutoGenerateColumns = false;//设定列不能自动生成
                dataGridView1.DataSource = list;
            }
            else
            {
                MessageBox.Show("至少输入一项", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }
        /// <summary>
        /// 显示客户类型
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {

            if (e == null || e.Value == null || !(sender is DataGridView))
                return;
            DataGridView view = (DataGridView)sender;

            if (view.Columns[e.ColumnIndex].DataPropertyName == "type")
            {
                int val = Convert.ToInt32(e.Value);
                switch (val)
                {
                    case 1:
                        e.Value = "大客户";
                        break;

                    case 2:
                        e.Value = "一般客户";
                        break;
                    case 3:
                        e.Value = "小客户";
                        break;
                    default:
                        break;
                }
                e.FormattingApplied = true;
            }
        }
      
        /// <summary>
        /// 删除和修改数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        /*static public string s1;
        static public string s2;
        static public DateTime s3;
        static public string s4;
        static public string s5;
        static public string s6;*/
        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            dataGridView1.AutoGenerateColumns = false;//设定列不能自动作成
            if (dataGridView1.Columns[e.ColumnIndex].Name == "linkDetail")
            {
                int r = this.dataGridView1.CurrentRow.Index;//当前行的索引值
                formDetail fD = new formDetail(
                    int.Parse(this.dataGridView1.Rows[r].Cells[0].Value.ToString()),
                    this.dataGridView1.Rows[r].Cells[1].Value.ToString(),
                    this.dataGridView1.Rows[r].Cells[2].Value.ToString(),
                    this.dataGridView1.Rows[r].Cells[3].Value.ToString(),
                    this.dataGridView1.Rows[r].Cells[4].Value.ToString(),
                    this.dataGridView1.Rows[r].Cells[5].Value.ToString(),
                    this.dataGridView1.Rows[r].Cells[6].Value.ToString(),
                    DateTime.Parse(this.dataGridView1.Rows[r].Cells[7].Value.ToString()),
                    this.dataGridView1.Rows[r].Cells[8].Value.ToString(),
                    this.dataGridView1.Rows[r].Cells[9].Value.ToString());
                fD.Show();
            }
            else if (dataGridView1.Columns[e.ColumnIndex].Name == "linkEdit")
            {
                int r = this.dataGridView1.CurrentRow.Index;//当前行的索引值
                formModify fM = new formModify(
                    int.Parse(this.dataGridView1.Rows[r].Cells[0].Value.ToString()),
                    this.dataGridView1.Rows[r].Cells[1].Value.ToString(),
                    this.dataGridView1.Rows[r].Cells[2].Value.ToString(),
                    this.dataGridView1.Rows[r].Cells[3].Value.ToString(),
                    this.dataGridView1.Rows[r].Cells[4].Value.ToString(),
                    this.dataGridView1.Rows[r].Cells[5].Value.ToString(),
                    this.dataGridView1.Rows[r].Cells[6].Value.ToString(),
                    DateTime.Parse(this.dataGridView1.Rows[r].Cells[7].Value.ToString()),
                    this.dataGridView1.Rows[r].Cells[8].Value.ToString(),
                    this.dataGridView1.Rows[r].Cells[9].Value.ToString());
                fM.Show();
            }
        }

        /// <summary>
        /// 回车键
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void txtForm_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)//如果输入的是回车键 
            {
                this.btnSelect_Click(sender, e);//触发button事件 
            }
        }
    }

}

Part2-业务逻辑层

 public class CustomerBLL
    {
        CustomerDAL dal = new CustomerDAL();//调用数据访问层之前先要进行实例化

        public IList<Customer> GetByCondition(CustomerSearchVO vo)
        {
            return dal.GetByCondition(vo);
        }

}

Part3-数据访问层

public class CustomerDAL
    {

        /// <summary>
        /// 多条件查询
        /// </summary>
        /// <param name="address"></param>
        /// <returns></returns>
        public IList<Customer> GetByCondition(CustomerSearchVO vo)
        {
            IList<Customer> list = new List<Customer>();
            Customer customer = null;

            string where = "";
            if (Regex.IsMatch(vo.Name, "^[a-zA-Z]+$") && vo.Name != "")
            {
                where += " and pinyin like '%" + vo.Name + "%'";
                where += " or pinyin_abbr like '%" + vo.Name + "%'";
            }
            else
            {
                where += " and name like '%" + vo.Name + "%'";
            }
            if (vo.Phone != "")
            {
                where += " and phone like '%" + vo.Phone + "%'";
            }
            if (vo.Address != "")
            {
                where += " and address like '%" + vo.Address + "%'";
            }
            if (vo.IDCard != "")
            {
                where += " and id_card like '%" + vo.IDCard + "%'";
            }

            string sql = "SELECT id,name,type,id_card,birthday,phone,email,qq,address,remark,creationtime FROM customer WHERE 1=1 " + where + " order by creationtime desc";
            SQLiteCommand cmd = new SQLiteCommand(SQLiteHelper.Conn);
            IDataReader reader = SQLiteHelper.ExecuteReader(cmd, sql, null);

            while (reader.Read())
            {
                customer = new Customer();
                customer.ID = int.Parse(reader["id"].ToString());
                customer.Name = reader["name"].ToString();
                customer.Type = int.Parse(reader["type"].ToString());
                customer.IDCard = reader["id_card"].ToString();
                customer.Birthday = DateTime.Parse(reader["birthday"].ToString());
                customer.Phone = reader["phone"].ToString();
                customer.Email = reader["email"].ToString();
                customer.QQ = reader["qq"].ToString();
                customer.Address = reader["address"].ToString();
                customer.Remark = reader["remark"].ToString();
                customer.CreationTime = DateTime.Parse(reader["creationtime"].ToString());
                list.Add(customer);
            }
            return list;
        }

 

}