/// <summary>
/// Create multiple queries for batch operations like insert.
/// Queries are executed with Dapper ExecuteAsync.
/// See documentation at https://github.com/CommunityHiQ/Frends.Community.Oracle
/// </summary>
/// <param name="input">Input parameters</param>
/// <param name="options"></param>
/// <param name="cancellationToken"></param>
/// <returns>Object { bool Success, string Message, JArray Results}</returns>
public static async Task <MultiBatchOperationOutput> MultiBatchOperationOracle(
[PropertyTab] InputMultiBatchOperation input,
[PropertyTab] BatchOptions options,
CancellationToken cancellationToken)
{
try
{
using (var c = new OracleConnection(input.ConnectionString))
{
try
{
await c.OpenAsync(cancellationToken);
if (options.IsolationLevel == Oracle_IsolationLevel.None)
{
// Declare Result object.
int queryResult;
var queryResults = new JArray();
foreach (var query in input.BatchQueries)
{
using (var command = new OracleCommand(query.BatchInputQuery, c))
{
command.CommandTimeout = options.TimeoutSeconds;
// Is this xmlCommand specific?
command.BindByName = true;
var obj = JsonConvert.DeserializeObject <ExpandoObject[]>(query.InputJson, new ExpandoObjectConverter());
queryResult = await c.ExecuteAsync(
query.BatchInputQuery,
param : obj,
commandTimeout : options.TimeoutSeconds,
commandType : CommandType.Text)
.ConfigureAwait(false);
var result = new { QueryIndex = Array.IndexOf(input.BatchQueries, query), RowCount = queryResult };
queryResults.Add(JObject.FromObject(result));
}
cancellationToken.ThrowIfCancellationRequested();
}
return(new MultiBatchOperationOutput {
Success = true, Results = queryResults
});
}
else
{
var txn = c.BeginTransaction(options.IsolationLevel.GetTransactionIsolationLevel());
// Declare queryResult (rowcount).
int queryResult;
var queryResults = new JArray();
try
{
foreach (var query in input.BatchQueries)
{
var obj = JsonConvert.DeserializeObject <ExpandoObject[]>(query.InputJson, new ExpandoObjectConverter());
queryResult = await c.ExecuteAsync(
query.BatchInputQuery,
param : obj,
commandTimeout : options.TimeoutSeconds,
commandType : CommandType.Text,
transaction : txn)
.ConfigureAwait(false);
var result = new { QueryIndex = Array.IndexOf(input.BatchQueries, query), RowCount = queryResult };
queryResults.Add(JObject.FromObject(result));
cancellationToken.ThrowIfCancellationRequested();
}
txn.Commit();
txn.Dispose();
return(new MultiBatchOperationOutput {
Success = true, Results = queryResults
});
}
catch (Exception)
{
txn.Rollback();
txn.Dispose();
throw;
}
}
}
finally
{
// Close connection.
c.Dispose();
c.Close();
OracleConnection.ClearPool(c);
}
}
}
catch (Exception ex)
{
if (options.ThrowErrorOnFailure)
{
throw;
}
return(new MultiBatchOperationOutput
{
Success = false,
Message = ex.Message
});
}
}
/// <summary>
/// Create a query for a batch operation like insert.
/// The query is executed with Dapper ExecuteAsync.
/// See documentation at https://github.com/CommunityHiQ/Frends.Community.Oracle
/// </summary>
/// <param name="input">Input parameters</param>
/// <param name="options"></param>
/// <param name="cancellationToken"></param>
/// <returns>Object { bool Success, string Message, string Result }</returns>
public static async Task <BatchOperationOutput> BatchOperationOracle(
[PropertyTab] InputBatchOperation input,
[PropertyTab] BatchOptions options,
CancellationToken cancellationToken)
{
try
{
using (var c = new OracleConnection(input.ConnectionString))
{
try
{
await c.OpenAsync(cancellationToken);
using (var command = new OracleCommand(input.Query, c))
{
command.CommandTimeout = options.TimeoutSeconds;
// Is this xmlCommand specific?
command.BindByName = true;
// Declare Result object.
int queryResult;
if (options.IsolationLevel == Oracle_IsolationLevel.None)
{
var obj = JsonConvert.DeserializeObject <ExpandoObject[]>(input.InputJson,
new ExpandoObjectConverter());
queryResult = await c.ExecuteAsync(
input.Query,
param : obj,
commandTimeout : options.TimeoutSeconds,
commandType : CommandType.Text)
.ConfigureAwait(false);
return(new BatchOperationOutput {
Success = true, Result = queryResult
});
}
else
{
var txn = c.BeginTransaction(options.IsolationLevel.GetTransactionIsolationLevel());
try
{
var obj = JsonConvert.DeserializeObject <ExpandoObject[]>(input.InputJson, new ExpandoObjectConverter());
queryResult = await c.ExecuteAsync(
input.Query,
param : obj,
commandTimeout : options.TimeoutSeconds,
commandType : CommandType.Text,
transaction : txn)
.ConfigureAwait(false);
txn.Commit();
txn.Dispose();
return(new BatchOperationOutput {
Success = true, Result = queryResult
});
}
catch (Exception)
{
txn.Rollback();
txn.Dispose();
throw;
}
}
}
}
finally
{
// Close connection.
c.Dispose();
c.Close();
OracleConnection.ClearPool(c);
}
}
}
catch (Exception ex)
{
if (options.ThrowErrorOnFailure)
{
throw;
}
return(new BatchOperationOutput
{
Success = false,
Message = ex.Message
});
}
}
