/// <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 OdbcCommand command, OutputProperties output, CancellationToken cancellationToken)
{
using (var reader = await command.ExecuteReaderAsync(cancellationToken))
{
var culture = string.IsNullOrWhiteSpace(output.JsonOutput.CultureInfo) ? CultureInfo.InvariantCulture : new CultureInfo(output.JsonOutput.CultureInfo);
// UTF-8 as default encoding.
var 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);
await writer.WriteValueAsync(reader.GetValue(i) ?? string.Empty, cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
}
// End row object.
await writer.WriteEndObjectAsync(cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
}
// End array.
await writer.WriteEndArrayAsync(cancellationToken);
return(output.OutputToFile ? output.OutputFile.Path : ((JTokenWriter)writer).Token.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> ToXmlAsync(this OdbcCommand command, OutputProperties output, CancellationToken cancellationToken)
{
// UTF-8 as default encoding.
var encoding = string.IsNullOrWhiteSpace(output.OutputFile?.Encoding) ? Encoding.UTF8 : Encoding.GetEncoding(output.OutputFile.Encoding);
using (var writer = output.OutputToFile ? new StreamWriter(output.OutputFile.Path, false, encoding) : new StringWriter() as TextWriter)
using (var reader = await command.ExecuteReaderAsync(cancellationToken))
{
using (var 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();
}
return(output.OutputToFile ? output.OutputFile.Path : 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 OdbcCommand command, OutputProperties output, CancellationToken cancellationToken)
{
// UTF-8 as default encoding.
var encoding = string.IsNullOrWhiteSpace(output.OutputFile?.Encoding) ? Encoding.UTF8 : Encoding.GetEncoding(output.OutputFile.Encoding);
using (var reader = await command.ExecuteReaderAsync(cancellationToken))
using (var 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();
}
return(output.OutputToFile ? output.OutputFile.Path : w.ToString());
}
}
/// <summary>
/// ODBC query task.
/// </summary>
/// <param name="queryParameters"></param>
/// <param name="output"></param>
/// <param name="options"></param>
/// <param name="cancellationToken"></param>
/// <returns>Object { String Result or Dynamic Result }</returns>
/// <exception cref="Exception"></exception>
public static async Task <dynamic> Query([PropertyTab] QueryParameters queryParameters, [PropertyTab] OutputProperties output, [PropertyTab] ConnectionInformation options, CancellationToken cancellationToken)
{
using (var connection = new OdbcConnection(options.ConnectionString))
{
await connection.OpenAsync(cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
using (var command = connection.CreateCommand())
{
command.CommandTimeout = options.TimeoutSeconds;
command.CommandText = queryParameters.Query;
command.CommandType = CommandType.Text;
command.Parameters.AddRange(queryParameters.ParametersInOrder.Select(x => new OdbcParameter {
Value = x.Value
}).ToArray());
string queryResult;
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 {
Result = queryResult
});
}
}
}