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