多条件查询

第一步在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;
       }