多条件查询及模糊查询
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;
}
}
}