Stream.CopyToAsync never calls my WriteAynsc method

Question

I wrote a custom Stream class that needs to hit a database to read/write info. I wanted to use the XxAsync database methods, so I was advised to write both the synchronous and asynchronous methods in my stream since I couldn't use connection.OpenAsync() in my synchronous Read/Write methods.

However, after implementing both versions, when I then use this stream as the destination in a call like the following in a simple Console application's void Main() method:

Task.Run( async () =>
{
    using ( var ctx = new JobContext() )
    {
        // create AssemblyCache record and get assemblyKey...

        using ( var fs = new System.IO.FileStream( @"C:\BTR\Source\Assemblies\BTR.Rbl.Evolution.Documents.dll",
                                    System.IO.FileMode.Open,
                                    System.IO.FileAccess.Read,
                                    System.IO.FileShare.None,
                                    8192,
                                    true ) )
        using ( var bs = ctx.GetBinaryWriteStream<AssemblyCache>( assemblyKey ) )
        {
            await fs.CopyToAsync( bs );
        }
    }
} ).Wait();

It never seems to call my XxAsync methods. I only get trace for the synchronous versions. Looking at the source for Stream base class, it looks like CopyToAsync should call my WriteAsync method, so I'm not sure what I'm missing.

What are you trying to do?? Treating the database as a stream of .. something is a very bad idea, simply because a database is *not* a stream. Your methods would have to parse lines, split values and send records to the database in your "async" methods. You gain absolutely nothing by hiding an entire ETL process there, but you *do* make your code a lot harder to read and run. For example, sending rows one by one is a lot slower than sending a batch of rows with SqlBulkCopy — Panagiotis Kanavos, Sep 07 '16 at 15:29
The appropriate abstraction for an ETL job is the dataflow. A pipeline of concurrent steps that read lines from files, parse them, transform them and finally batch them and send them to the database. Which, by the way, is what SSIS does. — Panagiotis Kanavos, Sep 07 '16 at 15:31
Streaming (sometimes very large) files to varbinary columns. — Terry, Sep 07 '16 at 15:31
That is doen by passing a `FileStream` value instead of a binary value to the appropriate SqlCommand, as shown in [SqlClient Streaming Support](https://msdn.microsoft.com/en-us/library/hh556234(v=vs.110).aspx). Check the `StreamBLOBToServer` sample — Panagiotis Kanavos, Sep 07 '16 at 15:37
Interesting. I'll check it out. Although, for learning I guess I'd still like to figure out why CopyToAsync didn't call my WriteAsync. — Terry, Sep 07 '16 at 16:24
Looking at [the underlying implementation of `Stream.CopyToAsync()`](http://referencesource.microsoft.com/#mscorlib/system/io/stream.cs,08ee62b6d544c8fe), I don't see any way that it could _not_ call your `ReadAsync()` method (`FileStream` doesn't override the base implementation). Please provide a good [mcve] that reliably reproduces the behavior you describe. — Peter Duniho, Sep 07 '16 at 19:47
@PeterDuniho Where do I place this program after writing it? — Terry, Sep 07 '16 at 23:31
@PeterDuniho Ever get to look at the demo? Or was there too much code for you to digest? — Terry, Sep 23 '16 at 16:21
I did not look at the linked code. Time is far too precious for me to spend even a moment doing work that someone else should have done. Fact is, if you take the time to create a true [mcve], the most likely outcome is that you'll find the problem yourself. But if not, then you'll have a code example others can use to help you without wasting any of their time. Please see also [ask], and especially the linked articles at the bottom. These go into much more detail about the how and why of creating a good code example. — Peter Duniho, Sep 23 '16 at 16:37

score 2 · Answer 1 · answered Sep 07 '16 at 15:49

You can use SqlClient's streaming support to stream data from the client to a BLOV (ie varbinary(MAX)) column on the server. You use almost the same code that you'd use to perform an INSERT but instead of using passing a value to the SQL command, you pass a stream or stream reader. For example, instead of using

var sql="INSERT INTO [MyTable] (blobField) VALUES (@data)"
using (SqlConnection conn = new SqlConnection(connectionString)) 
using (SqlCommand cmd = new SqlCommand(sql, conn)) 
{
    cmd.Parameters.Add("@data", SqlDbType.Binary, myBuffer.Length).Value = myBuffer;
    await conn.OpenAsync();
     await cmd.ExecuteNonQueryAsync();
}

you'd write

var sql="INSERT INTO [MyTable] (blobField) VALUES (@data)"
using (SqlConnection conn = new SqlConnection(connectionString)) 
using (SqlCommand cmd = new SqlCommand(sql, conn)) 
{
    await conn.OpenAsync();
    using (FileStream file = File.Open("binarydata.bin", FileMode.Open)) 
    {
        cmd.Parameters.Add("@data", SqlDbType.Binary, -1).Value = file;
        await cmd.ExecuteNonQueryAsync();
     }
}

If you want to send many files, you can assign streams to the parameter in a loop:

    var blobParam=cmd.Parameters.Add("@data", SqlDbType.Binary, -1);
    foreach(var somePath in aListOfPaths)
    {
        using (FileStream file = File.Open(somePath, FileMode.Open)) 
        {
            blobparam.Value = file;
            await cmd.ExecuteNonQueryAsync();
         }
     }

Trying to go this route. Curious on your thoughts on compressing data. I've asked another question at http://stackoverflow.com/questions/40346948/compressing-data-with-a-gzipstream-stream-wrapper-when-using-sqlclient-streaming — Terry, Oct 31 '16 at 17:06
What are you trying to do? SQL Server already supports compression of table data and FILESTREAM files [can be stored on compressed volumes](https://msdn.microsoft.com/en-us/library/gg471497.aspx#FILESTREAM+Storage) — Panagiotis Kanavos, Oct 31 '16 at 17:15

score 0 · Accepted Answer · answered Feb 05 '20 at 16:49

0

It turns out my GetBinaryWriteStream extension (intentionally) wraps a GZipStream around my custom stream to support compression. The GZipStream was the cause of not calling WriteAsync. I've asked a new question specific to this here

answered Feb 05 '20 at 16:49

Terry

2,148
2
32
53

Stream.CopyToAsync never calls my WriteAynsc method

2 Answers2