public string GetDeleteQuery(Dictionary <string, object> parameters, QueryCondition queryCondition)
{
var stringBuilder = new StringBuilder();
stringBuilder.Append("DELETE FROM [");
stringBuilder.Append(_entityModel.TableName);
stringBuilder.AppendLine("]");
stringBuilder.Append("WHERE ");
queryCondition.GenerateConditionString(stringBuilder, parameters);
stringBuilder.AppendLine();
return(stringBuilder.ToString());
}
public string GetSelectQuery(Dictionary <string, object> parameters, QueryCondition queryCondition = null, QueryOptions queryOptions = null)
{
if (queryOptions?.SkipRowCount != null &&
queryOptions.OrderByOptions.Count == 0)
{
throw new ArgumentException("Missing order by clause while using skip rows feature.");
}
var selectQuery = GetSelectQuery();
if (queryOptions?.MaxRowCount != null &&
queryOptions.SkipRowCount == null)
{
selectQuery = string.Format(selectQuery, " TOP " + queryOptions.MaxRowCount);
}
else
{
selectQuery = string.Format(selectQuery, string.Empty);
}
var stringBuilder = new StringBuilder(selectQuery);
if (queryCondition != null)
{
stringBuilder.Append("WHERE ");
queryCondition.GenerateConditionString(stringBuilder, parameters);
stringBuilder.AppendLine();
}
if (queryOptions?.OrderByOptions?.Count > 0)
{
stringBuilder.Append("ORDER BY ");
stringBuilder.AppendLine(string.Join(", ", queryOptions.OrderByOptions.Select(o => $"{o.Column} {(o.SortDirection == SortDirection.Descending ? "DESC" : "ASC")}")));
}
if (queryOptions?.SkipRowCount != null)
{
stringBuilder.AppendLine($"OFFSET {queryOptions.SkipRowCount} ROWS");
if (queryOptions.MaxRowCount != null)
{
stringBuilder.AppendLine($"FETCH NEXT {queryOptions.MaxRowCount} ROWS ONLY");
}
}
return(stringBuilder.ToString());
}
public string GetSelectMaxQuery(string attributeName, Dictionary <string, object> parameters, QueryCondition queryCondition = null)
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"SELECT MAX([{_entityModel.TableName}].[{attributeName}])");
stringBuilder.Append("FROM [");
stringBuilder.Append(_entityModel.TableName);
stringBuilder.AppendLine("]");
if (queryCondition != null)
{
stringBuilder.Append("WHERE ");
queryCondition.GenerateConditionString(stringBuilder, parameters);
stringBuilder.AppendLine();
}
return(stringBuilder.ToString());
}
