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://www.cnblogs.com/jiangyan219/articles/6930177.html