/// <summary>
/// Task to execute multiple queries in Oracle database.
/// See documentation at https://github.com/CommunityHiQ/Frends.Community.Oracle
/// </summary>
/// <param name="input"></param>
/// <param name="output"></param>
/// <param name="options"></param>
/// <param name="cancellationToken"></param>
/// <returns>Object { bool Success, string Message, Array Results}</returns>
///
public static async Task <MultiQueryOutput> TransactionalMultiQuery(
[PropertyTab] InputMultiQuery input,
[PropertyTab] QueryOutputProperties output,
[PropertyTab] QueryOptions options,
CancellationToken cancellationToken)
{
try
{
if (options.EnableDetailedLogging == true)
{
OracleConfiguration.TraceFileLocation = options.TraceFileLocation;
OracleConfiguration.TraceLevel = options.TraceLevel;
}
using (var c = new OracleConnection(input.ConnectionString))
{
try
{
object queryResult;
var queryResults = new JArray();
await c.OpenAsync(cancellationToken);
if (options.IsolationLevel == Oracle_IsolationLevel.None)
{
// Set commandType according to ReturnType.
switch (output.ReturnType)
{
case QueryReturnType.Json:
foreach (var query in input.Queries)
{
var command = new OracleCommand(query.InputQueryString, c);
if (input.Parameters != null)
{
command.Parameters.AddRange(input.Parameters.Select(p => CreateOracleParameter(p)).ToArray());
}
command.CommandTimeout = options.TimeoutSeconds;
command.BindByName = true;
queryResult = await command.MultiQueryToJsonAsync(output, cancellationToken);
var result = new { QueryIndex = Array.IndexOf(input.Queries, query), Output = queryResult };
queryResults.Add(JObject.FromObject(result));
cancellationToken.ThrowIfCancellationRequested();
}
break;
case QueryReturnType.Xml:
foreach (var query in input.Queries)
{
var command = new OracleCommand(query.InputQueryString, c);
if (input.Parameters != null)
{
command.Parameters.AddRange(input.Parameters.Select(p => CreateOracleParameter(p)).ToArray());
}
command.CommandTimeout = options.TimeoutSeconds;
command.BindByName = true;
queryResult = await command.MultiQueryToXmlAsync(output, cancellationToken);
var result = new { QueryIndex = Array.IndexOf(input.Queries, query), Output = queryResult };
queryResults.Add(JObject.FromObject(result));
cancellationToken.ThrowIfCancellationRequested();
}
break;
case QueryReturnType.Csv:
foreach (var query in input.Queries)
{
var command = new OracleCommand(query.InputQueryString, c);
if (input.Parameters != null)
{
command.Parameters.AddRange(input.Parameters.Select(p => CreateOracleParameter(p)).ToArray());
}
command.CommandTimeout = options.TimeoutSeconds;
command.BindByName = true;
queryResult = await command.MultiQueryToCSVAsync(output, cancellationToken);
var result = new { QueryIndex = Array.IndexOf(input.Queries, query), Output = queryResult };
queryResults.Add(JObject.FromObject(result));
cancellationToken.ThrowIfCancellationRequested();
}
break;
default:
throw new ArgumentException("Task 'Return Type' was invalid! Check task properties.");
}
if (output.OutputToFile)
{
using (var file = File.CreateText(output.OutputFile.Path))
using (var writer = new JsonTextWriter(file))
{
writer.Formatting = Formatting.Indented;
queryResults.WriteTo(writer);
}
// Return output file path, not query results.
queryResults.Clear();
queryResults.Add(JObject.FromObject(new { OutputPath = output.OutputFile.Path.ToString() }));
}
return(new MultiQueryOutput {
Success = true, Results = queryResults
});
}
else
{
var txn = c.BeginTransaction(options.IsolationLevel.GetTransactionIsolationLevel());
try
{
// Set commandType according to ReturnType.
switch (output.ReturnType)
{
case QueryReturnType.Xml:
foreach (var query in input.Queries)
{
var command = new OracleCommand(query.InputQueryString, c);
if (input.Parameters != null)
{
command.Parameters.AddRange(input.Parameters.Select(p => CreateOracleParameter(p)).ToArray());
}
command.CommandTimeout = options.TimeoutSeconds;
command.BindByName = true;
queryResult = await command.MultiQueryToXmlAsync(output, cancellationToken);
var result = new { QueryIndex = Array.IndexOf(input.Queries, query), Output = queryResult };
queryResults.Add(JObject.FromObject(result));
cancellationToken.ThrowIfCancellationRequested();
}
txn.Commit();
break;
case QueryReturnType.Csv:
foreach (var query in input.Queries)
{
var command = new OracleCommand(query.InputQueryString, c);
if (input.Parameters != null)
{
command.Parameters.AddRange(input.Parameters.Select(p => CreateOracleParameter(p)).ToArray());
}
command.CommandTimeout = options.TimeoutSeconds;
command.BindByName = true;
queryResult = await command.MultiQueryToCSVAsync(output, cancellationToken);
var result = new { QueryIndex = Array.IndexOf(input.Queries, query), Output = queryResult };
queryResults.Add(JObject.FromObject(result));
cancellationToken.ThrowIfCancellationRequested();
}
txn.Commit();
break;
case QueryReturnType.Json:
foreach (var query in input.Queries)
{
var command = new OracleCommand(query.InputQueryString, c);
if (input.Parameters != null)
{
command.Parameters.AddRange(input.Parameters.Select(p => CreateOracleParameter(p)).ToArray());
}
command.CommandTimeout = options.TimeoutSeconds;
queryResult = await command.MultiQueryToJsonAsync(output, cancellationToken);
var result = new { QueryIndex = Array.IndexOf(input.Queries, query), Output = queryResult };
queryResults.Add(JObject.FromObject(result));
cancellationToken.ThrowIfCancellationRequested();
}
txn.Commit();
break;
default:
throw new ArgumentException("Task 'Return Type' was invalid! Check task properties.");
}
}
catch (Exception)
{
txn.Rollback();
txn.Dispose();
throw;
}
txn.Dispose();
if (output.OutputToFile)
{
using (var file = File.CreateText(output.OutputFile.Path))
using (var writer = new JsonTextWriter(file))
{
writer.Formatting = Formatting.Indented;
queryResults.WriteTo(writer);
}
// Return output file path, not query results.
queryResults.Clear();
queryResults.Add(JObject.FromObject(new { OutputPath = output.OutputFile.Path.ToString() }));
}
return(new MultiQueryOutput {
Success = true, Results = queryResults
});
}
}
finally
{
// Close connection.
c.Dispose();
c.Close();
OracleConnection.ClearPool(c);
}
}
}
catch (Exception ex)
{
if (options.ThrowErrorOnFailure)
{
throw;
}
return(new MultiQueryOutput
{
Success = false,
Message = ex.Message
});
}
}
/// <summary>
/// Task for performing queries in Oracle databases.
/// See documentation at https://github.com/CommunityHiQ/Frends.Community.Oracle
/// </summary>
/// <param name="queryInput"></param>
/// <param name="queryOutput"></param>
/// <param name="queryOptions"></param>
/// <param name="cancellationToken"></param>
/// <returns>Object { bool Success, string Message, string Result }</returns>
public static async Task <Output> ExecuteQueryOracle(
[PropertyTab] QueryProperties queryInput,
[PropertyTab] QueryOutputProperties queryOutput,
[PropertyTab] QueryOptions queryOptions,
CancellationToken cancellationToken)
{
try
{
if (queryOptions.EnableDetailedLogging == true)
{
OracleConfiguration.TraceFileLocation = queryOptions.TraceFileLocation;
OracleConfiguration.TraceLevel = queryOptions.TraceLevel;
}
using (var c = new OracleConnection(queryInput.ConnectionString))
{
try
{
await c.OpenAsync(cancellationToken);
using (var command = new OracleCommand(queryInput.Query, c))
{
command.CommandTimeout = queryOptions.TimeoutSeconds;
// Is this xmlCommand specific?
command.BindByName = true;
// Check for command parameters and set them.
if (queryInput.Parameters != null)
{
command.Parameters.AddRange(queryInput.Parameters.Select(p => CreateOracleParameter(p)).ToArray());
}
// Declare Result object.
string queryResult;
if (queryOptions.IsolationLevel == Oracle_IsolationLevel.None)
{
// Set commandType according to ReturnType.
switch (queryOutput.ReturnType)
{
case QueryReturnType.Xml:
queryResult = await command.ToXmlAsync(queryOutput, cancellationToken);
break;
case QueryReturnType.Json:
queryResult = await command.ToJsonAsync(queryOutput, cancellationToken);
break;
case QueryReturnType.Csv:
queryResult = await command.ToCsvAsync(queryOutput, cancellationToken);
break;
default:
throw new ArgumentException("Task 'Return Type' was invalid! Check task properties.");
}
return(new Output {
Success = true, Result = queryResult
});
}
else
{
var txn = c.BeginTransaction(queryOptions.IsolationLevel.GetTransactionIsolationLevel());
try
{
// Set commandType according to ReturnType.
switch (queryOutput.ReturnType)
{
case QueryReturnType.Xml:
queryResult = await command.ToXmlAsync(queryOutput, cancellationToken);
txn.Commit();
break;
case QueryReturnType.Json:
queryResult = await command.ToJsonAsync(queryOutput, cancellationToken);
txn.Commit();
break;
case QueryReturnType.Csv:
queryResult = await command.ToCsvAsync(queryOutput, cancellationToken);
txn.Commit();
break;
default:
throw new ArgumentException("Task 'Return Type' was invalid! Check task properties.");
}
}
catch (Exception)
{
txn.Rollback();
txn.Dispose();
throw;
}
txn.Dispose();
return(new Output {
Success = true, Result = queryResult
});
}
}
}
finally
{
// Close connection.
c.Dispose();
c.Close();
OracleConnection.ClearPool(c);
}
}
}
catch (Exception ex)
{
if (queryOptions.ThrowErrorOnFailure)
{
throw;
}
return(new Output
{
Success = false,
Message = ex.Message
});
}
}
/// <summary>
/// Write query results to csv string or file.
/// </summary>
/// <param name="command"></param>
/// <param name="queryOutput"></param>
/// <param name="cancellationToken"></param>
/// <returns>String</returns>
public static async Task <string> ToXmlAsync(this OracleCommand command, QueryOutputProperties queryOutput, CancellationToken cancellationToken)
{
var encoding = string.IsNullOrWhiteSpace(queryOutput.OutputFile?.Encoding) ? Encoding.UTF8 : Encoding.GetEncoding(queryOutput.OutputFile.Encoding);
using (var writer = queryOutput.OutputToFile ? new StreamWriter(queryOutput.OutputFile.Path, false, encoding) : new StringWriter() as TextWriter)
using (var reader = await command.ExecuteReaderAsync(cancellationToken) as OracleDataReader)
{
using (var xmlWriter = XmlWriter.Create(writer, new XmlWriterSettings {
Async = true, Indent = true
}))
{
await xmlWriter.WriteStartDocumentAsync();
await xmlWriter.WriteStartElementAsync("", queryOutput.XmlOutput.RootElementName, "");
while (await reader.ReadAsync(cancellationToken))
{
// single row element container.
await xmlWriter.WriteStartElementAsync("", queryOutput.XmlOutput.RowElementName, "");
for (var i = 0; i < reader.FieldCount; i++)
{
switch (reader.GetDataTypeName(i))
{
case "Decimal":
var v = reader.GetOracleDecimal(i);
var decimalValue = OracleDecimal.SetPrecision(v, 28);
string decimalString = decimalValue.ToString();
// Is decimal separator overwrite value given and query result value is not null?
if (!string.IsNullOrWhiteSpace(queryOutput.XmlOutput.DecimalSeparator))
{
decimalString = decimalString
.Replace(".", queryOutput.XmlOutput.DecimalSeparator)
.Replace(",", queryOutput.XmlOutput.DecimalSeparator);
}
await xmlWriter.WriteElementStringAsync("", reader.GetName(i), "", decimalString);
break;
case "Date":
case "TimeStamp":
case "TimeStampLTZ":
case "TimeStampTZ":
string dateString = ParseOracleDate(reader,
i,
queryOutput.XmlOutput.DateTimeFomat);
await xmlWriter.WriteElementStringAsync("", reader.GetName(i), "", dateString);
break;
default:
await xmlWriter.WriteElementStringAsync("", reader.GetName(i), "", reader.GetValue(i).ToString());
break;
}
}
// Close single row element container.
await xmlWriter.WriteEndElementAsync();
// Write only complete elements, but stop if process was terminated.
cancellationToken.ThrowIfCancellationRequested();
}
await xmlWriter.WriteEndElementAsync();
await xmlWriter.WriteEndDocumentAsync();
}
if (queryOutput.OutputToFile)
{
return(queryOutput.OutputFile.Path);
}
else
{
return(writer.ToString());
}
}
}
/// <summary>
/// Write query results to csv string or file.
/// </summary>
/// <param name="command"></param>
/// <param name="queryOutput"></param>
/// <param name="cancellationToken"></param>
/// <returns>String</returns>
public static async Task <string> MultiQueryToXmlAsync(this OracleCommand command, QueryOutputProperties queryOutput, CancellationToken cancellationToken)
{
using (var writer = new StringWriter())
{
using (var reader = await command.ExecuteReaderAsync(cancellationToken) as OracleDataReader)
{
using (var xmlWriter = XmlWriter.Create(writer, new XmlWriterSettings {
Async = true, Indent = true
}))
{
await xmlWriter.WriteStartDocumentAsync();
await xmlWriter.WriteStartElementAsync("", queryOutput.XmlOutput.RootElementName, "");
while (await reader.ReadAsync(cancellationToken))
{
// Single row element container.
await xmlWriter.WriteStartElementAsync("", queryOutput.XmlOutput.RowElementName, "");
for (var i = 0; i < reader.FieldCount; i++)
{
if (reader.GetDataTypeName(i).Equals("Decimal"))
{
var v = reader.GetOracleDecimal(i);
OracleDecimal.SetPrecision(v, 28);
await xmlWriter.WriteElementStringAsync("", reader.GetName(i), "", v.ToString());
}
else
{
await xmlWriter.WriteElementStringAsync("", reader.GetName(i), "", reader.GetValue(i).ToString());
}
cancellationToken.ThrowIfCancellationRequested();
}
// Close single row element container.
await xmlWriter.WriteEndElementAsync();
// Write only complete elements, but stop if process was terminated.
cancellationToken.ThrowIfCancellationRequested();
}
await xmlWriter.WriteEndElementAsync();
await xmlWriter.WriteEndDocumentAsync();
}
return(writer.ToString());
}
}
}
/// <summary>
/// Write query results to json string or file.
/// </summary>
/// <param name="command"></param>
/// <param name="queryOutput"></param>
/// <param name="cancellationToken"></param>
/// <returns>String</returns>
public static async Task <string> MultiQueryToCSVAsync(this OracleCommand command, QueryOutputProperties queryOutput, CancellationToken cancellationToken)
{
using (var reader = await command.ExecuteReaderAsync(cancellationToken) as OracleDataReader)
{
using (var writer = new StringWriter())
{
var headerWritten = false;
while (await reader.ReadAsync(cancellationToken))
{
// Initiate string builder for the line.
var sb = new StringBuilder();
// write csv header if necessary
if (!headerWritten && queryOutput.CsvOutput.IncludeHeaders)
{
var fieldNames = new object[reader.FieldCount];
for (var i = 0; i < reader.FieldCount; i++)
{
var fieldName = reader.GetName(i);
sb.Append(fieldName);
if (i < reader.FieldCount - 1)
{
sb.Append(queryOutput.CsvOutput.CsvSeparator);
}
cancellationToken.ThrowIfCancellationRequested();
}
await writer.WriteLineAsync(sb.ToString());
headerWritten = true;
}
sb = new StringBuilder();
var fieldValues = new object[reader.FieldCount];
for (var i = 0; i < reader.FieldCount; i++)
{
if (reader.GetDataTypeName(i).Equals("Decimal"))
{
var v = reader.GetOracleDecimal(i);
fieldValues[i] = OracleDecimal.SetPrecision(v, 28);
}
else
{
fieldValues[i] = reader.GetValue(i);
}
var fieldValue = fieldValues[i].ToString();
sb.Append(fieldValue);
if (i < reader.FieldCount - 1)
{
sb.Append(queryOutput.CsvOutput.CsvSeparator);
}
cancellationToken.ThrowIfCancellationRequested();
}
await writer.WriteLineAsync(sb.ToString());
// Write only complete rows, but stop if process was terminated.
cancellationToken.ThrowIfCancellationRequested();
}
return(writer.ToString());
}
}
}
/// <summary>
/// Write query results to json string or file.
/// </summary>
/// <param name="command"></param>
/// <param name="queryOutput"></param>
/// <param name="cancellationToken"></param>
/// <returns>JToken</returns>
public static async Task <object> MultiQueryToJsonAsync(this OracleCommand command, QueryOutputProperties queryOutput, CancellationToken cancellationToken)
{
using (var reader = await command.ExecuteReaderAsync(cancellationToken) as OracleDataReader)
{
var culture = string.IsNullOrWhiteSpace(queryOutput.JsonOutput.CultureInfo) ? CultureInfo.InvariantCulture : new CultureInfo(queryOutput.JsonOutput.CultureInfo);
// UTF-8 as default encoding.
var encoding = string.IsNullOrWhiteSpace(queryOutput.OutputFile?.Encoding) ? Encoding.UTF8 : Encoding.GetEncoding(queryOutput.OutputFile.Encoding);
// Create json result.
using (var writer = new JTokenWriter() as JsonWriter)
{
writer.Formatting = Newtonsoft.Json.Formatting.Indented;
writer.Culture = culture;
// Start array.
await writer.WriteStartArrayAsync(cancellationToken);
while (reader.Read())
{
// Start row object.
await writer.WriteStartObjectAsync(cancellationToken);
for (var i = 0; i < reader.FieldCount; i++)
{
// Add row element name.
await writer.WritePropertyNameAsync(reader.GetName(i), cancellationToken);
// Add row element value.
switch (reader.GetDataTypeName(i))
{
case "Decimal":
// FCOM-204 fix; proper handling of decimal values and NULL values in decimal type fields.
var v = reader.GetOracleDecimal(i);
var FieldValue = OracleDecimal.SetPrecision(v, 28);
if (!FieldValue.IsNull)
{
await writer.WriteValueAsync((decimal)FieldValue, cancellationToken);
}
else
{
await writer.WriteValueAsync(string.Empty, cancellationToken);
}
break;
default:
await writer.WriteValueAsync(reader.GetValue(i) ?? string.Empty, cancellationToken);
break;
}
cancellationToken.ThrowIfCancellationRequested();
}
// End row object.
await writer.WriteEndObjectAsync(cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
}
// End array.
await writer.WriteEndArrayAsync(cancellationToken);
return(((JTokenWriter)writer).Token);
}
}
}
/// <summary>
/// Write query results to csv string or file.
/// </summary>
/// <param name="command"></param>
/// <param name="queryOutput"></param>
/// <param name="cancellationToken"></param>
/// <returns>String</returns>
public static async Task <string> ToCsvAsync(this OracleCommand command, QueryOutputProperties queryOutput, CancellationToken cancellationToken)
{
var encoding = string.IsNullOrWhiteSpace(queryOutput.OutputFile?.Encoding) ? Encoding.UTF8 : Encoding.GetEncoding(queryOutput.OutputFile.Encoding);
using (var reader = await command.ExecuteReaderAsync(cancellationToken) as OracleDataReader)
using (var w = queryOutput.OutputToFile ? new StreamWriter(queryOutput.OutputFile.Path, false, encoding) : new StringWriter() as TextWriter)
{
var headerWritten = false;
while (await reader.ReadAsync(cancellationToken))
{
// Initiate string builder for the line.
var sb = new StringBuilder();
// Write csv header if necessary.
if (!headerWritten && queryOutput.CsvOutput.IncludeHeaders)
{
var fieldNames = new object[reader.FieldCount];
for (var i = 0; i < reader.FieldCount; i++)
{
var fieldName = reader.GetName(i);
sb.Append(fieldName);
if (i < reader.FieldCount - 1)
{
sb.Append(queryOutput.CsvOutput.CsvSeparator);
}
cancellationToken.ThrowIfCancellationRequested();
}
await w.WriteLineAsync(sb.ToString());
headerWritten = true;
}
sb = new StringBuilder();
var fieldValues = new object[reader.FieldCount];
for (var i = 0; i < reader.FieldCount; i++)
{
switch (reader.GetDataTypeName(i))
{
case "Decimal":
var v = reader.GetOracleDecimal(i);
var decimalValue = OracleDecimal.SetPrecision(v, 28);
// Is decimal separator overwrite value given and query result value is not null?
if (!string.IsNullOrWhiteSpace(queryOutput.CsvOutput.DecimalSeparator) && !decimalValue.IsNull)
{
fieldValues[i] = decimalValue.ToString()
.Replace(".", queryOutput.CsvOutput.DecimalSeparator)
.Replace(",", queryOutput.CsvOutput.DecimalSeparator);
}
else
{
fieldValues[i] = decimalValue;
}
break;
case "Date":
case "TimeStamp":
case "TimeStampLTZ":
case "TimeStampTZ":
var dateString = ParseOracleDate(reader, i, queryOutput.CsvOutput.DateTimeFomat);
fieldValues[i] = dateString;
break;
default:
fieldValues[i] = reader.GetValue(i);
break;
}
var fieldValue = fieldValues[i].ToString();
// Remove possible line breaks.
fieldValue = fieldValue.Replace("\r\n", " ").Replace("\n", " ").Replace("\r", " ");
sb.Append(fieldValue);
if (i < reader.FieldCount - 1)
{
sb.Append(queryOutput.CsvOutput.CsvSeparator);
}
cancellationToken.ThrowIfCancellationRequested();
}
await w.WriteLineAsync(sb.ToString());
}
if (queryOutput.OutputToFile)
{
return(queryOutput.OutputFile.Path);
}
else
{
return(w.ToString());
}
}
}
