Is it possible to transfer contents in a C# .NET DataTable from memory to a remote file endpoint lik-CodePudding

Currently we have C# .NET Framework 4x 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)

CodePudding user response：

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();
}