// Loops through object properties, checks if column has been added, adds as sql parameter. Used for the UpdateQuery method.
public static void AddSqlParamsForUpdateQuery <T>(List <SqlParameter> sqlParameters, HashSet <string> columns, T item)
{
PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (var column in columns.ToList())
{
for (int i = 0; i < props.Length; i++)
{
if (props[i].Name == column && item != null && CheckForValidDataType(props[i].PropertyType, throwIfInvalid: true))
{
DbType sqlType = SqlTypeMap.GetSqlTypeFromNetType(props[i].PropertyType);
SqlParameter param = new SqlParameter($"@{column}", sqlType);
param.Value = props[i].GetValue(item, null);
sqlParameters.Add(param);
}
}
}
}
/// <summary>
///
/// </summary>
/// <param name="leftName"></param>
/// <param name="value"></param>
/// <param name="valueType"></param>
/// <param name="expressionType"></param>
/// <param name="predicateList"></param>
/// <param name="sqlParamsList"></param>
/// <param name="sortOrder"></param>
/// <param name="appendParam"></param>
/// <param name="predicateType"></param>
internal static void BuildCondition(string leftName, string value, Type valueType, ExpressionType expressionType,
List <Condition> predicateList, List <SqlParameter> sqlParamsList, PredicateType predicateType, int sortOrder, string appendParam)
{
Condition condition = new Condition()
{
Expression = expressionType,
LeftName = leftName,
ValueType = valueType,
Value = value,
PredicateType = predicateType,
SortOrder = sortOrder
};
predicateList.Add(condition);
DbType sqlType = SqlTypeMap.GetSqlTypeFromNetType(condition.ValueType);
string paramName = appendParam != null ? leftName + appendParam + sortOrder : leftName;
SqlParameter param = new SqlParameter($"@{paramName}", sqlType);
param.Value = condition.Value;
sqlParamsList.Add(param);
}
/// <summary>
///
/// </summary>
/// <param name="predicate"></param>
/// <param name="predicateType"></param>
/// <param name="predicateList"></param>
/// <param name="sqlParamsList"></param>
/// <param name="sortOrder"></param>
/// <param name="appendParam"></param>
internal static void AddPredicate <T>(Expression <Func <T, bool> > predicate, PredicateType predicateType, List <Condition> predicateList,
List <SqlParameter> sqlParamsList, int sortOrder, string appendParam)
{
string leftName;
string value;
Condition condition;
BinaryExpression binaryBody = predicate.Body as BinaryExpression;
if (binaryBody == null)
{
throw new SqlBulkToolsException($"Expression not supported for {GetPredicateMethodName(predicateType)}");
}
// For expression types Equal and NotEqual, it's possible for user to pass null value. This handles the null use case.
// SqlParameter is not added when comparison to null value is used.
switch (predicate.Body.NodeType)
{
case ExpressionType.NotEqual:
{
leftName = ((MemberExpression)binaryBody.Left).Member.Name;
value = Expression.Lambda(binaryBody.Right).Compile().DynamicInvoke()?.ToString();
if (value != null)
{
condition = new Condition()
{
Expression = ExpressionType.NotEqual,
LeftName = leftName,
ValueType = binaryBody.Right.Type,
Value = value,
PredicateType = predicateType,
SortOrder = sortOrder
};
DbType sqlType = SqlTypeMap.GetSqlTypeFromNetType(condition.ValueType);
string paramName = appendParam != null ? leftName + appendParam + sortOrder : leftName;
SqlParameter param = new SqlParameter($"@{paramName}", sqlType);
param.Value = condition.Value;
sqlParamsList.Add(param);
}
else
{
condition = new Condition()
{
Expression = ExpressionType.NotEqual,
LeftName = leftName,
Value = "NULL",
PredicateType = predicateType,
SortOrder = sortOrder
};
}
predicateList.Add(condition);
break;
}
// For expression types Equal and NotEqual, it's possible for user to pass null value. This handles the null use case.
// SqlParameter is not added when comparison to null value is used.
case ExpressionType.Equal:
{
leftName = ((MemberExpression)binaryBody.Left).Member.Name;
value = Expression.Lambda(binaryBody.Right).Compile().DynamicInvoke()?.ToString();
if (value != null)
{
condition = new Condition()
{
Expression = ExpressionType.Equal,
LeftName = leftName,
ValueType = binaryBody.Right.Type,
Value = value,
PredicateType = predicateType,
SortOrder = sortOrder
};
DbType sqlType = SqlTypeMap.GetSqlTypeFromNetType(condition.ValueType);
string paramName = appendParam != null ? leftName + appendParam + sortOrder : leftName;
SqlParameter param = new SqlParameter($"@{paramName}", sqlType);
param.Value = condition.Value;
sqlParamsList.Add(param);
}
else
{
condition = new Condition()
{
Expression = ExpressionType.Equal,
LeftName = leftName,
Value = "NULL",
PredicateType = predicateType,
SortOrder = sortOrder
};
}
predicateList.Add(condition);
break;
}
case ExpressionType.LessThan:
{
leftName = ((MemberExpression)binaryBody.Left).Member.Name;
value = Expression.Lambda(binaryBody.Right).Compile().DynamicInvoke()?.ToString();
BuildCondition(leftName, value, binaryBody.Right.Type, ExpressionType.LessThan, predicateList, sqlParamsList,
predicateType, sortOrder, appendParam);
break;
}
case ExpressionType.LessThanOrEqual:
{
leftName = ((MemberExpression)binaryBody.Left).Member.Name;
value = Expression.Lambda(binaryBody.Right).Compile().DynamicInvoke()?.ToString();
BuildCondition(leftName, value, binaryBody.Right.Type, ExpressionType.LessThanOrEqual, predicateList,
sqlParamsList, predicateType, sortOrder, appendParam);
break;
}
case ExpressionType.GreaterThan:
{
leftName = ((MemberExpression)binaryBody.Left).Member.Name;
value = Expression.Lambda(binaryBody.Right).Compile().DynamicInvoke()?.ToString();
BuildCondition(leftName, value, binaryBody.Right.Type, ExpressionType.GreaterThan, predicateList,
sqlParamsList, predicateType, sortOrder, appendParam);
break;
}
case ExpressionType.GreaterThanOrEqual:
{
leftName = ((MemberExpression)binaryBody.Left).Member.Name;
value = Expression.Lambda(binaryBody.Right).Compile().DynamicInvoke()?.ToString();
BuildCondition(leftName, value, binaryBody.Right.Type, ExpressionType.GreaterThanOrEqual, predicateList,
sqlParamsList, predicateType, sortOrder, appendParam);
break;
}
case ExpressionType.AndAlso:
{
throw new SqlBulkToolsException($"And && expression not supported for {GetPredicateMethodName(predicateType)}. " +
$"Try chaining predicates instead e.g. {GetPredicateMethodName(predicateType)}." +
$"{GetPredicateMethodName(predicateType)}");
}
case ExpressionType.OrElse:
{
throw new SqlBulkToolsException($"Or || expression not supported for {GetPredicateMethodName(predicateType)}.");
}
default:
{
throw new SqlBulkToolsException($"Expression used in {GetPredicateMethodName(predicateType)} not supported. " +
$"Only == != < <= > >= expressions are accepted.");
}
}
}