Is it possible to transfer contents in a C# .NET DataTable from memory to a remote file endpoint like SFTP?

Question

Currently we have C# .NET Framework 4.x app that exports data from a local database into a DataTable in memory, then writes it to a local TSV file. Then, the TSV file is zipped and uploaded to a remote https file object storage (OpenStack/Rackspace Cloud Files - similar to Amazon S3).

The plan is to enhance this process so that we can skip the "write to local TSV and zip" steps and instead stream or pipe out the in memory DataTable to a new remote file destination which will be a SFTP server (unless we find a better protocol/storage alternative that suits this particular detail of streaming/piping from memory) without first writing to a local file system.

Any thoughts on this and if there are libraries or code examples of writing out from an memory DataTable contents to an opened remote SFTP endpoint so that it sees it as a file upload? Or to a different remote endpoint type but still writing out the DataTable without first writing to the local filesystem? (could even be our current HTTPS Cloud Files or S3 like endpoint)

Answer 1

While your solutions works, it is quite inefficient memory-wise. You end up with four copies of the data in memory:

• DataTable
• string returned by ConvertDataTableToTSVFormat
• bytes byte array
• MemoryStream

Instead, you can directly write the data to the MemoryStream:

var remoteFilename = "datatable.tsv";
using (MemoryStream ms = new MemoryStream())
{
    ConvertDataTableToTSVFormat(dataTable, ms);

    using (var sftpClient = new SftpClient(host, Port, username, password))
    {
        sftpClient.Connect();
        sftpClient.BufferSize = 4 * 1024;
        sftpClient.UploadFile(ms, remoteFilename);
    }
}

private static void ConvertDataTableToTSVFormat(DataTable dataTable, Stream stream)
{
    using (var writer = new StreamWriter(stream))
    {
        string sep = string.Empty;
        foreach (var col in dataTable.Columns)
        {
            writer.Write(sep);
            writer.Write(col);
            sep = "\t";
        }
        writer.Write(Environment.NewLine);

        foreach (var dr in dataTable.Rows)
        {
            sep = string.Empty;
            foreach (var column in dr.ItemArray)
            {
                writer.Write(sep);
                writer.Write("\"" + column.ToString() + "\"");
                sep = "\t";
            }
            writer.Write(Environment.NewLine);
        }
    }
}

---

Alternatively, you can write the data directly to SFTP upload stream:

using (var stream = sftpClient.Create(remoteFilename))
{
   ConvertDataTableToTSVFormat(dataTable, stream);
}

Though here I'm not sure if there's any performance penalty for the upload.

Answer 2

Responding with an answer and example code from a first pass at this taken recently, using Renci.SshNet library:

var remoteFilename = "datatable.tsv";
var tsvContent = ConvertDataTableToTSVFormat(dataTable);

using (var sftpClient = new SftpClient(host, Port, username, password))
{
    sftpClient.Connect();
    sftpClient.BufferSize = 4 * 1024;
    using (MemoryStream ms = new MemoryStream())
    {
        byte[] bytes = Encoding.ASCII.GetBytes(tsvContent);
        ms.Write(bytes, 0, bytes.Length);
        ms.Seek(0, SeekOrigin.Begin);
        ms.Position = 0;
        sftpClient.UploadFile(ms, remoteFilename);
    }
}

private static string ConvertDataTableToTSVFormat(DataTable dataTable)
{
    StringBuilder fileContent = new StringBuilder();
    foreach (var col in dataTable.Columns)
    {
        fileContent.Append(col.ToString() + "\t");
    }
    fileContent.Replace("\t", System.Environment.NewLine, fileContent.Length - 1, 1);
    foreach (DataRow dr in dataTable.Rows)
    {
        foreach (var column in dr.ItemArray)
        {
            fileContent.Append("\"" + column.ToString() + "\"\t");
        }
        fileContent.Replace("\t", System.Environment.NewLine, fileContent.Length - 1, 1);
    }
    return fileContent.ToString();
}