// for RK with a rowversion column private static Procedure UpdateProc <T>(NodeMap map, ColumnSelector selector, Column[] valueParams, ColumnMap[] modifiedColumns, Column[] outputColumns, Column[] selectColumns) where T : DbRow { return(Designer.GetNewDesigner(Text.Method.UpdateGo, true, true) .ParamNodeColumns(map.ID, selector) // rowMatch predicate columns .ParamNodeColumns(modifiedColumns, "v") // values in .Select method .Param("@Table", Designer.Inliner.Table) // target table .Param("@Where", Designer.Inliner.Expression) // optimistic predicate .Param("@Columns", Designer.Inliner.Column) // column in .IntoColumns method .DesignTable <T>("@Output", true).EndDesign() .From("@Table") .Where("@Where") .Select(valueParams) .IntoColumns("@Columns") .Update().Output(outputColumns).Into("@Output") .From("@Output").Select(selectColumns) .Declare <int>("@r").Set(Designer.RowCount) .Return("@r") .EndProc()); }
private static SubResult ProcessPackage_UpdateRows <T>( Assembly client, IEnumerable <T> package, NodeMap map, ConnectBy connectBy, Nullable <bool> identityInsert) where T : DbRow { List <ParameterArgument> args = new List <ParameterArgument>(); args.Add(ViewConverter.ConvertDbRowData(package, false, true)); // #Rows args.Add(map.Name); // @Table args.Add(map.GetColumnsForUpdate(2)); // @SourceColumns args.Add(map.GetColumnsForUpdate(null)); // @TargetColumns args.Add(map.BuildSelfRelationWithOriginalValues(1, 2).E()); // @OnUpdate args.Add(map.BuildSelfRelation(1, 2).E()); // @OnSelect args.Add(map.GetColumns(1, 2)); // @AllColumns (with RowID) PassChainer cpass = _updateRowsProc.Pass(args.ToArray()); cpass.SetRootMap(map.ID); // important! var connectable = Reader.GetConnectable(client, cpass, connectBy); var result = connectable.Go(); var data = result.Table2.ToList(); var computedIndexes = map.SortedComputedColumns.Select(a => a.ID.ColumnZ).ToArray(); var hasComputedColumns = computedIndexes.Length > 0; var i = 0; var count = result.RowCount; var updated = result.Table1.ToList(); foreach (var row in package) { if (i >= count) { break; } if (((IRow)row).RowID == updated[i].RowID) { if (hasComputedColumns) { PropertyAccessor.SetValues(row, PropertyAccessor.GetValues(data[i], Text.Reserved.QtRowIDColumnName), computedIndexes); } row.SetStatus(DbRowStatus.Loaded); ++i; } } return(new SubResult(true, result.ReturnValue)); }
private static int _BuildDbRowsOuterSelect(bool rkOnly, bool withRowID, NodeMap nodeMap, List <ViewColumnInfo> columns, StringBuilder sql, int i) { foreach (var column in nodeMap.SortedColumns) { if (rkOnly && !column.IsRK) { continue; } var columnName = column.Name.Part1; columns.Add(new ViewColumnInfo(columnName, column.DataType, column.IsNullable)); if (i != 0) { sql.NewLineIndent(Text.Comma); } var argument = String.Format("{0}{1}", Text.ColumnShortName, i + 1); AppendColumn(sql, String.Format("CAST({0} AS {1})", Filter.DelimitNonAsterix(argument), column.DataType.Build()), columnName); ++i; if (withRowID && column.IsRK) { if (i != 0) { sql.NewLineIndent(Text.Comma); } var argumentOfOriginalValue = String.Format("{0}{1}", Text.ColumnShortName, i + 1); AppendColumn(sql, String.Format("CAST({0} AS {1})", Filter.DelimitNonAsterix(argumentOfOriginalValue), column.DataType.Build()), Common.ProvideOriginalColumnCRUD(columnName)); ++i; } } return(i); }
private static void _getColumnsWithoutRowversion(NodeMap map, out Column[] outputColumns, out Column[] selectColumns) { List <Column> ocols = new List <Column>(); List <Column> scols = new List <Column>(); foreach (var column in map.SortedColumns) { // skip rowversion if (column.DataType.DT.IsRowversion()) { continue; } scols.Add(String.Format(column.Name.Part1)); ocols.Add(String.Format("Inserted.{0}", column.Name.Part1)); } outputColumns = ocols.ToArray(); selectColumns = scols.ToArray(); }
private static SubResult ProcessPackage_DeleteRows <T>( Assembly client, IEnumerable <T> package, NodeMap map, ConnectBy connectBy, Nullable <bool> identityInsert) where T : DbRow { List <ParameterArgument> args = new List <ParameterArgument>(); args.Add(ViewConverter.ConvertDbRowData(package, false, true)); // #Rows args.Add(map.Name); // @Table args.Add(map.BuildSelfRelationWithOriginalValues(1, 2).E()); // @On PassChainer cpass = _deleteRowsProc.Pass(args.ToArray()); cpass.SetRootMap(map.ID); // important! var connectable = Reader.GetConnectable(client, cpass, connectBy); var result = connectable.Go(); var i = 0; var count = result.RowCount; var deleted = result.ToList(); foreach (var row in package) { if (i >= count) { break; } if (((IRow)row).RowID == deleted[i].RowID) { row.SetStatus(DbRowStatus.Deleted); ++i; } } return(new SubResult(true, result.ReturnValue)); }
private static void _BuildDbRowsValues(bool rkOnly, bool withRowID, NodeMap nodeMap, List <IPropertyAccessor> getters, StringBuilder sql, bool firstRow, DbRow row, object[] originalValues, ref int j, ref int gi) { int ov = 0; // original values index foreach (var column in nodeMap.SortedColumns) { if (rkOnly && !column.IsRK) { ++gi; continue; } if (j != 0) { sql.Append(Text.Comma); } var value = getters[gi].GetValue(row); value = column.TryCorrectMinWeakDatetime(value, row); AppendColumnValue(sql, value, j + 1, column.DataType, (firstRow && column.DataType.DT == DT.Sqlvariant)); ++j; ++gi; if (withRowID && column.IsRK) { if (j != 0) { sql.Append(Text.Comma); } AppendColumnValue(sql, originalValues[ov], j + 1, column.DataType, (firstRow && column.DataType.DT == DT.Sqlvariant)); ++j; ++ov; } } }
/// <summary> /// Adds a node to the mapping data. /// </summary> /// <param name="map">A node mapping object.</param> public static void AddNode(NodeMap map) { DbMapping.AddNode(map); }
internal static void AddNode(NodeMap map) { _nodes.Add(map); }
private static Connectable GetDeleteBody(Assembly client, DbRow row, bool forceMirroring, ConnectBy connectBy, string method, ref string name, out NodeMap map) { if (row == null) { throw new QueryTalkException("Crud", QueryTalkExceptionType.ArgumentNull, "row = null", method); } Crud.CheckTable(row, method); ColumnSelector selector = forceMirroring ? ColumnSelector.All : ColumnSelector.RK; List <ParameterArgument> args = new List <ParameterArgument>(); map = DbMapping.TryGetNodeMap(row); name = map.Name.Sql; if (forceMirroring) { if (!row.GetStatus().IsUpdatable()) { var arguments = map.BuildExceptionReport(row.GetOriginalRKValues(), row.GetOriginalRowversionValue()); throw new QueryTalkException("Crud.DeleteGo", QueryTalkExceptionType.InvalidMirroring, arguments, Text.Method.DeleteGo).SetObjectName(map.Name.Sql); } } object[] originalValues; if (selector == ColumnSelector.All) { if (map.HasRowversion) { originalValues = new object[] { row.GetOriginalRowversionValue() }; } else { originalValues = row.GetOriginalValues(); } } // RK selector else { originalValues = row.GetOriginalRKValues(); } for (int i = 0; i < originalValues.Length; ++i) { args.Add(new ParameterArgument(new Value(originalValues[i]))); } args.Add(map.Name); if (selector == ColumnSelector.All) { args.Add(new ParameterArgument(map.BuildOptimisticPredicate(originalValues, 1))); } else { args.Add(new ParameterArgument(map.BuildRKPredicate(originalValues, 1))); } var cpass = DeleteProc(row.NodeID, selector).Pass(args.ToArray()); cpass.SetRootMap(row.NodeID); // important! return(Reader.GetConnectable(client, row, cpass, connectBy)); }
private static void _BuildClassOuterSelect(Type type, ref Type clrType, ref QueryTalkException exception, List <ViewColumnInfo> columns, bool isEmpty, PropertyInfo[] properties, out List <IPropertyAccessor> getters, out int numberOfProperties, StringBuilder sqlOuter, StringBuilder sqlEmpty) { numberOfProperties = properties.Length; if (numberOfProperties == 0) { throw new QueryTalkException("ViewConverter.ToView<T>", QueryTalkExceptionType.InvalidDataClass, String.Format("data class = {0}", type)); } bool cached = Cache.PropertyAccessors.TryGetValue(type, out getters); if (!cached) { getters = new List <IPropertyAccessor>(); } NodeMap rowMap = null; bool isDbRow = type.IsDbRow(); if (isDbRow) { rowMap = DbMapping.GetNodeMap(type); if (rowMap.ID.Equals(DB3.Default)) { DbRow.ThrowInvalidDbRowException(type); } } // outer select: int i = 0; foreach (var property in properties) { string column; var clrTypeMatch = Mapping.CheckClrCompliance(property.PropertyType, out clrType, out exception); if (clrTypeMatch != Mapping.ClrTypeMatch.ClrMatch) { continue; } ViewColumnInfo columnInfo; if (isDbRow) { var rowColumn = rowMap.Columns.Where(a => a.ID.ColumnZ == i + 1).First(); column = rowColumn.Name.Part1; columnInfo = new ViewColumnInfo(column, rowColumn.DataType, rowColumn.IsNullable); columns.Add(columnInfo); } else { column = property.Name; columnInfo = new ViewColumnInfo(column, property.PropertyType, clrType); columns.Add(columnInfo); } if (i != 0) { sqlOuter.NewLineIndent(Text.Comma); sqlEmpty.Append(Text.Comma); } var dataType = Mapping.ProvideDataType(columnInfo.DataType, clrType); AppendOuterColumn(sqlOuter, dataType, i + 1, column); if (isEmpty) { AppendNullValueColumn(sqlEmpty, i + 1); } if (!cached) { getters.Add(PropertyAccessor.Create(type, property)); } ++i; } numberOfProperties = i; if (numberOfProperties == 0) { ThrowInvalidDataClassException(type); } if (!cached) { Cache.PropertyAccessors[type] = getters; } }
private static Connectable GetUpdateBody <T>(Assembly client, DbRow row, bool forceMirroring, ConnectBy connectBy, string method, ref string name, out NodeMap map) where T : DbRow { if (row == null) { throw new QueryTalkException("Crud", QueryTalkExceptionType.ArgumentNull, "row = null", method); } Crud.CheckTable(row, method); ColumnSelector selector = forceMirroring ? ColumnSelector.All : ColumnSelector.RK; List <ParameterArgument> args = new List <ParameterArgument>(); map = DbMapping.TryGetNodeMap(row); name = map.Name.Sql; if (forceMirroring) { if (!row.GetStatus().IsUpdatable()) { var arguments = map.BuildExceptionReport(row.GetOriginalRKValues(), row.GetOriginalRowversionValue()); throw new QueryTalkException("Crud.UpdateGo", QueryTalkExceptionType.InvalidMirroring, arguments, Text.Method.UpdateGo).SetObjectName(map.Name.Sql); } } object[] originalValues; if (selector == ColumnSelector.All) { if (map.HasRowversion) { originalValues = new object[] { row.GetOriginalRowversionValue() }; } else { originalValues = row.GetOriginalValues(); } } // RK selector else { originalValues = row.GetOriginalRKValues(); } for (int i = 0; i < originalValues.Length; ++i) { args.Add(new ParameterArgument(new Value(originalValues[i]))); } var currentValues = PropertyAccessor.GetValues(row); var updatableColumns = row.GetUpdatableColumns(forceMirroring); if (updatableColumns == null || updatableColumns.Length == 0) { return(null); } var valueParams = new Column[updatableColumns.Length]; int j = 0; foreach (var column in updatableColumns) { var value = new Value(currentValues[column.ID.ColumnZ - 1]); args.Add(new ParameterArgument(value)); valueParams[j] = String.Format("@v{0}", j + 1); ++j; } args.Add(map.Name); if (selector == ColumnSelector.All) { args.Add(new ParameterArgument(map.BuildOptimisticPredicate(originalValues, 1))); } else { args.Add(new ParameterArgument(map.BuildRKPredicate(originalValues, 1))); } int[] modified = updatableColumns.Select(a => a.ID.ColumnZ).ToArray(); args.Add(map.Columns .Where(a => modified.Contains(a.ID.ColumnZ)) .OrderBy(a => a.ID.ColumnZ) .Select(a => new Column(a.Name)) .ToArray()); Column[] outputColumns; Column[] selectColumns; _getColumnsWithoutRowversion(map, out outputColumns, out selectColumns); var cpass = UpdateProc <T>(map, selector, valueParams, updatableColumns, outputColumns, selectColumns).Pass(args.ToArray()); cpass.SetRootMap(row.NodeID); return(Reader.GetConnectable(client, row, cpass, connectBy)); }
private static SubResult ProcessPackage_InsertRows <T>(Assembly client, IEnumerable <T> package, NodeMap map, ConnectBy connectBy, Nullable <bool> identityInsert) where T : DbRow { if (package.Count() == 0) { return(new SubResult()); } PassChainer cpass; List <ParameterArgument> args = new List <ParameterArgument>(); var view = ViewConverter.ConvertDbRowData(package, false, true); var columns = map.GetInsertableColumns(true).Select(a => new Column(a.Name)).ToArray(); if (map.HasIdentity) { var identityPK = map.TryGetIdentityPK(); if (identityInsert == false) { var delta = ((IRow)package.First()).RowID; args.Add(view); args.Add(delta); // @Delta args.Add(identityPK.Name.Part1); // @Identity args.Add(new Column(Designer.IsNull( // ISNULL(MAX(@Identity), 0) Designer.Max(Designer.Identifier("1", identityPK.Name.Part1)), 0))); args.Add(new Column(String.Format("[1].[{0}] - @Delta + @LastID + 1", Text.Reserved.QtRowIDColumnName).E())); args.Add(map.Name); // @Table args.Add(columns); // @Columns args.Add(map.GetColumns(1)); // @AllColumns args.Add(map.BuildSelfRelation(1, 2).E()); // @On cpass = _insertRowsIntoIdentityProc.Pass(args.ToArray()); } else { args.Add(ViewConverter.ConvertDbRowData(package, false, true)); // #Rows args.Add(map.Name); // @Table args.Add(columns); // @Columns args.Add(map.GetColumns(1)); // @AllColumns args.Add(map.BuildSelfRelation(1, 2).E()); // @On cpass = _identityInsertRowsProc.Pass(args.ToArray()); } } // non-identity table else { // pass arguments args.Add(ViewConverter.ConvertDbRowData(package, false, true)); // #Rows args.Add(map.Name); // @Table args.Add(columns); // @Columns args.Add(map.GetColumns(1)); // @AllColumns args.Add(map.BuildSelfRelation(1, 2).E()); // @On cpass = _insertRowsProc.Pass(args.ToArray()); } // execute cpass.SetRootMap(map.ID); var connectable = Reader.GetConnectable(client, cpass, connectBy); var result = connectable.Go <T>(); var data = result.ToList(); var computedIndexes = map.SortedComputedColumns.Select(a => a.ID.ColumnZ).ToArray(); var j = 0; foreach (var row in package) { PropertyAccessor.SetValues(row, PropertyAccessor.GetValues(data[j++]), computedIndexes); row.SetStatus(DbRowStatus.Loaded); } // all rows inserted successfully or none return(new SubResult(true, result.RowCount)); }