// This method is used to execute the raw SQL. It can execute many batches separated by the GO command. internal static void Go(Assembly client, string sql, ConnectBy connectBy) { if (String.IsNullOrWhiteSpace(sql)) { return; } var connectionData = ConnectionManager.GetConnectionData(client, connectBy, null, null); var batches = GetBatches(sql); using (SqlConnection cn = new SqlConnection(connectionData.ConnectionString)) { cn.Open(); foreach (var batch in batches) { if (String.IsNullOrWhiteSpace(batch)) { continue; } var batchSql = Text.GenerateSql(200) .Append(Text.Free.QueryTalkCode) .AppendLine(batch); using (var cmd = new SqlCommand(batchSql.ToString(), cn)) { cmd.CommandTimeout = connectionData.CommandTimeout; cmd.ExecuteNonQuery(); } } } }
internal static Connectable GetConnectable(Assembly client, Chainer prev, ConnectBy connectBy) { if (prev is Connectable) { return((Connectable)prev); } return(GetConnectable(client, prev, ConnectionManager.GetConnectionData(client, connectBy, null, prev))); }
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)); }
// common method for processing the connection data internal static ConnectionData GetConnectionData(Assembly client, ConnectBy connectBy, // explicitly given query key DbNode node, // database object bound to database map key (very likely to be given) Chainer chainObject) // chain object that is very likely to have no connection data (except when .ConnectBy is called inside the compilable) { ConnectionKey connectionKey = null; if (connectBy != null) { connectionKey = ((IConnectable)connectBy).ConnectionKey; } else if (node != null) { connectionKey = ((IConnectable)node).ConnectionKey; if (connectionKey != null) { ((IConnectable)node).ResetConnectionKey(); // always clean it up after the getter provide the ConnectionKey } } else if (chainObject != null) { var root = chainObject.GetRoot(); // root: in cases when connection data is stored into compilable object // example: // s.River.Select().ConnectBy().Go() if (root.ConnectionKey != null) { connectionKey = root.ConnectionKey; root.ConnectionKey = null; } // root.Node: in cases when the compilable is based on the mapped object // example: // s.River.Select().Go() else if (root.Node != null) { connectionKey = ((IConnectable)root.Node).ConnectionKey; if (connectionKey != null) { ((IConnectable)root.Node).ResetConnectionKey(); // always clean it up after the getter provide the ConnectionKey } } } return(ConnectionManager.InvokeConnectionFunc(client, connectionKey)); }
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); } }
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)); }
internal static Result <T> DeleteGo <T>(Assembly client, DbRow row, bool forceMirroring, ConnectBy connectBy) where T : DbRow { var name = Text.NotAvailable; NodeMap map; try { var connectable = GetDeleteBody(client, row, forceMirroring, connectBy, Text.Method.DeleteGo, ref name, out map); var result = connectable.Go(); if (result.ReturnValue == 1) { row.SetStatus(DbRowStatus.Deleted); return(new Result <T>(true, 1)); } // concurrency violation: else { if (forceMirroring) { var arguments = map.BuildExceptionReport(row.GetOriginalRKValues(), row.GetOriginalRowversionValue()); throw new QueryTalkException("Crud.DeleteGo", QueryTalkExceptionType.ConcurrencyViolation, arguments, Text.Method.DeleteGo).SetObjectName(map.Name.Sql); } else { return(new Result <T>(true, 0)); } } } catch (QueryTalkException ex) { Loader.TryThrowInvalidSqlOperationException(ex, name, Text.Method.DeleteGo); throw; } catch (System.Exception ex) { var ex2 = Crud.ClrException(ex, name, Text.Method.DeleteGo); 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)); }
internal static SubResult DeleteRowGo <T>(Assembly client, DbRow row, bool forceMirroring, ConnectBy connectBy) where T : DbRow { var result = DeleteGo <T>(client, row, forceMirroring, connectBy); return(new SubResult(result.Executed, result.AffectedCount)); }
internal static TransactionOptions GetTransactionScopeOption(Assembly client, Map map, ConnectBy connectBy) { var tempNode = new InternalNode(map.ID); var connectionData = ConnectionManager.GetConnectionData(client, connectBy, tempNode, null); var scopeOption = new TransactionOptions(); scopeOption.Timeout = TimeSpan.FromSeconds(connectionData.CommandTimeout); scopeOption.IsolationLevel = connectionData.MassDataOperationIsolationLevel.ToSystemEnum(); return(scopeOption); }
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)); }
// 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; } }
// 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; } }
internal static Result <T> UpdateGo <T>(Assembly client, DbRow row, bool forceMirroring, ConnectBy connectBy) where T : DbRow { var name = Text.NotAvailable; NodeMap map; try { var connectable = GetUpdateBody <T>(client, row, forceMirroring, connectBy, Text.Method.UpdateGo, ref name, out map); // no columns to modify? if (connectable == null) { return(new Result <T>(false, 0)); } var result = connectable.Go <T>(); // success if (result.ReturnValue == 1) { PropertyAccessor.SetValues(row, PropertyAccessor.GetValues(result.First())); // has rowversion & it is not RK if (row.HasNonRKRowversion) { row.SetStatus(DbRowStatus.Loaded); Crud.ReloadGo <T>(client, row, true, connectBy, true); } else { // rowversion column is null: if (map.HasRowversion && row.GetRowversionValue() == null) { row.SetStatus(DbRowStatus.Faulted); } else { row.SetStatus(DbRowStatus.Loaded); } } result.FinalizeCrud(); return(result); } // optimistic concurrency violation else { if (forceMirroring) { var arguments = map.BuildExceptionReport(row.GetOriginalRKValues(), row.GetOriginalRowversionValue()); throw new QueryTalkException("Crud.UpdateGo", QueryTalkExceptionType.ConcurrencyViolation, arguments, Text.Method.UpdateGo).SetObjectName(map.Name.Sql); } else { result.FinalizeCrud(); return(result); } } } 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 Async <Result <T> > InsertGoAsync <T>(Assembly client, DbRow row, bool identityInsert, ConnectBy connectBy, Action <Result <T> > onCompleted) where T : DbRow { var name = Text.NotAvailable; try { var connectable = GetInsertBody <T>(client, row, identityInsert, connectBy, Text.Method.InsertGoAsync, ref name); return(connectable.GoAsync <T>(result => { if (result.RowCount > 0) { PropertyAccessor.SetValues(row, PropertyAccessor.GetValues(result.First())); row.SetStatus(DbRowStatus.Loaded); } result.FinalizeCrud(); onCompleted?.Invoke(result); })); } catch (QueryTalkException) { throw; } catch (System.Exception ex) { throw Crud.ClrException(ex, name, Text.Method.InsertGoAsync); } }
internal static Result <T> InsertGo <T>(Assembly client, DbRow row, bool identityInsert, ConnectBy connectBy) where T : DbRow { var name = Text.NotAvailable; try { var connectable = GetInsertBody <T>(client, row, identityInsert, connectBy, Text.Method.InsertGo, ref name); var result = connectable.Go <T>(); if (result.RowCount > 0) { PropertyAccessor.SetValues(row, PropertyAccessor.GetValues(result.First())); row.SetStatus(DbRowStatus.Loaded); // has rowversion & it is not RK if (row.HasNonRKRowversion) { Crud.ReloadGo <T>(client, row, false, connectBy, true); } else { // rowversion column is null: var map = DbMapping.TryGetNodeMap(row); if (map.HasRowversion && row.GetRowversionValue() == null) { row.SetStatus(DbRowStatus.Faulted); } else { row.SetStatus(DbRowStatus.Loaded); } } } result.FinalizeCrud(); return(result); } catch (QueryTalkException) { throw; } catch (System.Exception ex) { throw Crud.ClrException(ex, name, Text.Method.InsertGo); } }
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 Result <T> DeleteRowsGo <T>(Assembly client, IEnumerable <T> rows, bool forceMirroring, ConnectBy connectBy) where T : DbRow { SubResult subResult; if (forceMirroring) { subResult = GoMirroring <T>(client, rows, Crud.DeleteRowGo <T>, connectBy); } else { subResult = GoNonMirroring <T>(client, rows, ProcessPackage_DeleteRows, connectBy, null); } return(new Result <T>(subResult.Executed, subResult.AffectedCount)); }
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; } }
/// <summary> /// Inserts the specified rows in the database. /// </summary> /// <typeparam name="T">The type of the row object.</typeparam> /// <param name="client">A client assembly.</param> /// <param name="rows">The rows to insert.</param> /// <param name="identityInsert">If true, then the explicit value can be inserted into the identity column of a table.</param> /// <param name="connectBy">A ConnectBy object.</param> public static Result <T> InsertRowsGo <T>(Assembly client, IEnumerable <T> rows, bool identityInsert, ConnectBy connectBy) where T : DbRow { var subResult = GoNonMirroring <T>(client, rows, ProcessPackage_InsertRows, connectBy, identityInsert); return(new Result <T>(subResult.Executed, subResult.AffectedCount)); }
internal static Result ExecuteBulkInsert(Assembly client, DataTable data, TableArgument table, ConnectBy connectBy) { table.TryThrow(Text.Method.BulkInsertGo); if (data == null) { throw new QueryTalkException("Crud.GoBulkInsert", QueryTalkExceptionType.ArgumentNull, "data = null", Text.Method.BulkInsertGo) .SetObjectName(table.Sql); } if (data.Rows.Count == 0) { return(new Result(false, 0)); } 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.Sql; bulkCopy.WriteToServer(data); } } return(new Result(true, data.Rows.Count)); } catch (QueryTalkException ex) { ex.Method = Text.Method.BulkInsertGo; throw; } }
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 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); } }
internal static Async <Result <T> > DeleteGoAsync <T>(Assembly client, DbRow row, bool forceMirroring, ConnectBy connectBy, Action <Result <T> > onCompleted) where T : DbRow { var name = Text.NotAvailable; NodeMap map; try { var connectable = GetDeleteBody(client, row, forceMirroring, connectBy, Text.Method.DeleteGoAsync, ref name, out map); // ignore table T load connectable.IgnoreLoad = true; return(connectable.GoAsync <T>(result => { if (result.ReturnValue == 1) { row.SetStatus(DbRowStatus.Deleted); } // concurrency violation: else { if (forceMirroring) { var arguments = map.BuildExceptionReport(row.GetOriginalRKValues(), row.GetOriginalRowversionValue()); throw new QueryTalkException("Crud.DeleteGoAsync", QueryTalkExceptionType.ConcurrencyViolation, arguments, Text.Method.DeleteGo).SetObjectName(map.Name.Sql); } } result.FinalizeCrud(); onCompleted?.Invoke(result); })); } catch (QueryTalkException ex) { Loader.TryThrowInvalidSqlOperationException(ex, name, Text.Method.DeleteGoAsync); throw; } catch (System.Exception ex) { var ex2 = Crud.ClrException(ex, name, Text.Method.DeleteGoAsync); Loader.TryThrowInvalidSqlOperationException(ex2, name); throw ex2; } }
internal static SubResult UpdateRowGo <T>(Assembly client, DbRow row, bool optimistic, ConnectBy connectBy) where T : DbRow { var result = UpdateGo <T>(client, row, optimistic, connectBy); return(new SubResult(result.Executed, result.AffectedCount)); }
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); } }