Пример #1
0
        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);
        }
Пример #2
0
        /// <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());
        }
Пример #3
0
        /// <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);
        }