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