How to read file in chunks from database with Dapper in dot net standard app

Question

I have created a dot net standard app for central place for storing files. I've managed to upload file in chunks cuz basically I leave the client to send the chunks and just append them as a stream in the database but the problem comes when I want to do it the way back, retrieving the file in chunks from the database (with a couple of sql queries perhaps) and not sending it at once. There is possibility to be done with SqlFileStream but its not possible in dot net standard application therefore I'm seeking to some solutions with Dapper reader may be ? Found some sample code here - https://stackoverflow.com/a/2101447 but I'm not sure if I can do it with dapper. Every proposition is much appreciated.

Answer 1

Found this solution with passing the Stream. Basically using the Respose.Body stream from client asp.net mvc and modifying it directly without returning any data. So on file get starts sending chunks directly to the client without using the whole server memory (only this 1mb specified at a time).

var sql = $@"
    SELECT [Data]
    FROM {TableName}
    WHERE ChunkId = @chunkId";

        using (var conn = this.dbConnectionFactory.GetSqlConnection)
        using (var reader = await conn.ExecuteReaderAsync(sql, new { chunkId }).ConfigureAwait(false))
        {
            while (reader.Read())
            {
                var buffer = new byte[1024 * 1024]; // Read chunks of 1MB
                var bytesRead = 0L;
                var dataIndex = 0L;

                while ((bytesRead = reader.GetBytes(0, dataIndex, buffer, 0, buffer.Length)) > 0)
                {
                    var actual = new byte[bytesRead];
                    Array.Copy(buffer, 0, actual, 0, bytesRead);

                    await stream.WriteAsync(actual, 0, (int)bytesRead).ConfigureAwait(false);

                    dataIndex += bytesRead;
                }
            }
        }