util-C# 复杂条件查询（sql 复杂条件查询）查询解决方案

using System;
using System.Collections.Generic;
using System.Linq;

using Ylbtech.Model;
using Microsoft.DBUtility;
using System.Text;
using System.Data.SqlClient;
using Ylbtech.Common;

namespace Ylbtech.DAL
{
    /// <summary>
    ///Employee 的摘要说明
    ///desc：员工操作类
    ///author:yuanbo
    ///date:2013-6-12
    /// </summary>
    public class Employee
    {
        /// <summary>
        /// ylb:查询员工信息
        /// </summary>
        /// <param name="dalEmployee"></param>
        /// <returns></returns>
        public static IList<EmployeeInfo> GetAllSearch(EmployeeInfo dalEmployee)
        {
            IList<EmployeeInfo> dals = new List<EmployeeInfo>();

            string sql = "select employeeId,[id],username,sex,cardNo"
                + ",hireDate,e.departmentId,state,ToRegularDate,d.departmentName from Employee e inner join Department d on e.departmentId=d.departmentId";

            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(sql);


            IList<SqlParameter> sqlParamI = new List<SqlParameter>();

            if (dalEmployee.EmployeeId != 0)   /*单一查询条件*/
            {
                sqlParamI.Add(new SqlParameter("@employeeId", dalEmployee.EmployeeId));
                sbSql.Append(" where employeeId=@employeeId");
            }
            else if (dalEmployee.Id.Length > 0) /*单一查询条件*/
            {
                sqlParamI.Add(new SqlParameter("@id", dalEmployee.Id));
                sbSql.Append(" where [id]=@id");
            }
            else  /*复合条件查询*/
            {
                sbSql.Append(" where 1=1");
                if (dalEmployee.Username.Length > 0)
                {
                    sbSql.Append(" and username=@username");
                    sqlParamI.Add(new SqlParameter("@username", dalEmployee.Username));
                }
                if (dalEmployee.Sex != "-1")
                {
                    sbSql.Append(" and sex=@sex");
                    sqlParamI.Add(new SqlParameter("@sex", dalEmployee.Sex));
                }
                if (dalEmployee.CardNo.Length > 0)
                {
                    sbSql.Append(" and cardNo=@cardNo");
                    sqlParamI.Add(new SqlParameter("@cardNo", dalEmployee.CardNo));
                }
                DateTime hireDate;
                if (DateTime.TryParse(dalEmployee.HireDate, out hireDate))
                {
                    sbSql.Append(" and hiredate=@hiredate");
                    sqlParamI.Add(new SqlParameter("@hiredate", hireDate));
                }
                if (dalEmployee.DepartmentId != -1)
                {
                    sbSql.Append(" and e.departmentId=@departmentId");
                    sqlParamI.Add(new SqlParameter("@departmentId", dalEmployee.DepartmentId));
                }
                if (dalEmployee.State != "-1")
                {
                    sbSql.Append(" and state=@state");
                    sqlParamI.Add(new SqlParameter("@state", dalEmployee.State));
                }
                DateTime toRegularDate;
                if (DateTime.TryParse(dalEmployee.ToRegularDate, out toRegularDate))
                {
                    sbSql.Append(" and toRegularDate=@toRegularDate");
                    sqlParamI.Add(new SqlParameter("@toRegularDate", dalEmployee.ToRegularDate));
                }
            }
            sbSql.Append(" order by employeeId desc");

            SqlParameter[] sqlParam = new SqlParameter[sqlParamI.Count];

            //A：方式一
            //for (int i = 0; i < sqlParamI.Count; i++)
            //{
            //    sqlParam[i] = sqlParamI[i];
            //}
            //A:方式二
            sqlParam = sqlParamI.ToArray();


            using (SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.ConnStr_WelfareSystem, System.Data.CommandType.Text, sbSql.ToString(), sqlParam))
            {
                while (sdr.Read())
                {
                    EmployeeInfo dal = new EmployeeInfo()
                    {
                        EmployeeId = sdr.GetInt32(0),
                        Id = sdr[1] == System.DBNull.Value ? "" : sdr.GetString(1),
                        Username = sdr[2] == System.DBNull.Value ? "" : sdr.GetString(2),
                        Sex = sdr[3] == System.DBNull.Value ? "" : sdr.GetString(3),
                        CardNo = sdr[4] == System.DBNull.Value ? "" : sdr.GetString(4),

                        HireDate = DateTimeFun.DateTimeConvertToyyyy_MM_dd(sdr[5] == System.DBNull.Value ? "" : sdr.GetString(5)),
                        DepartmentId = sdr.GetInt32(6),
                        State = sdr[7] == System.DBNull.Value ? "" : sdr.GetString(7),
                        ToRegularDate = DateTimeFun.DateTimeConvertToyyyy_MM_dd(sdr[8] == System.DBNull.Value ? "" : sdr.GetString(8)),
                        DepartmentName = sdr[9] == System.DBNull.Value ? "" : sdr.GetString(9)
                    };

                    dals.Add(dal);

                }
            }
            return dals;
        }

        public Employee()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //
        }
    }
}