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); }