where 1=1
在实际操作sql数据的过程中,经常会用到多个判定定位的条件,而这些条件有时候又未必存在,比如
select*from tableA where name=jiangyan//判定条件存在
select*from tableA //判定条件不存在
当出现多个判定条件时,如何使代码更健壮呢?
这里,我们介绍下 where 1=1
在本人实际的项目中,由于用到多个判定条件,且不知道这些条件又是不是存在,代码如下:
#region 获取表记录条数 /// <summary> /// 获取表记录条数 /// </summary> /// <returns></returns> public static int GetCount(int? staionId, int? yxCommDevId, int? commDevYxOrder) { try { string tabName = "TYxYk";//主表名 StringBuilder strSql = new StringBuilder(); strSql.Append("select count(*)from " + tabName); strSql.Append(" where 1=1 "); if (staionId != null) { strSql.Append(" and StationId=" + staionId); } if (yxCommDevId != null) { strSql.Append(" and YxCommDevId=" + yxCommDevId); } if (commDevYxOrder != null) { strSql.Append(" and CommDevYxOrder=" + commDevYxOrder); } SqlParameter[] cmdParams = { }; DataSet dsResult = SQLHelper.ExecuteDataset(GlobalData.DBConnStr, CommandType.Text, strSql.ToString(), cmdParams); if (dsResult.Tables[0].Rows.Count > 0) { DataRow dataRow = dsResult.Tables[0].Rows[0]; return int.Parse(dataRow[0].ToString()); } else return -1; } catch { return -1; } } #endregion #region 获取分页内容 /// <summary> /// 获取分页内容 /// </summary> /// <param name="PageIndex"></param> /// <param name="PageSize"></param> /// <returns></returns> public static List<TYxYk> GetPageContent(int pageIndex, int pageSize,int? staionId,int? yxCommDevId,int? commDevYxOrder) { List<TYxYk> yxYKs = new List<TYxYk>(); try { StringBuilder strSql = new StringBuilder(); string tabName, PKfld; tabName = "TYxYk";//主表名 // PKfld = "Id";//排序参照字段 strSql.Append("with Row as "); strSql.Append(" ( "); strSql.Append(" select jyid=ROW_NUMBER() over(order by StationId,YxCommDevId,CommDevYxOrder desc ),*from " + tabName); strSql.Append(" where 1=1 "); if (staionId != null) { strSql.Append(" and StationId=" + staionId); } if (yxCommDevId != null) { strSql.Append(" and YxCommDevId=" + yxCommDevId); } if (commDevYxOrder != null) { strSql.Append(" and CommDevYxOrder=" + commDevYxOrder); } strSql.Append(" ) "); strSql.Append(" select top " + pageSize.ToString() + " * from Row "); // strSql.Append(" left join TYxYk on Row.YxId=TYxYk.Id"); strSql.Append(" where jyid not in (select top " + (pageSize * (pageIndex - 1)).ToString() + " jyid from Row order by jyid)order by jyid "); SqlParameter[] cmdParams = { }; DataSet dsResult = SQLHelper.ExecuteDataset(GlobalData.DBConnStr, CommandType.Text, strSql.ToString(), cmdParams); foreach (DataRow dataRow in dsResult.Tables[0].Rows) { yxYKs.Add(new TYxYk(dataRow)); } } catch (Exception ex) { string error = ex.Message; } return yxYKs; } #endregion
-----------------------------------------------------------姜彦 20170601
![](https://files-cdn.cnblogs.com/files/jiangyan219/Alipay.bmp)
您的资助是我最大的动力!
金额随意,欢迎来赏!
![](https://files-cdn.cnblogs.com/files/jiangyan219/WeChat.bmp)
我写的东西能让你能懂,那是义务
毕竟占用了你生命中的宝贵的时间和注意力
要是你还能喜欢我的作品,那就是缘分了
如果,您希望更容易地发现我的新博客,不妨点击一下绿色通道的因为,我的写作热情也离不开您的肯定支持,感谢您的阅读,我是【青青子衿】!