Data Transfer – SQL to Local CSV

This post demonstrates how write a SQL table out to a local CSV file, the full code for this can be found here.

GetSqlRowsAsync

This returns an IAsyncEnumerable of DataRows, IAsyncEnumerable has been newly introduced with C# 8.0 and it allows results to be streamed back to the caller as they become available meaning we can stream our SQL table rows directly to our destination CSV file without having to hold anything in memory.

public static async IAsyncEnumerable<DataRow> GetSqlRowsAsync(string schema, string tableName)
{
	SqlConnection _sqlConnection = new SqlConnection(_sqlConnectionString);

	using (SqlCommand command = new SqlCommand("select * from " + schema + "." + tableName))
	{
		command.Connection = _sqlConnection;
		await _sqlConnection.OpenAsync();

		DataTable table = new DataTable();

		// Get schema of SQL table
		using (SqlDataAdapter adapter = new SqlDataAdapter(command))
		{
			table.TableName = tableName;
			adapter.FillSchema(table, SchemaType.Source);
		}

		// Return rows as they're read
		using (SqlDataReader reader = await command.ExecuteReaderAsync())
		{
			while (await reader.ReadAsync())
			{
				DataRow row = table.NewRow();

				row.Table.Columns
					.Cast<DataColumn>()
					.ToList()
					.ForEach(x => row[x] = reader.GetValue(x.Ordinal));

				yield return row;
			}
		}

		_sqlConnection.Close();
	}
}

Usage

using (var streamWriter = new StreamWriter(_outputPath))
using (var csvWriter = new CsvWriter(streamWriter))
{
	// Quote all fields
	csvWriter.Configuration.ShouldQuote = (field, context) => true;

	bool writeHeaders = true;

	await foreach (DataRow row in GetSqlRowsAsync(_schema, _table))
	{
		// Write header row
		if (writeHeaders)
		{
			foreach (DataColumn column in row.Table.Columns)
			{
				csvWriter.WriteField(column.ColumnName);
			}

			await csvWriter.NextRecordAsync();
			writeHeaders = false;
		}
		
		// Write data rows
		for (var i = 0; i < row.ItemArray.Length; i++)
		{
			csvWriter.WriteField(row[i]);
		}

		await csvWriter.NextRecordAsync();
	}
}

Usage – GZip

It’s also possible to zip the output stream in transit which is more efficient than outputting the file and then zipping it afterwards.

using (var outputFile = File.Create(_outputPath))
using (var gZipStream = new GZipStream(outputFile, CompressionMode.Compress))
using (var streamWriter = new StreamWriter(gZipStream))
using (var csvWriter = new CsvWriter(streamWriter))
{
	// Quote all fields
	csvWriter.Configuration.ShouldQuote = (field, context) => true;

	bool writeHeaders = true;

	await foreach (DataRow row in GetSqlRowsAsync(_schema, _table))
	{
		// Write header row
		if (writeHeaders)
		{
			foreach (DataColumn column in row.Table.Columns)
			{
				csvWriter.WriteField(column.ColumnName);
			}

			await csvWriter.NextRecordAsync();
			writeHeaders = false;
		}

		// Write data rows
		for (var i = 0; i < row.ItemArray.Length; i++)
		{
			csvWriter.WriteField(row[i]);
		}

		await csvWriter.NextRecordAsync();
	}
}