// mapped bulk insert internal static Result <T> ExecuteBulkInsert <T>(Assembly client, IEnumerable <T> rows, ConnectBy connectBy) where T : DbRow { if (rows == null) { throw new QueryTalkException("Importer.ExecuteBulkInsert<T>", QueryTalkExceptionType.ArgumentNull, "rows = null", Text.Method.BulkInsertGo); } if (rows.Count() == 0) { return(new Result <T>(false, 0)); } Crud.CheckTable(rows.First(), Text.Method.BulkInsertGo); var first = rows.First(); var nodeID = ((DbRow)first).NodeID; var nodeMap = DbMapping.GetNodeMap(nodeID); var table = nodeMap.Name.ToString(); var data = rows.ToDataTable(); try { ConnectionKey connKey = null; if (connectBy != null) { connKey = ((IConnectable)connectBy).ConnectionKey; } var connectionString = ConnectionManager.InvokeConnectionFunc(client, connKey).ConnectionString; using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(cn)) { bulkCopy.DestinationTableName = table; foreach (var column in nodeMap.GetInsertableColumns(false)) { bulkCopy.ColumnMappings.Add(column.Name.Part1, column.Name.Part1); } bulkCopy.WriteToServer(data); } } return(new Result <T>(true, rows.Count())); } catch (QueryTalkException ex) { ex.Method = Text.Method.BulkInsertGo; throw; } }
internal static Result <T> InsertCascadeGo <T>(Assembly client, DbRow row, ConnectBy connectBy) where T : DbRow { var name = Text.NotAvailable; if (row == null) { throw new QueryTalkException("Crud", QueryTalkExceptionType.ArgumentNull, "row = null", Text.Method.InsertCascadeGo); } Crud.CheckTable(row, Text.Method.InsertCascadeGo); try { var map = DbMapping.TryGetNodeMap(row); name = map.Name.Sql; var scopeOption = GetTransactionScopeOption(client, map, connectBy); if (Transaction.Current != null) { scopeOption.IsolationLevel = Transaction.Current.IsolationLevel; } using (var scope = new TransactionScope(TransactionScopeOption.Required, scopeOption)) { Crud.InsertGo <T>(client, row, false, connectBy); ((INode)row).InsertGraph(client, connectBy); scope.Complete(); } return(new Result <T>(true, -1)); } catch (QueryTalkException) { throw; } catch (System.Exception ex) { throw Crud.ClrException(ex, name, Text.Method.InsertCascadeGo); } }
internal static Result <T> DeleteCascadeGo <T>(Assembly client, DbRow row, int maxLevels, ConnectBy connectBy) where T : DbRow { var name = Text.NotAvailable; try { if (row == null) { throw new QueryTalkException("Crud.GoDeleteCascade", QueryTalkExceptionType.ArgumentNull, "row = null", Text.Method.DeleteCascadeGo); } Crud.CheckTable(row, Text.Method.DeleteCascadeGo); CrudProcedure cached = row.NodeID.TryGetProcedure(CrudProcedureType.DeleteCascadeGo); Procedure proc; if (cached == null) { var map = DbMapping.TryGetNodeMap(row); var where = map.BuildRKPredicate(row.GetOriginalRKValues(), 0); Chainer workingProc = Designer.GetNewDesigner(Text.Method.DeleteCascadeGo, true, true) .ParamNodeColumns(row.NodeID, ColumnSelector.RK); // collect all linked nodes using GetChildren method List <NodeTree> tree = new List <NodeTree>(); ((ITable)((INode <T>)row).Node).LoadChildren(ref tree, 1, maxLevels); // loop through all leaves foreach (var node in tree.OrderByDescending(a => a.Level)) { workingProc = BuildQuery(tree, node, node, node.Level, workingProc, where, true); } // delete root row & create Procedure object proc = ((IFrom)workingProc) .From(map.Name).As(0) .Where(where) .Delete(Text.Zero) .EndProcInternal(); Cache.CrudProcedures.Add(new CrudProcedure(row.NodeID, CrudProcedureType.DeleteCascadeGo, proc)); } else { proc = cached.Procedure; } var rkValues = row.GetOriginalRKValues(); var args = new List <ParameterArgument>(); foreach (var value in rkValues) { args.Add(new ParameterArgument(new Value(value))); } var cpass = proc.Pass(args.ToArray()); cpass.SetRootMap(row.NodeID); var connectable = Reader.GetConnectable(client, row, cpass, connectBy); var result = connectable.Go(); return(new Result <T>(true, -1)); } catch (QueryTalkException) { throw; } catch (System.Exception ex) { throw Crud.ClrException(ex, name, Text.Method.DeleteCascadeGo); } }
// isSilent: // if true, then this method is used by other CRUD method (.UpdateGo/.InsertGo) which requires special treatment. internal static Result <T> ReloadGo <T>(Assembly client, DbRow row, bool forceMirroring, ConnectBy connectBy, bool isSilent = false) where T : DbRow { var name = Text.NotAvailable; try { ColumnSelector selector = forceMirroring ? ColumnSelector.All : ColumnSelector.RK; if (isSilent) { selector = ColumnSelector.RK; } if (row == null) { throw new QueryTalkException("Crud.GoReload", QueryTalkExceptionType.ArgumentNull, "row = null", Text.Method.ReloadGo); } Crud.CheckTable(row, Text.Method.ReloadGo); List <ParameterArgument> args = new List <ParameterArgument>(); var 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.ReloadGo", QueryTalkExceptionType.InvalidMirroring, arguments, Text.Method.ReloadGo).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 = ReloadProc(row.NodeID, selector).Pass(args.ToArray()); cpass.SetRootMap(row.NodeID); // important! Connectable connectable = Reader.GetConnectable(client, row, cpass, connectBy); var result = connectable.Go <T>(); // success if (result.RowCount > 0) { PropertyAccessor.SetValues(row, result.First().GetOriginalValues()); row.SetStatus(DbRowStatus.Loaded); return(new Result <T>(true, 0)); } // not found else { var arguments = map.BuildExceptionReport(row.GetOriginalRKValues(), row.GetOriginalRowversionValue()); if (!isSilent) { if (forceMirroring) { throw new QueryTalkException("Crud.ReloadGo", QueryTalkExceptionType.ConcurrencyViolation, arguments, Text.Method.ReloadGo).SetObjectName(map.Name.Sql); } else { throw new QueryTalkException("Crud.ReloadGo", QueryTalkExceptionType.ReloadFailed, arguments, Text.Method.ReloadGo).SetObjectName(map.Name.Sql); } } else { row.SetStatus(DbRowStatus.Faulted); return(new Result <T>(true, 0)); } } } catch (QueryTalkException ex) { Loader.TryThrowInvalidSqlOperationException(ex, name, Text.Method.ReloadGo); throw; } catch (System.Exception ex) { var ex2 = Crud.ClrException(ex, name, Text.Method.ReloadGo); Loader.TryThrowInvalidSqlOperationException(ex2, name); throw ex2; } }
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 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)); }
internal static Async <Result <T> > UpdateGoAsync <T>(Assembly client, DbRow row, bool forceMirroring, ConnectBy connectBy, Action <Result <T> > onCompleted) where T : DbRow { var name = Text.NotAvailable; NodeMap map; Crud.CheckTable(row, Text.Method.UpdateGo); try { var connectable = GetUpdateBody <T>(client, row, forceMirroring, connectBy, Text.Method.UpdateGo, ref name, out map); // no columns to modify? if (connectable == null) { return(Async <Result <T> > .CreateDefault <T>()); } return(connectable.GoAsync <T>(result => { try { // success if (result.ReturnValue == 1) { PropertyAccessor.SetValues(row, PropertyAccessor.GetValues(result.First())); row.SetStatus(DbRowStatus.Loaded); } // concurrency violation else { if (forceMirroring) { var arguments = map.BuildExceptionReport(row.GetOriginalRKValues(), row.GetOriginalRowversionValue()); throw new QueryTalkException("Crud.UpdateGoAsync", QueryTalkExceptionType.ConcurrencyViolation, arguments, Text.Method.UpdateGoAsync).SetObjectName(map.Name.Sql); } } result.FinalizeCrud(); onCompleted?.Invoke(result); } catch (QueryTalkException) { throw; } catch (System.Exception ex) { throw Crud.ClrException(ex, name, Text.Method.InsertGoAsync); } })); } catch (QueryTalkException ex) { Loader.TryThrowInvalidSqlOperationException(ex, name, Text.Method.UpdateGo); throw; } catch (System.Exception ex) { var ex2 = Crud.ClrException(ex, name, Text.Method.UpdateGo); Loader.TryThrowInvalidSqlOperationException(ex2, name); throw ex2; } }
private static Connectable GetInsertBody <T>(Assembly client, DbRow row, bool identityInsert, ConnectBy connectBy, string method, ref string name) where T : DbRow { if (row == null) { throw new QueryTalkException("Crud", QueryTalkExceptionType.ArgumentNull, "row = null", method); } Crud.CheckTable(row, method); List <ParameterArgument> args = new List <ParameterArgument>(); var map = DbMapping.TryGetNodeMap(row); name = map.Name.Sql; if (!map.HasIdentity) { identityInsert = false; } var insertableColumns = map.GetInsertableColumns(row, identityInsert); Column[] outputColumns; Column[] selectColumns; _getColumnsWithoutRowversion(map, out outputColumns, out selectColumns); PassChainer cpass; if (insertableColumns.Length > 0) { var currentValues = PropertyAccessor.GetValues(row); var valueParams = new Column[insertableColumns.Length]; int j = 0; foreach (var column in insertableColumns) { var value = new Value(currentValues[column.ID.ColumnZ - 1]); value.Original = column.TryCorrectMinWeakDatetime(value.Original, row); args.Add(new ParameterArgument(value)); valueParams[j] = String.Format("@v{0}", j + 1); ++j; } args.Add(map.Name); args.Add(insertableColumns .Select(a => new Column(a.Name)) .ToArray()); cpass = InsertProc <T>(insertableColumns, valueParams, identityInsert, outputColumns, selectColumns) .Pass(args.ToArray()); } // default values: else { cpass = InsertProcDefaultValues <T>(outputColumns, selectColumns).Pass(map.Name); } cpass.SetRootMap(row.NodeID); return(Reader.GetConnectable(client, row, cpass, connectBy)); }
internal static SubResult GoMirroring <T>( Assembly client, IEnumerable <T> rows, Func <Assembly, T, bool, ConnectBy, SubResult> processMethod, ConnectBy connectBy) where T : DbRow { var name = Text.NotAvailable; try { #region Check var subResult = new SubResult(); if (rows == null) { throw new QueryTalkException("Crud.GoMirroring", QueryTalkExceptionType.ArgumentNull, "rows = null", Text.Method.CrudRowsGo); } if (rows.Count() == 0) { return(subResult); } // non-null rows var rows2 = new List <T>(); foreach (var row in rows) { if (row != null) { rows2.Add(row); } } if (rows2.Count() == 0) { return(subResult); } Crud.CheckTable(rows.First(), Text.Method.CrudRowsGo); #endregion var map = DbMapping.TryGetNodeMap(rows2.First()); name = map.Name.Sql; var scopeOption = GetTransactionScopeOption(client, map, connectBy); // check if ambient transaction exists and use its isolation level if (Transaction.Current != null) { scopeOption.IsolationLevel = Transaction.Current.IsolationLevel; } using (var scope = new TransactionScope(TransactionScopeOption.Required, scopeOption)) { // loop foreach (var row in rows) { var sub = processMethod(client, row, true, connectBy); if (sub.Executed) { subResult .SetExecuted() .AddAffectedCount(sub.AffectedCount); } } scope.Complete(); } return(subResult); } catch (QueryTalkException) { throw; } catch (System.Exception ex) { throw Crud.ClrException(ex, name, Text.NotAvailable); } }
internal static SubResult GoNonMirroring <T>( Assembly client, IEnumerable <T> rows, Func <Assembly, IEnumerable <T>, NodeMap, ConnectBy, Nullable <bool>, SubResult> processMethod, ConnectBy connectBy, Nullable <bool> identityInsert) where T : DbRow { var name = Text.NotAvailable; try { #region Check var subResult = new SubResult(); if (rows == null) { throw new QueryTalkException("Crud.GoNonMirroring", QueryTalkExceptionType.ArgumentNull, "rows = null", Text.Method.CrudRowsGo); } if (rows.Count() == 0) { return(subResult); } Crud.CheckTable(rows.First(), Text.Method.CrudRowsGo); // non-null rows var rows2 = new List <T>(); foreach (var row in rows) { if (row != null) { rows2.Add(row); } } if (rows2.Count() == 0) { return(subResult); } #endregion var map = DbMapping.TryGetNodeMap(rows2.First()); name = map.Name.Sql; if (!map.HasIdentity) { identityInsert = false; } // set RowID var i = 0; foreach (var row in rows2) { ((IRow)row).RowID = i++; } var packageIndex = 0; var scopeOption = GetTransactionScopeOption(client, map, connectBy); // check if ambient transaction exists and use its isolation level if (Transaction.Current != null) { scopeOption.IsolationLevel = Transaction.Current.IsolationLevel; } using (var scope = new TransactionScope(TransactionScopeOption.Required, scopeOption)) { var count = rows2.Count(); // entire volume of data to be processed var ix = 0; // pointer to the first row in the package do { var package = rows2.Where(row => ((IRow)row).RowID >= ix && ((IRow)row).RowID < ix + Crud.PackageSize).ToList(); var sub = processMethod(client, package, map, connectBy, identityInsert); if (sub.Executed) { subResult .SetExecuted() .AddAffectedCount(sub.AffectedCount); } ix += Crud.PackageSize; ++packageIndex; } while (ix < count); scope.Complete(); } return(subResult); } catch (QueryTalkException) { throw; } catch (System.Exception ex) { throw Crud.ClrException(ex, name, Text.NotAvailable); } }