主要分为两部分：
1.分页
2.查询条件
首先新建一个QueryParameter用来接收分页排序和查询参数，这个类可以自定义，但是要保证查询查询和数据库的字段名一致，和后面的SqlQueryAndParameter中的接收参数一致，最好也和前端的传到接口中的数据格式一致，这样就不需要在进行其他处理，直接进行查询就行了
public class QueryParameter<T> where T : class
{
public T data { get; set; } //查询参数
public int pageNum { get; set; } = 1;
public int pageSize { get; set; } = 10;
public int Count { get; set; } = 0;
/// <summary>
/// 排序列
/// </summary>
public string sidx { get; set; } = "Id";
/// <summary>
/// 排序类型
/// </summary>
public string sord { get; set; } = "desc";
}
然后需要根据具体业务和查询创建一个返回的实体，用来接收查询结果，和返回给前端
这样，利用反射就可以获取到查询类的属性和类型，进而用来判断sql语句该怎么拼
PropertyInfo[] propertys = datadto.GetType().GetProperties();
foreach (var item in propertys)
{
var type = item.PropertyType.FullName;
var value = item?.GetValue(datadto);
var valueS = Convert.ToString(item?.GetValue(datadto));
if (string.IsNullOrWhiteSpace(valueS))
{
continue;
}
if (type.Contains("Int"))
{
if (Convert.ToInt32(value) <= 0)
{
continue;
}
param.Add(new SqlParameter("@" + item.Name, value));
strsql.Append(" and t." + item.Name + " = @" + item.Name);
}
else if (type.Contains("String"))
{
param.Add(new SqlParameter("@" + item.Name, "%" + value + "%"));
strsql.Append(" and t." + item.Name + " like @" + item.Name);
}
else if (type.Contains("Datetime"))
{
var date = ((DateTime)value).ToString("yyyy/MM/dd");
param.Add(new SqlParameter("@" + item.Name, date));
strsql.Append(" and t." + item.Name + " = @" + item.Name);
}
else
{
param.Add(new SqlParameter("@" + item.Name, value));
strsql.Append(" and t." + item.Name + " = @" + item.Name);
}
}
但是这样做太过简单，很多业务无法涵盖到，不符合实际需求，比如，string类型需要比大小，比如大于、小于，比如区间等查询条件都无法识别，所以还需要进行改进
首先需要新建一个ConditionsAttribute特性和两个枚举：
/// <summary>
/// </summary>
[AttributeUsage(AttributeTargets.All, Inherited = true, AllowMultiple = false)]
public class ConditionsAttribute: Attribute
{
public ConditionsAttribute()
{
Enable = true;
NotSelect = false;
ConditionsTypes = ConditionsType.STRING;
IsSplit = false;
SymbolAttributes = SymbolAttribute.EQUAL;
}
/// <summary>
///
/// </summary>
/// <param name="notSelect">是否永不查询，优先级最高</param>
public ConditionsAttribute(bool notSelect):this(true,notSelect, ConditionsType.STRING, SymbolAttribute.EQUAL,false,"")
{ }
/// <summary>
///
/// </summary>
/// <param name="notSelect">是否永不查询，优先级最高</param>
/// <param name="enable">是否启用，如果不启用特性，则默认使用字段的类型,优先级低于NotSelect</param>
public ConditionsAttribute(bool notSelect,bool enable) : this(enable, notSelect, ConditionsType.STRING, SymbolAttribute.EQUAL, false, "")
{ }
/// <summary>
///
/// </summary>
/// <param name="conditionsTypes">字段类型</param>
public ConditionsAttribute(ConditionsType conditionsTypes):this(true,false,conditionsTypes, SymbolAttribute.EQUAL, false,"")
{ }
/// <summary>
///
/// </summary>
/// <param name="conditionsTypes">字段类型</param>
public ConditionsAttribute(ConditionsType conditionsTypes, SymbolAttribute symbolAttribute, bool isSplit, string splitString) : this(true, false, conditionsTypes, symbolAttribute, isSplit, splitString)
{ }
/// <summary>
///
/// </summary>
/// <param name="symbolAttribute">字符串类型,只在为string时生效</param>
/// <param name="isSplit">是否字符分割，只在string或datetime类型下生效</param>
/// <param name="splitString">指定分割字符串，只在IsSplit为true时生效</param>
public ConditionsAttribute(SymbolAttribute symbolAttribute, bool isSplit, string splitString) : this(true, false, ConditionsType.STRING, symbolAttribute, isSplit, splitString)
{
}
/// <summary>
///
/// </summary>
/// <param name="enable">是否启用，如果不启用特性，则默认使用字段的类型,优先级低于NotSelect</param>
/// <param name="notSelect">是否永不查询，优先级最高</param>
/// <param name="conditionsTypes">字段类型</param>
/// <param name="symbolAttributes">字符串类型,只在为string时生效</param>
/// <param name="isSplit">是否字符分割，只在string或datetime类型下生效</param>
/// <param name="splitString">指定分割字符串，只在IsSplit为true时生效</param>
public ConditionsAttribute(bool enable,bool notSelect, ConditionsType conditionsTypes, SymbolAttribute symbolAttributes,bool isSplit,string splitString = "")
{
Enable = enable;
NotSelect = notSelect;
ConditionsTypes = conditionsTypes;
SymbolAttributes = symbolAttributes;
IsSplit = isSplit;
SplitString = splitString;
}
/// <summary>
/// 是否启用，如果不启用特性，则默认使用字段的类型,优先级低于NotSelect
/// </summary>
public bool Enable { get; set; }
/// <summary>
/// 是否永不查询，优先级最高
/// </summary>
public bool NotSelect { get; set; }
/// <summary>
/// 字段类型
/// </summary>
public ConditionsType ConditionsTypes { get; set; }
/// <summary>
/// 字符串运算符
/// </summary>
public SymbolAttribute SymbolAttributes { get; set; }
/// <summary>
/// 是否字符分割，只在string或datetime类型下生效
/// </summary>
public bool IsSplit { get; set; }
/// <summary>
/// 指定分割字符串，只在IsSplit为true时生效
/// </summary>
public string SplitString { get; set; }
}
public enum ConditionsType
{
/// <summary>
/// 字符
/// </summary>
INT,
/// <summary>
/// 字符串
/// </summary>
STRING,
/// <summary>
/// 时间
/// </summary>
DATETIME,
}
public enum SymbolAttribute
{
/// <summary>
/// 等于
/// </summary>
EQUAL,
/// <summary>
/// 包含
/// </summary>
CONTAILS,
/// <summary>
/// 从左包含
/// </summary>
STARTSWITH,
/// <summary>
/// 从右包含
/// </summary>
ENDSWITH,
/// <summary>
/// 大于
/// </summary>
GREATER,
/// <summary>
/// 小于
/// </summary>
LESS,
/// <summary>
/// 大于等于
/// </summary>
GREATEREQUAL,
/// <summary>
/// 小于等于
/// </summary>
LESSEQUAL,
/// <summary>
/// 区间
/// </summary>
INTERVAL
}
在查询类中将作为查询条件的字段给上特性
[Conditions]
public string BillNO { get; set; }
[Conditions(ConditionsType.DATETIME, SymbolAttribute.INTERVAL,true,",")]
public string CreationTime { get; set; }
这里因为我的时间查询字段前端传递的格式是"yyyy-MM-dd,yyyy-MM-dd"所以我给CreationTime的特性附上DATETIME类型，区间查询，有分隔符，分隔符为","
先创建一个SqlRepositorys仓储类，注入IDbContextProvider，然后开始拼接sql字符串
写一个SqlQueryAndParameter方法，使用两个泛型，T是返回的实体，也就是查询结果，D是查询参数
考虑到可能会存在查询参数类中没有定义，但是需要进行查询的情况，所以在参数中会有一个List
/// <summary>
/// 在查询条件的DTO中给字段加特性ConditionsAttribute
/// 没有ConditionsAttribute特性时，会根据字段自身的类型进行查询，int时按=，string时按like，datetime时搜索当天
/// 有特性时 跟根据特性一定的特性来进行搜索
/// NotSelect为永不查询，即使DTO中有值也不会查询
/// Enable是否启用，如果不启用的话，同没有特性的逻辑（已废弃）
/// symbolAttribute是判断字符运算符，根据运算符进行查询，目前有=，>,<,>=,<=,like，范围等，详情请看SymbolAttribute枚举
/// IsSplit为是否有分隔符，只有在运算符为范围时生效
/// SplitString是分隔符的字符，只有在IsSplit为true时生效
/// 另外，此方法还支持自定义写查询条件，可以在param中定义不在DTO中的查询条件，写在sql字符串中即可
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="D"></typeparam>
/// <param name="sql"></param>
/// <param name="queryParameter"></param>
/// <param name="pages"></param>
/// <param name="param"></param>
/// <returns></returns>
public IEnumerable<T> SqlQueryAndParameter<T, D>(string sql, QueryParameter<D> queryParameter, List<SqlParameter> param = null) where T : class, new() where D : class, new()
{
StringBuilder strsql = new StringBuilder();
strsql.Append(@"select * from (" + sql + ") t where 1=1");
if (param == null)
{
param = new List<SqlParameter>();
}
//var param = new List<SqlParameter>();
var datadto = queryParameter.data;
if (datadto != null)
{
PropertyInfo[] propertys = datadto.GetType().GetProperties();
foreach (var item in propertys)
{
var attribute = item.GetCustomAttribute<ConditionsAttribute>();
var type = item.PropertyType.FullName;
var value = item?.GetValue(datadto);
var valueS = Convert.ToString(item?.GetValue(datadto));
if (string.IsNullOrWhiteSpace(valueS))
{
continue;
}
if (attribute == null)
{
if (type.Contains("Int"))
{
if (Convert.ToInt32(value) <= 0)
{
continue;
}
param.Add(new SqlParameter("@" + item.Name, value));
strsql.Append(" and t." + item.Name + " = @" + item.Name);
}
else if (type.Contains("String"))
{
param.Add(new SqlParameter("@" + item.Name, "%" + value + "%"));
strsql.Append(" and t." + item.Name + " like @" + item.Name);
}
else if (type.Contains("Datetime"))
{
var date = ((DateTime)value).ToString("yyyy/MM/dd");
param.Add(new SqlParameter("@" + item.Name, date));
strsql.Append(" and t." + item.Name + " = @" + item.Name);
}
else
{
param.Add(new SqlParameter("@" + item.Name, value));
strsql.Append(" and t." + item.Name + " = @" + item.Name);
}
}
else
{
if (!attribute.NotSelect)
{
var conditionsType = attribute.ConditionsTypes;
var symbolAttribute = attribute.SymbolAttributes;
switch (symbolAttribute)
{
case SymbolAttribute.EQUAL:
param.Add(new SqlParameter("@" + item.Name, value));
strsql.Append(" and t." + item.Name + " = @" + item.Name);
break;
case SymbolAttribute.CONTAILS:
param.Add(new SqlParameter("@" + item.Name, "%" + value + "%"));
strsql.Append(" and t." + item.Name + " like @" + item.Name);
break;
case SymbolAttribute.STARTSWITH:
param.Add(new SqlParameter("@" + item.Name, "%" + value));
strsql.Append(" and t." + item.Name + " like @" + item.Name);
break;
case SymbolAttribute.ENDSWITH:
param.Add(new SqlParameter("@" + item.Name, value + "%"));
strsql.Append(" and t." + item.Name + " like @" + item.Name);
break;
case SymbolAttribute.GREATER:
param.Add(new SqlParameter("@" + item.Name, value));
strsql.Append(" and t." + item.Name + " > @" + item.Name);
break;
case SymbolAttribute.LESS:
param.Add(new SqlParameter("@" + item.Name, value));
strsql.Append(" and t." + item.Name + " < @" + item.Name);
break;
case SymbolAttribute.GREATEREQUAL:
param.Add(new SqlParameter("@" + item.Name, value));
strsql.Append(" and t." + item.Name + " >= @" + item.Name);
break;
case SymbolAttribute.LESSEQUAL:
param.Add(new SqlParameter("@" + item.Name, value));
strsql.Append(" and t." + item.Name + " <= @" + item.Name);
break;
case SymbolAttribute.INTERVAL:
if (attribute.IsSplit)
{
string[] timestring = valueS.Split(attribute.SplitString);
if (timestring != null)
{
if (timestring.Length == 1)
{
param.Add(new SqlParameter("@" + item.Name + "start", timestring[0]));
strsql.Append(" and (t." + item.Name + " >= @" + item.Name + "start )");
}
else if (timestring.Length == 2)
{
if (timestring[0] != "" && timestring[1] != "")
{
param.Add(new SqlParameter("@" + item.Name + "start", timestring[0]));
param.Add(new SqlParameter("@" + item.Name + "end", timestring[1]));
strsql.Append(" and (t." + item.Name + " >= @" + item.Name + "start and t." + item.Name + " <= @" + item.Name + "end)");
}
else if (timestring[0] != "" && timestring[1] == "")
{
param.Add(new SqlParameter("@" + item.Name + "start", timestring[0]));
strsql.Append(" and (t." + item.Name + " >= @" + item.Name + "start )");
}
else if (timestring[0] == "" && timestring[1] != "")
{
param.Add(new SqlParameter("@" + item.Name + "start", timestring[1]));
strsql.Append(" and ( t." + item.Name + " <= @" + item.Name + "end)");
}
}
}
}
break;
default:
switch (conditionsType)
{
case ConditionsType.INT:
param.Add(new SqlParameter("@" + item.Name, value));
strsql.Append(" and t." + item.Name + " = @" + item.Name);
break;
case ConditionsType.STRING:
param.Add(new SqlParameter("@" + item.Name, "%" + value + "%"));
strsql.Append(" and t." + item.Name + " like @" + item.Name);
break;
case ConditionsType.DATETIME:
var date = ((DateTime)value).ToString("yyyy/MM/dd");
param.Add(new SqlParameter("@" + item.Name, date));
strsql.Append(" and t." + item.Name + " = @" + item.Name);
break;
default:
param.Add(new SqlParameter("@" + item.Name, value));
strsql.Append(" and t." + item.Name + " = @" + item.Name);
break;
}
break;
}
}
else
{
continue;
}
}
}
}
queryParameter.Count = _dbContextProvider.GetDbContext().Set<T>().FromSqlRaw(strsql.ToString(), param.ToArray()).Count();
if (queryParameter.sidx == "")
{
queryParameter.sidx = "Id";
queryParameter.sord = "desc";
}
strsql.Append($@" order by {queryParameter.sidx} {queryParameter.sord} offset {(queryParameter.pageNum - 1) * queryParameter.pageSize} rows fetch next {queryParameter.pageSize} rows only");
return SqlQueryForParameter<T>(strsql.ToString(), param.ToArray());
}
public IEnumerable<T> SqlQueryForParameter<T>(string sql, params object[] parameters) where T : class, new()
{
return _dbContextProvider.GetDbContext().Set<T>().FromSqlRaw(sql, parameters);
}
最后是如何使用：
在service的查询方法中只需要，写好sql语句，然后把参数传过去就行了
public List<Table1List> GetListall(QueryParameter<Table1Dto> queryParameter, RequestPages page)
{
string sql = @"select * from table1"
return _sqlRepository.SqlQueryAndParameter<Table1List, Table1Dto>(sql, queryParameter,page).ToList();
}
Controller中也不需要多余的代码，使用定义的QueryParameter接收前端的传值（ToJson,Success是另外写的公共方法，转Json和返回成功信息，意思懂了就行）
public IActionResult GetListall([FromBody] QueryParameter<QueryStockinDto> queryParameter)
{
var list = table1App.GetListall(queryParameter, pages).ToJson();
return Success(list);
}
OK这样，查询和分页就封装好了，只需要写好T-sql语句就行了，也不再需要管查询条件之类的。
如果有更好的想法，欢迎交流，一起成长
原文：https://www.cnblogs.com/bugenniduobb/p/14212218.html