第一步在EmployeeListWindow.xaml中设计搜索的界面。如下:
设计代码如下:
<GroupBox Height="100" Header="搜索条件" DockPanel.Dock="Top"> <Grid> <CheckBox Content="姓名" Height="16" HorizontalAlignment="Left" Margin="0,7,0,0" Name="cbSearchByName" VerticalAlignment="Top" /> <TextBox Height="23" HorizontalAlignment="Left" Margin="50,5,0,0" Name="txtName" VerticalAlignment="Top" Width="120" /> <CheckBox Content="入职时间" Height="16" HorizontalAlignment="Left" Margin="194,7,0,0" Name="cbSearchByInDate" VerticalAlignment="Top" /> <DatePicker Height="25" HorizontalAlignment="Left" Margin="268,3,0,0" Name="dpInDateStart" VerticalAlignment="Top" Width="115" /> <TextBlock Height="23" HorizontalAlignment="Left" Margin="399,4,0,0" Name="textBlock1" Text="到" VerticalAlignment="Top" /> <DatePicker Height="25" HorizontalAlignment="Left" Margin="427,0,0,0" Name="dpInDateEnd" VerticalAlignment="Top" Width="115" /> <CheckBox Content="部门" Height="16" HorizontalAlignment="Left" Margin="0,45,0,0" Name="cbSearchByDept" VerticalAlignment="Top" /> <ComboBox DisplayMemberPath="Name" SelectedValuePath="Id" Height="23" HorizontalAlignment="Left" Margin="50,38,0,0" Name="cmbDept" VerticalAlignment="Top" Width="120" /> <Button Content="搜索" Height="23" HorizontalAlignment="Left" Margin="194,38,0,0" Name="btnSearch" VerticalAlignment="Top" Width="75" Click="btnSearch_Click" /> </Grid> </GroupBox>
第二步,搜索按钮的事件,代码如下:
List<string> whereList = new List<string>(); List<SqlParameter> paramsList = new List<SqlParameter>(); if (cbSearchByName.IsChecked == true) { whereList.Add("Name=@Name"); paramsList.Add(new SqlParameter("@Name", txtName.Text)); } if (cbSearchByInDate.IsChecked == true) { whereList.Add("InDate>=@InDateStart and InDate<=@InDateEnd"); paramsList.Add(new SqlParameter("@InDateStart", dpInDateStart.SelectedDate)); paramsList.Add(new SqlParameter("@InDateEnd", dpInDateEnd.SelectedDate)); } if (cbSearchByDept.IsChecked == true) { whereList.Add("DepartmentId=@DepartmentId"); paramsList.Add(new SqlParameter("@DepartmentId", cmbDept.SelectedValue)); } string whereSql = string.Join(" and ", whereList); string sql = "select * from T_Employee"; if (whereSql.Length > 0) { sql = sql + " where " + whereSql; } Employee[] result = new EmployeeDAL().Search(sql, paramsList); datagrid.ItemsSource = result; }
注意事项:1.首先建立一个存储查询参数的动态列表并动态添加
List<string> whereList = new List<string>(); List<SqlParameter> paramsList = new List<SqlParameter>();
whereList.Add("Name=@Name"); paramsList.Add(new SqlParameter("@Name", txtName.Text));
2.拼接where参数,注意and的前后要有空格,防止拼接时候字符串连接的太紧密
string whereSql = string.Join(" and ", whereList);
3.组成查询语句并显示查询结果。
-------------------------------------------
在EmployDAL.cs中添加:
public Employee[] Search(string sql, List<SqlParameter> parameters) { DataTable table = SqlHelper.ExecuteDataTable(sql, parameters.ToArray()); return ToEmployees(table); } private Employee[] ToEmployees(DataTable table) { Employee[] items = new Employee[table.Rows.Count]; for (int i = 0; i < table.Rows.Count; i++) { items[i] = ToModel(table.Rows[i]); } return items; }