/// <summary>
/// 查询条件
/// </summary>
/// <typeparam name="T">查询条件类</typeparam>
/// <param name="obj">查询条件对象</param>
/// <param name="parames">返回SQL参数集合</param>
/// <returns></returns>
public static string GetWhere <T>(T obj, out List <SqlParameter> parames, string append = "", EWhereRelation appendRelation = EWhereRelation.AND) where T : class, new()
{
parames = new List <SqlParameter>();
if (obj == null)
{
return(string.Empty);
}
//获取查询条件
parames = GetSqlParameters(obj);
//获取WHERE SQL语句
var whereSql = GetWhereSql(obj);
//附加查询条件
if (append.IsNullOrWhiteSpace())
{
return(whereSql);
}
if (whereSql.IsNullOrWhiteSpace())
{
return(append.GetString(""));
}
return($"{append.GetString("")} {appendRelation.ToString()} {whereSql}");
}
/// <summary>
/// 拼接Where条件
/// </summary>
/// <param name="obj">对象</param>
/// <returns></returns>
private static string GetWhereSql(object obj)
{
if (obj == null)
{
return(string.Empty);
}
var props = obj.GetType().GetProperties();
if (props == null || props.Count() <= 0)
{
return(string.Empty);
}
var joinIndex = 0;
EWhereRelation lastRelation = EWhereRelation.AND;
var whereSql = new StringBuilder();
foreach (var p in props)
{
var pType = p.PropertyType;
var dbType = pType.ToDbType();
var value = p.GetValue(obj, null);
if (value == null)
{
continue;
}
var isList = pType.IsGenericType && pType.GetGenericTypeDefinition() == typeof(List <>);
var isNullable = pType.IsGenericType && pType.GetGenericTypeDefinition() == typeof(Nullable <>);
//获取属性上的特性（Attribute）
var attObj = p.GetCustomAttributes(typeof(SqlParameterAttribute), true).FirstOrDefault();
if (attObj == null)
{
continue;
}
var attr = (SqlParameterAttribute)attObj;
if (attr == null)
{
continue;
}
if (pType.IsGenericType && !isList && !isNullable)
{
continue;
}
//拼接条件关系 AND/OR
if (joinIndex >= 1)
{
//如果第一个查询条件是OR，那第二拼接无论是AND还是OR 都应该是OR
if (joinIndex == 1 && lastRelation == EWhereRelation.OR)
{
attr.ERelation = EWhereRelation.OR;
}
whereSql.Append($" {attr.ERelation.ToString()} ");
}
var fileName = p.Name;
if (!attr.FieldName.IsNullOrWhiteSpace())
{
fileName = attr.FieldName;
}
//获取查询条件 Value 默认是变量@属性名，如果是List 是拼接字符串(1,32,66) 并且 运算符 会用 IN
var val = $"@{p.Name}";
if (isList)
{
attr.EOperator = EWhereOperator.IN;
var listItemType = pType.GetGenericArguments()[0];
if (listItemType == null)
{
return(string.Empty);
}
if (listItemType == typeof(int))
{
val = $"({string.Join(",", value as List<int>)})";
}
else if (listItemType == typeof(string))
{
val = $"({string.Join(",", value as List<string>)})";
}
else
{
continue;
}
}
var tableAlias = attr.TableAlias.IsNullOrWhiteSpace() ? "" : $"{attr.TableAlias}.";
//拼接条件
whereSql.Append($" {tableAlias}{fileName} {attr.EOperator.GetEnumAttr().Desc} {val}");
//后续
joinIndex++;
}
return(whereSql.ToString());
}
