Created
July 11, 2021 00:29
-
-
Save brokenthorn/91ce2190a6b2a57c25c3a281df2f7084 to your computer and use it in GitHub Desktop.
In this C# Gist, a CsvHelper.CsvWriter is used to asynchronously write the results of a SQL query to a CSV file without worrying about each column's data type.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
try | |
{ | |
await using var sqlDataReader = await sqlCommand.ExecuteReaderAsync(cancellationToken); | |
await using var streamWriter = new StreamWriter($"{reportName}_{startDate:dd-MM-yyyy}-{endDate:dd-MM-yyyy}.csv"); | |
await using var csvWriter = new CsvWriter(streamWriter, _csvConfiguration); | |
var columnSchema = sqlDataReader.GetColumnSchema(); | |
var columnCount = columnSchema.Count; | |
// write headers to CSV: | |
foreach (var columnName in columnSchema.Select(column => column.ColumnName)) | |
{ | |
csvWriter.WriteField(columnName); | |
} | |
await csvWriter.NextRecordAsync(); | |
// write rows to CSV: | |
while (await sqlDataReader.ReadAsync(cancellationToken)) | |
{ | |
object[] values = new object[columnCount]; | |
sqlDataReader.GetValues(values); | |
foreach (var v in values) | |
{ | |
csvWriter.WriteField(v); | |
} | |
await csvWriter.NextRecordAsync(); | |
} | |
if (streamWriter.BaseStream is FileStream fs) | |
{ | |
_logger.LogInformation("Wrote report file at {0}", fs.Name); | |
} | |
} | |
catch (Exception e) | |
{ | |
_logger.LogError("Error during reading SQL data and writing CSV files: {0}", GetExceptionMessageFirstLine(e)); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Very useful, thank you