public override IAliasedCommandTypeDataOrder Lookup(TModel model) { Clause whereClause = Clause .New() .AddClause( IdProperty.GetValue(model), Clause.EqualStatementCallback(IdProperty.Name) ); var fields = typeof(TModel).GetProperties().Select(p => p.Name); var selectQuery = SelectQuery .New(TableName, fields, false) .Build(whereClause); return(new AliasedCommandTypeDataOrder(selectQuery, System.Data.CommandType.Text)); }
/// <summary> /// Used to create a query to update values on multiple tables. /// </summary> /// <param name="order">The information to create query.</param> /// <returns>The <see cref="IAliasedCommandTypeDataOrder"/> created.</returns> public static IAliasedCommandTypeDataOrder UpdateMultiple(IEnumerable <UpdateMultipleOrder> order) { order = order ?? new UpdateMultipleOrder[0]; if (order.Any() == false) { return(new AliasedCommandTypeDataOrder()); } string nullKey = "@null"; var parms = order .SelectMany(o => o.Values) .GroupBy(model => model.Item2) .Where(group => group.Key != null) .Select(group => Tuple.Create("@" + Get.SafeGuid(), group.Key)) .ToArray(); var queries = order .Select(model => { IEnumerable <Tuple <string, string> > updateParameters = model .Values .Select(value => Tuple.Create(value.Item1, parms.FirstOrDefault(p => p.Item2?.Equals(value.Item2) ?? false)?.Item1 ?? nullKey)); string query = $"UPDATE {model.TableName} SET{string.Join(",", updateParameters.Select(tuple => $"\r\n {tuple.Item1} = {tuple.Item2}"))}\r\nWHERE"; SimpleDataOrder clause = Clause.New().AddClause(model.Id.Item2, Clause.EqualStatementCallback(model.Id.Item1, model.TableName)).Build(); query += clause.Query; return(new AliasedDataOrder(query, clause.Parameters)); }).ToArray(); var aggregatedQuery = string.Join("\r\n\r\n", queries.Select(query => query.Query)); var aliases = queries.SelectMany(query => query.Aliases); var parameters = parms.Select(tuple => new KeyValuePair <string, object>(tuple.Item1, tuple.Item2)).Concat(queries.SelectMany(query => query.Parameters)).ToList(); if (aggregatedQuery.Contains(nullKey)) { parameters.Insert(0, new KeyValuePair <string, object>(nullKey, null)); } return(new AliasedCommandTypeDataOrder(SurroundQueryWithTransaction(aggregatedQuery), System.Data.CommandType.Text, parameters, aliases)); }
/// <summary> /// Produces a query to delete a record from a table. /// </summary> /// <param name="id">The id which should be deleted.</param> /// <param name="tableName">The name of the table which will be operated on.</param> /// <param name="idPropertyName">The name of the id field.</param> /// <returns><see cref="IAliasedCommandTypeDataOrder"/> containing the query, parents, and aliases.</returns> public static IAliasedCommandTypeDataOrder DeleteSingleId(int id, string tableName, string idPropertyName) { Clause whereClause = Clause.New().AddClause($"@id{Get.SafeGuid()}", id, Clause.EqualStatementCallback(idPropertyName)); return(new AliasedCommandTypeDataOrder(Get.DeleteQuery(tableName, whereClause), CommandType.Text)); }