C# sql中拼接查询条件时：避免where 1=1出现

最新推荐文章于 2024-01-09 15:39:44 发布

Jayzai

最新推荐文章于 2024-01-09 15:39:44 发布

阅读量923

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/jayzai/article/details/105262263

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

        string sql = string.Format("select * from {0} where 1=1", "user");
            //拼接查询条件
            string sqlWhere = string.Empty;
            if (userId > 0)
            {
                sqlWhere += string.Format(" and userId={0}", userId);
            }
            if (gender != -1)
            {
                sqlWhere += string.Format(" and gender={0}", gender);
            }
            if (status != -1)
            {
                sqlWhere += string.Format(" and `status`={0}", status);
            }
            sql += sqlWhere;
            sql += string.Format(" order by userId desc limit {0},{1}", skip, limit);

换成如下：

            string sql = string.Format("select * from {0}", "user");
            //拼接查询条件
            List<string> queryWhere = new List<string>();
            if (userId > 0)
            {
                queryWhere.Add(string.Format("userId={0}", userId));
            }
            if (gender != -1)
            {
                queryWhere.Add(string.Format("gender={0}", gender));
            }
            if (status != -1)
            {
                queryWhere.Add(string.Format("`status`={0}", status));
            }
            string sqlWhere = GetQueryWhere(queryWhere);
            sql += sqlWhere;
            sql += string.Format(" order by userId desc limit {0},{1}", skip, limit);

        public static string GetQueryWhere(this List<string> list)
        {
            if (list == null || list.Count <= 0)
                return "";

            //string sqlWhere = string.Join(" and ", list);
            //return " where " + sqlWhere;

            bool flag = true;
            StringBuilder strBuilder = new StringBuilder();
            foreach (var q in list)
            {
                if (flag)
                {
                    strBuilder.Append(" where ");
                    flag = false;
                }
                else
                {
                    strBuilder.Append(" and ");
                }
                strBuilder.Append(q);
            }
            return strBuilder.ToString();
        }