protected async override Task <Action <AsyncCodeActivityContext> > ExecuteAsync(AsyncCodeActivityContext context, CancellationToken cancellationToken)
{
DataTable dataTable = null;
string connString = null;
SecureString connSecureString = null;
string provName = null;
string tableName = null;
string[] columnNames = null;
DatabaseConnection existingConnection = null;
long affectedRecords = 0;
IExecutorRuntime executorRuntime = null;
var continueOnError = ContinueOnError.Get(context);
try
{
existingConnection = DbConnection = ExistingDbConnection.Get(context);
connString = ConnectionString.Get(context);
provName = ProviderName.Get(context);
tableName = TableName.Get(context);
dataTable = DataTable.Get(context);
columnNames = ColumnNames.Get(context);
executorRuntime = context.GetExtension <IExecutorRuntime>();
connSecureString = ConnectionSecureString.Get(context);
ConnectionHelper.ConnectionValidation(existingConnection, connSecureString, connString, provName);
affectedRecords = await Task.Run(() =>
{
DbConnection = DbConnection ?? new DatabaseConnection().Initialize(connString != null ? connString : new NetworkCredential("", connSecureString).Password, provName);
if (DbConnection == null)
{
return(0);
}
if (executorRuntime != null && executorRuntime.HasFeature(ExecutorFeatureKeys.LogMessage))
{
return(DbConnection.BulkUpdateDataTable(BulkUpdateFlag, tableName, dataTable, columnNames, executorRuntime));
}
else
{
return(DbConnection.BulkUpdateDataTable(BulkUpdateFlag, tableName, dataTable, columnNames));
}
});
}
catch (Exception ex)
{
HandleException(ex, continueOnError);
}
finally
{
if (existingConnection == null)
{
DbConnection?.Dispose();
}
}
return(asyncCodeActivityContext =>
{
AffectedRecords.Set(asyncCodeActivityContext, affectedRecords);
});
}
private static void LogWarningMessage(IExecutorRuntime executorRuntime)
{
var message = new LogMessage
{
Message = Resources.BulkInsert_DriverDoesNotSupportBulkInsert,
EventType = TraceEventType.Warning
};
executorRuntime.LogMessage(message);
}
public long BulkInsertDataTable(string tableName, DataTable dataTable, IExecutorRuntime executorRuntime = null)
{
if (SupportsBulk())
{
return(DoBulkInsert(tableName, dataTable));
}
else
{
//if no bulk insert possible, fallback to insert data table with warning message
if (executorRuntime != null)
{
LogWarningMessage(executorRuntime);
}
return(InsertDataTable(tableName, dataTable));
}
}
private int DoBulkUpdate(string tableName, DataTable dataTable, string[] columnNames, IExecutorRuntime executorRuntime)
{
if (_connection == null)
{
return(-1);
}
var tblName = string.Empty;
var sqlCommand = _connection?.CreateCommand();
if (sqlCommand != null)
{
sqlCommand.Connection = _connection;
sqlCommand.Transaction = _transaction;
sqlCommand.CommandType = CommandType.Text;
}
try
{
tblName = CreateTempTableForUpdate(dataTable, tableName, sqlCommand);
BulkInsertDataTable(tblName, dataTable, executorRuntime);
sqlCommand.CommandText = string.Format("MERGE INTO {0} t USING (SELECT * FROM {1})s on ({3}) WHEN MATCHED THEN UPDATE SET {2}", tableName, tblName,
string.Join(",", dataTable.Columns.Cast <DataColumn>()
.Where(x => !columnNames.Contains(x.ColumnName, StringComparer.InvariantCultureIgnoreCase))
.Select(x => string.Format("t.{0}=s.{0}", EscapeDbObject(x.ColumnName))).ToArray()),
string.Join(" and ", dataTable.Columns.Cast <DataColumn>()
.Where(x => columnNames.Contains(x.ColumnName, StringComparer.InvariantCultureIgnoreCase))
.Select(x => string.Format("t.{0}=s.{0}", EscapeDbObject(x.ColumnName))).ToArray())
);
if (_connection is SqlConnection)
{
sqlCommand.CommandText += ";";
}
return(sqlCommand.ExecuteNonQuery());
}
catch (Exception)
{
throw;
}
finally
{
if (!string.IsNullOrEmpty(tblName))
{
sqlCommand.CommandText = string.Format("TRUNCATE TABLE {0}", tblName);
sqlCommand.ExecuteNonQuery();
sqlCommand.CommandText = string.Format("DROP TABLE {0}", tblName);
sqlCommand.ExecuteNonQuery();
}
}
}
public long BulkUpdateDataTable(bool bulkBatch, string tableName, DataTable dataTable, string[] columnNames, IExecutorRuntime executorRuntime = null)
{
if (bulkBatch && SupportsBulk())
{
return(DoBulkUpdate(tableName, dataTable, columnNames, executorRuntime));
}
else
{
return(DoBatchUpdate(tableName, dataTable, columnNames));
}
}
