/// <summary>
/// Write query results to csv string or file
/// </summary>
/// <param name="command"></param>
/// <param name="output"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public static async Task <string> ToXmlAsync(this OracleCommand command, OutputProperties output, CancellationToken cancellationToken)
{
command.CommandType = CommandType.Text;
// utf-8 as default encoding
Encoding encoding = string.IsNullOrWhiteSpace(output.OutputFile?.Encoding) ? Encoding.UTF8 : Encoding.GetEncoding(output.OutputFile.Encoding);
using (TextWriter writer = output.OutputToFile ? new StreamWriter(output.OutputFile.Path, false, encoding) : new StringWriter() as TextWriter)
using (OracleDataReader reader = await command.ExecuteReaderAsync(cancellationToken) as OracleDataReader)
{
using (XmlWriter xmlWriter = XmlWriter.Create(writer, new XmlWriterSettings {
Async = true, Indent = true
}))
{
await xmlWriter.WriteStartDocumentAsync();
await xmlWriter.WriteStartElementAsync("", output.XmlOutput.RootElementName, "");
while (await reader.ReadAsync(cancellationToken))
{
// single row element container
await xmlWriter.WriteStartElementAsync("", output.XmlOutput.RowElementName, "");
for (int i = 0; i < reader.FieldCount; i++)
{
await xmlWriter.WriteElementStringAsync("", reader.GetName(i), "", reader.GetValue(i).ToString());
}
// 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 (output.OutputToFile)
{
return(output.OutputFile.Path);
}
else
{
return(writer.ToString());
}
}
}
/// <summary>
/// Write query results to csv string or file
/// </summary>
/// <param name="command"></param>
/// <param name="output"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public static async Task <string> ToCsvAsync(this OracleCommand command, OutputProperties output, CancellationToken cancellationToken)
{
command.CommandType = CommandType.Text;
// utf-8 as default encoding
Encoding encoding = string.IsNullOrWhiteSpace(output.OutputFile?.Encoding) ? Encoding.UTF8 : Encoding.GetEncoding(output.OutputFile.Encoding);
using (OracleDataReader reader = await command.ExecuteReaderAsync(cancellationToken) as OracleDataReader)
using (TextWriter w = output.OutputToFile ? new StreamWriter(output.OutputFile.Path, false, encoding) : new StringWriter() as TextWriter)
{
bool headerWritten = false;
while (await reader.ReadAsync(cancellationToken))
{
// write csv header if necessary
if (!headerWritten && output.CsvOutput.IncludeHeaders)
{
var fieldNames = new object[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
fieldNames[i] = reader.GetName(i);
}
await w.WriteLineAsync(string.Join(output.CsvOutput.CsvSeparator, fieldNames));
headerWritten = true;
}
var fieldValues = new object[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
fieldValues[i] = reader.GetValue(i);
}
await w.WriteLineAsync(string.Join(output.CsvOutput.CsvSeparator, fieldValues));
// write only complete rows, but stop if process was terminated
cancellationToken.ThrowIfCancellationRequested();
}
if (output.OutputToFile)
{
return(output.OutputFile.Path);
}
else
{
return(w.ToString());
}
}
}
/// <summary>
/// Write query results to json string or file
/// </summary>
/// <param name="command"></param>
/// <param name="output"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public static async Task <string> ToJsonAsync(this OracleCommand command, OutputProperties output, CancellationToken cancellationToken)
{
command.CommandType = CommandType.Text;
using (OracleDataReader reader = await command.ExecuteReaderAsync(cancellationToken) as OracleDataReader)
{
var culture = String.IsNullOrWhiteSpace(output.JsonOutput.CultureInfo) ? CultureInfo.InvariantCulture : new CultureInfo(output.JsonOutput.CultureInfo);
// utf-8 as default encoding
Encoding encoding = string.IsNullOrWhiteSpace(output.OutputFile?.Encoding) ? Encoding.UTF8 : Encoding.GetEncoding(output.OutputFile.Encoding);
// create json result
using (var fileWriter = output.OutputToFile ? new StreamWriter(output.OutputFile.Path, false, encoding) : null)
using (var writer = output.OutputToFile ? new JsonTextWriter(fileWriter) : new JTokenWriter() as JsonWriter)
{
writer.Formatting = Newtonsoft.Json.Formatting.Indented;
writer.Culture = culture;
// start array
await writer.WriteStartArrayAsync(cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
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
OracleDecimal 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();
}
await writer.WriteEndObjectAsync(cancellationToken); // end row object
cancellationToken.ThrowIfCancellationRequested();
}
// end array
await writer.WriteEndArrayAsync(cancellationToken);
if (output.OutputToFile)
{
return(output.OutputFile.Path);
}
else
{
return(((JTokenWriter)writer).Token.ToString());
}
}
}
}
/// <summary>
/// Task for performing queries in Oracle databases. See documentation at https://github.com/CommunityHiQ/Frends.Community.Oracle.Query
/// </summary>
/// <param name="query"></param>
/// <param name="output"></param>
/// <param name="connection"></param>
/// <param name="options"></param>
/// <param name="cancellationToken"></param>
/// <returns>Object { bool Success, string Message, string Result }</returns>
public static async Task <Output> Query(
[PropertyTab] QueryProperties query,
[PropertyTab] OutputProperties output,
[PropertyTab] ConnectionProperties connection,
[PropertyTab] Options options,
CancellationToken cancellationToken)
{
try
{
using (var c = new OracleConnection(connection.ConnectionString))
{
try
{
await c.OpenAsync(cancellationToken);
using (var command = new OracleCommand(query.Query, c))
{
command.CommandTimeout = connection.TimeoutSeconds;
command.BindByName = true; // is this xmlCommand specific?
// check for command parameters and set them
if (query.Parameters != null)
{
command.Parameters.AddRange(query.Parameters.Select(p => CreateOracleParameter(p)).ToArray());
}
// declare Result object
string queryResult;
// set commandType according to ReturnType
switch (output.ReturnType)
{
case QueryReturnType.Xml:
queryResult = await command.ToXmlAsync(output, cancellationToken);
break;
case QueryReturnType.Json:
queryResult = await command.ToJsonAsync(output, cancellationToken);
break;
case QueryReturnType.Csv:
queryResult = await command.ToCsvAsync(output, cancellationToken);
break;
default:
throw new ArgumentException("Task 'Return Type' was invalid! Check task properties.");
}
return(new Output {
Success = true, Result = queryResult
});
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
// Close connection
c.Dispose();
c.Close();
OracleConnection.ClearPool(c);
}
}
}
catch (Exception ex)
{
if (options.ThrowErrorOnFailure)
{
throw ex;
}
return(new Output
{
Success = false,
Message = ex.Message
});
}
}
