internal static int DataReaderToCsv(
DbDataReader reader,
CsvWriter csvWriter,
SaveQueryToCSVOptions options,
CancellationToken cancellationToken)
{
// Write header and remember column indexes to include
var columnIndexesToInclude = new List <int>();
for (var i = 0; i < reader.FieldCount; i++)
{
var columnName = reader.GetName(i);
var includeColumn =
options.ColumnsToInclude == null ||
options.ColumnsToInclude.Length == 0 ||
options.ColumnsToInclude.Contains(columnName);
if (includeColumn)
{
if (options.IncludeHeadersInOutput)
{
var formattedHeader = FormatDbHeader(columnName, options.SanitizeColumnHeaders);
csvWriter.WriteField(formattedHeader);
}
columnIndexesToInclude.Add(i);
}
}
if (options.IncludeHeadersInOutput)
{
csvWriter.NextRecord();
}
int count = 0;
while (reader.Read())
{
foreach (var columnIndex in columnIndexesToInclude)
{
var value = reader.GetValue(columnIndex);
var dbTypeName = reader.GetDataTypeName(columnIndex);
var dotnetType = reader.GetFieldType(columnIndex);
var formattedValue = FormatDbValue(value, dbTypeName, dotnetType, options);
csvWriter.WriteField(formattedValue, false);
}
csvWriter.NextRecord();
count++;
}
return(count);
}
/// <summary>
/// Formats a value according to options
/// </summary>
/// <param name="value">Value from the database</param>
/// <param name="dbTypeName">Type of database column. E.g. for differentiating between DATE and DATETIME types</param>
/// <param name="options">Formatting options</param>
/// <returns></returns>
internal static string FormatDbValue(object value, string dbTypeName, Type dotnetType, SaveQueryToCSVOptions options)
{
if (value == null || value == DBNull.Value)
{
if (dotnetType == typeof(string))
{
return("\"\"");
}
if (dotnetType == typeof(DateTime) && options.AddQuotesToDates)
{
return("\"\"");
}
return("");
}
if (dotnetType == typeof(string))
{
var str = (string)value;
var delimiter = options.GetFieldDelimeterAsString();
str = str.Replace("\"", "\\\"");
str = str.Replace("\r\n", " ");
str = str.Replace("\r", " ");
str = str.Replace("\n", " ");
return($"\"{str}\"");
}
if (dotnetType == typeof(DateTime))
{
var dateTime = (DateTime)value;
var dbType = dbTypeName?.ToLower();
string output = null;
switch (dbType)
{
case "date":
output = dateTime.ToString(options.DateFormat, CultureInfo.InvariantCulture);
break;
case "datetime":
default:
output = dateTime.ToString(options.DateTimeFormat, CultureInfo.InvariantCulture);
break;
}
if (options.AddQuotesToDates)
{
return($"\"{output}\"");
}
return(output);
}
if (dotnetType == typeof(float))
{
var floatValue = (float)value;
return(floatValue.ToString("0.###########", CultureInfo.InvariantCulture));
}
if (dotnetType == typeof(double))
{
var doubleValue = (double)value;
return(doubleValue.ToString("0.###########", CultureInfo.InvariantCulture));
}
if (dotnetType == typeof(decimal))
{
var decimalValue = (decimal)value;
return(decimalValue.ToString("0.###########", CultureInfo.InvariantCulture));
}
return(value.ToString());
}
/// <summary>
/// Saves SQL query results to CSV file.
/// </summary>
/// <param name="parameters">Parameters of task</param>
/// <param name="options">Additional options</param>
/// <param name="cancellationToken">Cancellation token</param>
/// <returns>Returns amount of entries written</returns>
public async static Task <int> SaveQueryToCSV([PropertyTab] SaveQueryToCSVParameters parameters, [PropertyTab] SaveQueryToCSVOptions options, CancellationToken cancellationToken)
{
int output = 0;
var encoding = GetEncoding(options.FileEncoding, options.EnableBom, options.EncodingInString);
using (var writer = new StreamWriter(parameters.OutputFilePath, false, encoding))
using (var csvFile = CreateCsvWriter(options.GetFieldDelimeterAsString(), writer))
using (var sqlConnection = new SqlConnection(parameters.ConnectionString))
{
writer.NewLine = options.GetLineBreakAsString();
await sqlConnection.OpenAsync(cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
using (var command = BuildSQLCommand(parameters.Query, parameters.QueryParameters))
{
command.CommandTimeout = parameters.TimeoutSeconds;
command.Connection = sqlConnection;
var reader = await command.ExecuteReaderAsync(cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
output = DataReaderToCsv(reader, csvFile, options, cancellationToken);
}
csvFile.Flush();
}
return(output);
}
