private async Task FetchAllAsync(DataObject dobj, SqlCommand command)
{
var count = dobj.Get <object>("count");
if (count == null)
{
PrepareFetchAllQuery(dobj, command);
var result = new List <Dictionary <string, object> >();
using (var dr = await command.ExecuteReaderAsync().ConfigureAwait(false))
{
while (await dr.ReadAsync().ConfigureAwait(false))
{
var row = new Dictionary <string, object>();
for (var ct = 0; ct < dr.FieldCount; ct++)
{
row.Add(dr.GetName(ct).ToCamelCase(), dr.IsDBNull(ct) ? null : dr.GetValue(ct));
}
result.Add(row);
}
}
dobj["items"] = result;
dobj.ResultStatusCode = result.Count > 0 ? HttpStatusCode.OK : HttpStatusCode.NoContent;
}
else
{
PrepareCountQuery(dobj, command);
dobj["totalRows"] = await command.ExecuteScalarAsync().ConfigureAwait(false);
}
}
private void ParseSelect(DataObject dobj)
{
var select = dobj.Get <string>("select");
if (select == null)
{
return;
}
var fields = select.Split(',');
var fieldList = new List <string>();
foreach (var field in fields)
{
var fieldName = field.Trim();
if (ValidateFieldName(fieldName) == false)
{
throw new ArgumentException($"Invalid Select clause: {fieldName}");
}
fieldList.Add($"[{fieldName}]");
}
_forcedSelectClause = string.Join(", ", fieldList);
}
private async Task FetchByIdAsync(DataObject dobj, SqlCommand command)
{
var id = dobj.Get <object>("id");
if (id == null)
{
throw new ArgumentNullException(nameof(id), "Required argument id was not provided");
}
PrepareFetchByIdQuery(dobj, command);
using (var dr = await command.ExecuteReaderAsync().ConfigureAwait(false))
{
if (await dr.ReadAsync().ConfigureAwait(false))
{
var row = new Dictionary <string, object>();
for (var ct = 0; ct < dr.FieldCount; ct++)
{
row.Add(dr.GetName(ct).ToCamelCase(), dr.IsDBNull(ct) ? null : dr.GetValue(ct));
}
dobj["item"] = row;
}
else
{
dobj["item"] = null;
dobj.ResultStatusCode = HttpStatusCode.NotFound;
}
}
}
private async Task FetchPageAsync(DataObject dobj, SqlCommand command)
{
var pageSize = dobj.Get <int>("pageSize");
var pageNumber = dobj.Get <int>("pageNumber");
if (pageSize < 2)
{
throw new ArgumentOutOfRangeException($"pageSize should be >= 2, but it is {pageSize}");
}
if (pageNumber < 1)
{
throw new ArgumentOutOfRangeException($"pageNumber should be >= 1, but it is {pageNumber}");
}
PrepareFetchPageQuery(dobj, command, pageSize, pageNumber);
using (var dr = await command.ExecuteReaderAsync().ConfigureAwait(false))
{
await dr.ReadAsync().ConfigureAwait(false);
var totalRows = dr.GetInt32(0);
dobj["totalRows"] = totalRows;
dobj["totalPages"] = Convert.ToInt32(Math.Ceiling(Convert.ToSingle(totalRows) / pageSize));
await dr.NextResultAsync().ConfigureAwait(false);
var result = new List <Dictionary <string, object> >();
while (await dr.ReadAsync().ConfigureAwait(false))
{
var row = new Dictionary <string, object>();
for (var ct = 0; ct < dr.FieldCount; ct++)
{
row.Add(dr.GetName(ct).ToCamelCase(), dr.IsDBNull(ct) ? null : dr.GetValue(ct));
}
result.Add(row);
}
dobj["items"] = result;
dobj.ResultStatusCode = result.Count > 0 ? HttpStatusCode.OK : HttpStatusCode.NoContent;
}
}
private async Task DeleteItemAsync(DataObject dobj, SqlCommand command)
{
var id = dobj.Get <object>("id");
if (id == null)
{
throw new ArgumentNullException(nameof(id), "Required argument id was not provided");
}
PrepareDeleteItemQuery(dobj, command);
dobj["affectedRows"] = await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
private async Task UpdateItemAsync(DataObject dobj, SqlCommand command, bool replace)
{
var id = dobj.Get <object>("id");
if (id == null)
{
throw new ArgumentNullException(nameof(id), "Required argument id was not provided");
}
if (dobj.Count == 1)
{
throw new ArgumentException("Cannot update entity without empty field information");
}
var columns = await GetTableColumnsAsync(command).ConfigureAwait(false);
PrepareUpdateItemQuery(dobj, command, columns, replace);
dobj["affectedRows"] = await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
private void ParseOrderBy(DataObject dobj)
{
var orderBy = dobj.Get <string>("orderBy");
if (orderBy == null)
{
return;
}
var clauses = orderBy.Split(',');
foreach (var clause in clauses)
{
var parts = clause.Trim().Replace(" ", " ").Split(' ');
if (parts.Length == 1)
{
parts = new[] { parts[0], "asc" };
}
else if (parts.Length != 2)
{
throw new ArgumentException($"Invalid Order By clause: {clause}");
}
if (parts[1] != "asc" && parts[1] != "desc")
{
throw new ArgumentException($"Invalid Order By clause: {clause}");
}
if (ValidateFieldName(parts[0]) == false)
{
throw new ArgumentException($"Invalid Order By clause: {clause}");
}
_orderByClauses.Add($"[{parts[0]}] {parts[1]}");
}
}
private void ParseWhere(DataObject dobj)
{
var filter = dobj.Get <string>("filter");
var clause = new List <string>();
if (filter == null)
{
return;
}
filter = filter.Trim();
while (filter.Length > 0)
{
var fieldName = GetNextToken(ref filter);
if (ValidateFieldName(fieldName) == false)
{
throw new ArgumentException($"Invalid field name: {fieldName}");
}
clause.Add($"[{fieldName}]");
var op = GetNextToken(ref filter).ToLower();
switch (op)
{
case "eq":
clause.Add("=");
break;
case "lt":
clause.Add("<");
break;
case "gt":
clause.Add(">");
break;
case "le":
clause.Add("<=");
break;
case "ge":
clause.Add(">=");
break;
case "ne":
clause.Add("<>");
break;
default:
throw new ArgumentException($"Invalid operator {op}");
}
var value = GetNextToken(ref filter);
if (value == "")
{
throw new ArgumentException($"Invalid value for {fieldName} {op}");
}
if (value.StartsWith("'") && value.EndsWith("'"))
{
clause.Add(value);
}
else
{
decimal dValue;
if (decimal.TryParse(value, NumberStyles.Float, _enUs, out dValue) == false)
{
throw new ArgumentOutOfRangeException($"Value {value} is not a numeric or string value");
}
clause.Add(dValue.ToString(_enUs));
}
var continuation = GetNextToken(ref filter);
switch (continuation.ToLower())
{
case "":
break;
case "and":
clause.Add("AND");
break;
case "or":
clause.Add("OR");
break;
default:
throw new ArgumentException($"Unexpected token {continuation}");
}
}
_parsedWhere = string.Join(" ", clause);
}
