ADO.NET之多条件查询

多条件查询及模糊查询

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace _05加多条件查询
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            LoadDeskInfo();
        }

        private void LoadDeskInfo()
        {
            List<DeskInfo> list = new List<DeskInfo>();
            string sql = "select deskid ,deskname,desknamepinyin ,desknum from DeskInfo";
            DataTable dt = SqlHelper.ExecuteTable(sql);
            //是否有数据
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    DeskInfo dk = RowToDeskInfo(dr);
                    list.Add(dk);
                }
            }
            dgv.AutoGenerateColumns = false;
            dgv.DataSource = list;
            dgv.SelectedRows[0].Selected = false;
        }

        //关系转对象
        private DeskInfo RowToDeskInfo(DataRow dr)
        {
            DeskInfo dk = new DeskInfo();
            dk.DeskId = Convert.ToInt32(dr["DeskId"]);
            dk.DeskName = dr["DeskName"].ToString();
            dk.DeskNamePinYin = dr["DeskNamePinYin"].ToString();
            dk.DeskNum = dr["DeskNum"].ToString();
            return dk;
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            //所有文本框的值
            List<SqlParameter> list = new List<SqlParameter>();
            string sql = "select DeskId, deskname,DeskNamePinYin,DeskNum from DeskInfo where DeskDelFlag=0";
            if (!string.IsNullOrEmpty(txtName.Text))//有名字
            {
                sql += " and DeskName like @DeskName";
                list.Add(new SqlParameter("@DeskName", "%" + txtName.Text + "%"));
            }
            if (!string.IsNullOrEmpty(txtPinYin.Text))//有拼音
            {
                sql += " and DeskNamePinYin=@DeskNamePinYin";
                list.Add(new SqlParameter("@DeskNamePinYin", txtPinYin.Text));
            }
            if (!string.IsNullOrEmpty(txtNum.Text))//有编号
            {
                sql += " and DeskNum=@DeskNum";
                list.Add(new SqlParameter("@DeskNum", txtNum.Text));
            }
            List<DeskInfo> listDesk = new List<DeskInfo>();
            DataTable dt = SqlHelper.ExecuteTable(sql, list.ToArray());
            if (dt.Rows.Count>0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    DeskInfo dk = RowToDeskInfo(dr);
                    listDesk.Add(dk);
                }
            }
            dgv.DataSource = listDesk;
        }
    }
}