Dapper 封装03-组装SQL-多条件

【Dapper 封装03-组装SQL-多条件】宁可枝头抱香死，何曾吹落北风中。这篇文章主要讲述Dapper 封装03-组装SQL-多条件相关的知识，希望能为你提供帮助。
在上一篇中。我们可以获得单条where 语句拼装的条件。但是我们往往 where 条件有很多。所以这篇组要来解决多条件问题。我们定义一个类 WhereClip ,其目的主要是讲多个单条件的SQL给组装起来。那么这个类应该有那些元素呢？我觉得应该含有完整的SQL和参数列表。

protected StringBuilder sbWhereSql = new StringBuilder(); protected bool toWrapBrackets = false; protected readonly List< DataParameter> parameters = new List< DataParameter> (); public string WhereSql { get { return sbWhereSql.ToString(); } set { sbWhereSql = new StringBuilder(value); } }private bool fixWhere = true; /// < summary> /// sql语句前是否添加where /// < /summary> public bool FixWhere { get { return fixWhere; } set { fixWhere = value; } } public string ToSql() { StringBuilder sbSql = new StringBuilder(); if (sbWhereSql.Length > 0) { if (fixWhere) { sbSql.Append(" WHERE "); } else { sbSql.Append(" AND "); } sbSql.Append(WhereSql); } return sbSql.ToString(); } public DynamicParameters GetDynamicParms() { DynamicParameters dynamicParms = null; if (this.parameters != null & & this.parameters.Count > 0) { dynamicParms = new DynamicParameters(); List< DataParameter> .Enumerator en = this.parameters.GetEnumerator(); while (en.MoveNext()) { dynamicParms.Add(en.Current.Name, en.Current.Value =https://www.songbingjia.com/android/= null ? DBNull.Value : en.Current.Value); } } return dynamicParms; } internal List< DataParameter> Parameters => this.parameters; public static bool IsNullOrEmpty(WhereClip where) { return ((object)where) == null || where.sbWhereSql.Length == 0; } public WhereClip And(WhereClip whereClip) { if(IsNullOrEmpty(whereClip)) { return this; } AddParameters(whereClip); if (sbWhereSql.Length > 0) { sbWhereSql.Append(" AND "); if (whereClip.toWrapBrackets) { sbWhereSql.Append($"({whereClip.WhereSql})"); } else { sbWhereSql.Append($"{whereClip.WhereSql}"); } } else { sbWhereSql.Append(whereClip.WhereSql); } this.toWrapBrackets = true; return this; } public WhereClip Or(WhereClip whereClip) { if (IsNullOrEmpty(whereClip)) { return this; } AddParameters(whereClip); if (sbWhereSql.Length > 0) { sbWhereSql.Append(" OR "); if (whereClip.toWrapBrackets) { sbWhereSql.Append($"({whereClip.WhereSql})"); } else { sbWhereSql.Append($"{whereClip.WhereSql}"); } } else { sbWhereSql.Append(whereClip.WhereSql); } this.toWrapBrackets = true; return this; } public WhereClip Not() { if (IsNullOrEmpty(this)) { return this; } sbWhereSql.Insert(0, "NOT("); sbWhereSql.Append(") "); this.toWrapBrackets = false; return this; } public WhereClip ToWrap() { if (IsNullOrEmpty(this)) { return this; } sbWhereSql.Insert(0, "("); sbWhereSql.Append(") "); this.toWrapBrackets = false; return this; } public WhereClip ApendWhere(string strSubWhere) { sbWhereSql.Append(strSubWhere); return this; } public static WhereClip operator & (WhereClip left, WhereClip right) { WhereClip newWhere = new WhereClip(); newWhere.And(left); newWhere.And(right); return newWhere; }public static WhereClip operator |(WhereClip left, WhereClip right) { WhereClip newWhere = new WhereClip(); newWhere.Or(left); newWhere.Or(right); return newWhere; }public static WhereClip operator !(WhereClip right) { return right.Not(); } private void AddParameters(WhereClip whereClip) { if (whereClip == null || whereClip.Parameters == null) { return; } List< DataParameter> additional = whereClip.Parameters; foreach (DataParameter item in additional) { string propertyName = item.Name; int chkFix = item.Name.IndexOf("_pfix_"); if (chkFix > 0) { propertyName = item.Name.Substring(0, chkFix); } DataParameter targetItem = parameters.FirstOrDefault(m => m.Name == item.Name); if (targetItem == null) { parameters.Add(item); } else { if (item.Value =https://www.songbingjia.com/android/= null|| (item.Value != null & & targetItem.Value != null & & item.Value.Equals(targetItem.Value))) { continue; } string srcParmName = item.Name; int count = parameters.Count(m => m.Name.StartsWith($"{srcParmName}_pfix_")); string newParmName = $"{srcParmName}_pfix_{count + 1}"; item.Name = newParmName; parameters.Add(item); bool chk = additional.Any(m => m.Name == newParmName); string replace = chk ? $"@{newParmName}_pfixtmp " : $"@{newParmName} "; whereClip.WhereSql = whereClip.WhereSql.Replace($"@{srcParmName}", replace); } } whereClip.WhereSql = whereClip.WhereSql.Replace("_pfixtmp", ""); }

这里面每个操作方法都返回 this 本身。主要是方便连写。AddParameters 主要解决的参数列表中出现多个同名的参数。重新命名的问题。
那么有了上面的方法。我们可以比较方便的调用了。