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