Data Transfer – SQL to SFTP CSV

Outputting a SQL table to a CSV file on an SFTP site is pretty much the same as outputting it to a local CSV file but with the added step of using SSH.NET to upload the stream. Full code for this can be found here.

GetSqlRowsAsync

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 (SftpClient destinationSftp = new SftpClient(_host, _userName, _password))
using (var memoryStream = new MemoryStream())
using (var streamWriter = new StreamWriter(memoryStream))
using (var csvWriter = new CsvWriter(streamWriter))
{
	destinationSftp.Connect();

	// Quote all fields
	csvWriter.Configuration.ShouldQuote = (field, context) => true;

	bool writeHeaders = true;

	await foreach (DataRow row in GetSqlRowsAsync(_sqlSchema, _sqlTable))
	{
		// 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();
	}

	// Reset memory stream to begining
	memoryStream.Position = 0;

	// Upload memory stream
	destinationSftp.UploadFile(memoryStream, _remoteOutputPath);
}

Usage – GZip

Similar to to my local CSV example it is also possible to zip the stream in flight which cuts down on the data needing to be transferred and improves processing speeds.

using (SftpClient destinationSftp = new SftpClient(_host, _userName, _password))
using (var compressedMemoryStream = new MemoryStream())
using (var gZipStream = new GZipStream(compressedMemoryStream, CompressionLevel.Optimal))
using (var memoryStream = new MemoryStream())
using (var streamWriter = new StreamWriter(memoryStream))
using (var csvWriter = new CsvWriter(streamWriter))
{
	destinationSftp.Connect();

	// Quote all fields
	csvWriter.Configuration.ShouldQuote = (field, context) => true;

	bool writeHeaders = true;

	await foreach (DataRow row in GetSqlRowsAsync(_sqlSchema, _sqlTable))
	{
		// 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();
	}

	// Upload memory stream
	memoryStream.Position = 0;
	await memoryStream.CopyToAsync(gZipStream);
	compressedMemoryStream.Position = 0;

	destinationSftp.UploadFile(compressedMemoryStream, _remoteOutputPath);
}