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