关于多条件查询，数据访问层的写法！

[xingshungames]

        /// <summary>
        /// 查询满足条件的用户总数
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <param name="userPass">验证状态</param>
        /// <param name="power">权限</param>
        /// <returns>用户总数</returns>
        public int GetUserCount(string userName, int userPass, int power)
        {
            StringBuilder buffer = new StringBuilder();
            buffer.Append("select count(1) from Users");
            buffer.Append(" where 1=1 ");
            if (userName != "")
            {
                buffer.Append(" and UserName like '%'+@UserName+'%' ");
            }
            if (userPass != -1)
            {
                buffer.Append(" and UserPass=@UserPass ");
            }
            if (power != -1)
            {
                buffer.Append(" and Power=@Power ");
            }
            SqlParameter[] parameters =
            {
                new SqlParameter("@UserName",SqlDbType.VarChar,20),
                new SqlParameter("@UserPass",SqlDbType.TinyInt),
                new SqlParameter("@Power",SqlDbType.TinyInt)
            };
            parameters[0].Value = userName;
            parameters[1].Value = userPass;
            parameters[2].Value = power;
            SqlDbHelper db = new SqlDbHelper();
            return int.Parse(db.ExecuteScalar(buffer.ToString(), CommandType.Text, parameters).ToString());
        }

这是我写的一个多条件查询的方法，是在数据访问层下的，然后我总感觉这样数据访问层就加入了逻辑，有点不太舒服，还有那个1=1 and .....，有没有更好的写法？一般多条件查询功能都是怎么实现的呢？

[hjw01592]

我觉得，这样挺好的。。。

[jack15850798154]

我也是这样写的。其实这样写也没有什么不好啊。
如果你认为不好可以把这段逻辑分析放到SQL中进行处理。。（用存储过程等。）

[丰云]

或者用linq吧，那样会轻松很多，也不会有你这种奇怪的感觉拉

[sjt000]

我一般写在存储过程中，但差不多 也是这样啊～

[wx8849]

 public static B_policyCollection SelectCollection(int userid, B_policy bmss, int pagerows, int pageno)
        {
            crsDBUtility.DBHelper db = new DBHelper();
            B_policyCollection bpcon=new B_policyCollection();

            string str_select = " WITH Table_Data AS ( SELECT  ROW_NUMBER() OVER (ORDER BY Discount DESC) AS RowIndexs, *,dbo.fc_GetPolicyFlagName(Flag) as FlagName  FROM B_Policy A WITH (NOLOCK) where 1 =1 and PromulgatorCode =  " + userid;
             if(bmss.Fromcitycode.Trim().ToUpper()!="ALL")
                 str_select += "  and  FromCityCode like '%/" + bmss.Fromcitycode.Trim().ToUpper() + "/%' ";
            if (bmss.Tocitycode.Trim().ToUpper() != "ALL")
                str_select += "  and  ToCityCode like '%/" + bmss.Tocitycode.Trim().ToUpper() + "/%' ";
            if (bmss.Airwaycode.Trim().ToUpper() != "ALL")
                 str_select += "  and AirwayCode like '%/" + bmss.Airwaycode.Trim().ToUpper() + "/%'";
             if (bmss.Begindate != null && bmss.Begindate > DateTime.MinValue)
                 str_select += "  and  ( BeginDate >= '" + bmss.Begindate.Date.ToString() + "') ";
             if (bmss.Enddate != null && bmss.Enddate > DateTime.MinValue)
                 str_select += "  and  ( Enddate <= '" + bmss.Enddate.Date.ToString().Trim() + "') ";
            if(bmss.OverdueDate!=null&&bmss.OverdueDate>DateTime.MinValue)
                str_select+=" and (Enddate<'"+bmss.OverdueDate.Date.ToString().Trim()+"')";

            
             if (bmss.Traveltype != 0)
                 str_select += "  and Traveltype = '" + bmss.Traveltype.ToString().Trim() + "'";
             if (bmss.Flag == 1 || bmss.Flag == 2)
             {
                 str_select += "  and Flag =  " + bmss.Flag + " ";
             }

             str_select += " )";
             str_select += "  SELECT * FROM Table_Data   WHERE RowIndexs BETWEEN @row1 AND @row2 ";
          
             DbCommand cmd = db.GetSqlStringCommond(str_select);
             db.AddParameter(cmd, "@row1", pagerows * (pageno - 1) + 1);
             db.AddParameter(cmd, "@row2", pagerows * pageno);

             DataTable dt = db.ExecuteDataTable(cmd);
             if (dt != null && dt.Rows.Count > 0)
             {
                 foreach (DataRow dr in dt.Rows)
                 {
                     B_policy bms = ParseDataRow(dr);
                     if (bms != null)
                     {
                         bpcon.Add(bms);
                     }
                 }
             }
             return bpcon;

        }

[criedshy]

用存储过程

[firstlanheng]

挺好啊，只要功能实现就行了！

[ZHUKY]

顶....存储过程

[sabty]

你好！

这种方式在编写代码的时候会写很多判断，不怎么方便。其它的也并没有什么不好。

建议你使用下面的方式，会更代码更清晰。

public int GetUserCount(string userName, int userPass, int power)
{
string commandText = @"
SELECT COUNT(1) FROM Users
WHERE (@UserName = '' OR UserName like '%'+@UserName+'%')
AND (@UserPass = -1 OR UserPass = @UserPass)
AND (@Power = -1 OR Power=@Power)";
SqlParameter[] parameters =
{
new SqlParameter("@UserName",SqlDbType.VarChar,20),
new SqlParameter("@UserPass",SqlDbType.TinyInt),
new SqlParameter("@Power",SqlDbType.TinyInt)
};
parameters[0].Value = userName;
parameters[1].Value = userPass;
parameters[2].Value = power;
SqlDbHelper db = new SqlDbHelper();
return int.Parse(db.ExecuteScalar(commandText, CommandType.Text, parameters).ToString());
}

[yangchu1986]

我们都用这种方法啊。本人也觉得挺好的。

[honkerhero]

条件这个东西有点像业务逻辑相关的，我觉得放BLL或DAL层都可以，

不过一般涉及到具体表字段都喜欢放DAL层，个人喜好吧

[skyonline12]

顺哥，给力吧 被我看到了，哇哈哈