/// <summary>This method is for grouping all conditions that follow, if openGroup is set to false then it closes the group, if true it opens a new grouping
/// <example>example:
/// <code>
/// var plan = _GeneralDataRepository.GetWhere<Plan>(new List<GeneralDataParameter> {
/// new GeneralDataParameter("Date",date),
/// new GeneralDataParameter(true, DataParameterType.Or) <- this is the start of a group and will have an 'OR' before it opens
/// new GeneralDataParameter("Id",1)
/// new GeneralDataParameter("Id",2)
/// new GeneralDataParameter("Id",3)
/// new GeneralDataParameter(false) <- this is the end of the group
/// })?.FirstOrDefault();
/// </code>
/// gets records that match a date or Id 1, 2 or 3:
/// </example>
/// </summary>
public GeneralDataParameter(string field, object value, DataConditional conditional = DataConditional.EqualTo, DataParameterType multiConditionType = DataParameterType.And)
{
Field = field;
Value = value;
Conditional = conditional;
MultiConditionType = multiConditionType;
}
public IEnumerable <T> GetWhere <T>(Dictionary <string, object> parameters, DataConditional conditional = DataConditional.EqualTo) where T : IOrmGenerated
{
var sb = new StringBuilder();
sb.AppendLine("WHERE ");
var sqlParams = new List <SqlParameter>();
bool first = true;
int paramIndex = 0;
Sanitise(parameters);
foreach (var o in parameters)
{
var paramName = $"sqlParam_{paramIndex}";
if (conditional == DataConditional.NotNull || conditional == DataConditional.IsNull)
{
sb.AppendLine($"{(first ? "" : " AND ")} [{o.Key}] {conditional.GetDataConditional()} ");
}
else
{
sb.AppendLine($"{(first ? "" : " AND ")} [{o.Key}] {conditional.GetDataConditional()} @{paramName.Replace(" ", "")} ");
sqlParams.Add(new SqlParameter($"@{paramName.Replace(" ", "")}", o.Value));
}
first = false;
paramIndex++;
}
using (var rep = new SqlRepository(_Configuration, _Logger))
return(rep.ExecuteSql <T>(sb.ToString(), sqlParams) ?? new List <T>());
}
public IEnumerable <T> GetWhere <T>(string field, object value, DataConditional conditional = DataConditional.EqualTo) where T : IOrmGenerated
{
Sanitise(field);
using (var rep = new SqlRepository(_Configuration, _Logger))
if (conditional == DataConditional.NotNull || conditional == DataConditional.IsNull)
{
return(rep.ExecuteSql <T>($"WHERE [{field}] {conditional.GetDataConditional()}", new List <SqlParameter>()) ?? new List <T>());
}
else
{
return(rep.ExecuteSql <T>($"WHERE [{field}] {conditional.GetDataConditional()} @{field}", new List <SqlParameter> {
new SqlParameter($"@{field}", value)
}) ?? new List <T>());
}
}
public T GetSingle <T>(string field, object value, DataConditional conditional = DataConditional.EqualTo) where T : IOrmGenerated
{
using (var rep = new SqlRepository(_Configuration, _Logger))
{
Sanitise(field);
if (conditional == DataConditional.NotNull || conditional == DataConditional.IsNull)
{
var sql = $"WHERE [{field}] {conditional.GetDataConditional()}";
var ret = rep.ExecuteSqlSingle <T>(sql, new List <SqlParameter>());
return(ret);
}
else
{
var sql = $"WHERE [{field}] {conditional.GetDataConditional()} @{field}";
var ret = rep.ExecuteSqlSingle <T>(sql, new List <SqlParameter> {
new SqlParameter($"@{field}", value)
});
return(ret);
}
}
}
public static string GetDataConditional(this DataConditional cond)
{
var conditionals = new string[] { "=", ">", "<", ">=", "<=", "<>", "IS NOT NULL", "IS NULL", "IN" };
return(conditionals[(int)cond]);
}
