Dapper 灏佽02-缁勮SQL _Dapper

君不见长松卧壑困风霜，时来屹立扶明堂。这篇文章主要讲述Dapper 灏佽02-缁勮SQL相关的知识，希望能为你提供帮助。
鏍囩锛?a href='http://www.mamicode.com/so/1/%e8%bd%ac%e4%b9%89' title='杞箟'>杞箟 inf 鎿嶄綔 sed com open return 鎵ц one
鍦?SQLSERVER 閲岋紝鎴戜滑鍦ㄦ墽琛屽懡浠ょ殑鏃跺€欐垜浠槸鍙互杩涜鍙傛暟鍖栦紶閫掔殑銆傝繖涓ソ澶勪富瑕佸彲浠ラ槻姝?strong>娉ㄥ叆銆?/strong>
【Dapper 灏佽02-缁勮SQL】瀹氫箟鎺ュ彈鍙傛暟鐨勭被锛?/p>

public class DataParameter { public DataParameter() { } public DataParameter(string name,object value) { this.Name = name; this.Value = https://www.songbingjia.com/android/value; } public string Name { get; set; } public object Value { get; set; } }

鎴戜滑姝ｅ父鍐欎竴涓?Where 璇彞锛屾瘮濡?nbsp; WHERE Age=23 and name like 鈥?delaywu%鈥?/strong> 銆備粠杩欏彞璇濅腑鎴戜滑鐭ラ亾涓€涓獁here 琛ㄨ揪寮?鏄敱琛ㄥ瓧娈靛悕 + 鎿嶄綔绗﹀彿 + 鍊?nbsp; 缁勬垚鐨勫熀鏈搷浣溿€?/p>鎵€浠ユ垜浠垱寤轰竴涓柟娉曟潵鏋勫缓杩欐牱鐨凷QL

private static WhereClip BuildWhereChip(string propertyName, object value, QueryOper op, string paramName=null) { if(op !=QueryOper.IsNull & & op!=QueryOper.IsNotNull & & (value=https://www.songbingjia.com/android/=null || value==DBNull.Value)) { return null; } WhereClip where = new WhereClip(); StringBuilder sbSql = new StringBuilder($"{propertyName}{SqlQueryUtils.ToString(op)}"); if (value !=null & & value !=DBNull.Value) { if(paramName ==null) { paramName = SqlQueryUtils.GetParmName(propertyName); }if(paramName.Length> 0) { sbSql.Append($"@{paramName}"); where.Parameters.Add(new DataParameter(paramName, value)); } else { sbSql.Append($"{value}"); } } where.WhereSql = sbSql.ToString(); return where; }

public static string GetParmName(string propertyName) { //[a.name]="delaywu" string paramName = propertyName.Replace("[", "").Replace("]", ""); if(paramName.Contains(".")) { //name int lstIdx = paramName.LastIndexOf(鈥?/span>.鈥?/span>); paramName = paramName.Substring(lstIdx + 1).Trim(); } if(!System.Text.RegularExpressions.Regex.IsMatch(paramName,"^[a-zA-Z1-9_]*$")) { return string.Empty; } return paramName; }

GetParmName 杩欎釜鏂规硶涓昏鏄?鍙兘鍦ㄥ啓澶氳〃鏌ヨ鐨勬椂鍊欏嚭鐜?a.Name b.age 杩欑被鐨勬儏鍐点€傝幏寰楀叾鐪熷疄鐨?瀛楁鍚嶇О銆備互鍙奫] 杩欎釜淇℃伅鐨勫鐞嗐€?br />鏈変簡鍩虹鐨勬瀯寤烘柟娉曘€傛垜浠彲浠ユ妸鐩稿叧鎿嶇殑绗︾殑 WHERE 閮藉彲浠ョ敓鎴愬嚭鏉ヤ簡銆?/pre>
文章图片

文章图片

public static WhereClip Eq(string propertyName, object value, string paramName = null) { return BuildWhereChip(propertyName, value, QueryOper.Eq, paramName); }public static WhereClip NotEq(string propertyName, object value, string paramName = null) { return BuildWhereChip(propertyName, value, QueryOper.NotEq, paramName); }public static WhereClip IsNull(string propertyName) { return BuildWhereChip(propertyName, null, QueryOper.IsNull, null); } public static WhereClip IsNotNull(string propertyName) { return BuildWhereChip(propertyName, null, QueryOper.IsNotNull, null); } public static WhereClip StartWith(string propertyName, string value, string paramName = null) { return Like(propertyName, value.Replace("%", "[%]").Replace("_", "[_]") + 鈥?/span>%鈥?/span>, paramName); } public static WhereClip EndsWith(string propertyName, string value, string paramName = null) { return Like(propertyName, 鈥?/span>%鈥?/span> + value.Replace("%", "[%]").Replace("_", "[_]"), paramName); }public static WhereClip Gt(string propertyName, object value, string paramName = null) { return BuildWhereChip(propertyName, value, QueryOper.Gt, paramName); } public static WhereClip Lt(string propertyName, object value, string paramName = null) { return BuildWhereChip(propertyName, value, QueryOper.Lt, paramName); } public static WhereClip Le(string propertyName, object value, string paramName = null) { return BuildWhereChip(propertyName, value, QueryOper.Le, paramName); } public static WhereClip Ge(string propertyName, object value, string paramName = null) { return BuildWhereChip(propertyName, value, QueryOper.Ge, paramName); } public static WhereClip Like(string propertyName, string value, string paramName = null) { if (string.IsNullOrEmpty(value)) { return null; } return BuildWhereChip(propertyName, value, QueryOper.Like, paramName); }public static WhereClip Between(string propertyName, object lo, object hi,string paramName=null) { WhereClip where = new WhereClip(); StringBuilder sbSql = new StringBuilder($"{propertyName} between "); if(paramName==null) { paramName = SqlQueryUtils.GetParmName(propertyName); } if(propertyName.Length> 0) { string strParamName1 = paramName + "_pmin"; string strParamName2 = paramName + "_pmax"; sbSql.Append($"@{strParamName1} and @{strParamName2} "); where.Parameters.Add(new DataParameter(strParamName1, lo)); where.Parameters.Add(new DataParameter(strParamName2, hi)); } else { sbSql.Append($"{lo} and {hi}"); } where.WhereSql = sbSql.ToString(); return where; }public static WhereClip DapperIn< T> (string propertyName,IEnumerable< T> values,string paramName=null) { if (values == null || !values.Any()) { return null; }WhereClip where = new WhereClip(); if(paramName==null) { paramName = SqlQueryUtils.GetParmName(propertyName); } if(paramName.Length> 0) { where.Parameters.Add(new DataParameter(paramName, values)); where.WhereSql = $"{propertyName} in @{paramName} "; } else { if(typeof(T).FullName==typeof(string).FullName) { string strIn = string.Join< T> ("鈥?鈥?/span>", values); where.WhereSql = $"{propertyName} in ({strIn})"; } else { string strIn = string.Join< T> (",", values); where.WhereSql = $"{propertyName} in ({strIn})"; } } return where; }public static WhereClip DapperNotIn< T> (string propertyName, IEnumerable< T> values, string paramName = null) { if (values == null || !values.Any()) { return null; } WhereClip where = new WhereClip(); if (paramName == null) { paramName = SqlQueryUtils.GetParmName(propertyName); } if (paramName.Length > 0) { where.Parameters.Add(new DataParameter(paramName, values)); where.WhereSql = $"{propertyName} not in @{paramName} "; } else { if (typeof(T).FullName == typeof(string).FullName) { string strIn = string.Join< T> ("鈥?鈥?/span>", values); where.WhereSql = $"{propertyName} not in (鈥榹strIn}鈥? "; } else { string strIn = string.Join< T> (",", values); where.WhereSql = $"{propertyName} not in ({strIn}) "; } } return where; }

View Code涓婇潰鐨勪唬鐮併€傛垜涓昏璇翠竴涓?Like 鐩稿叧鐨勩€傛暟鎹簱閲岄潰鎴戜滑缁忓父浣跨敤 []鏉ヨ浆涔夌壒娈婄殑鍗曡瘝銆俵ike 涓?鎴戜滑鏄杞箟鐨勶紝鍏舵_涔熻鐨勩€?/p>
文章图片

娴嬭瘯涓€涓嬶細

文章图片