1

I am reading the varbinary(MAX) from the SQL server table into the byte[] content variable. Then the DeflateStream is used to unpack the content. The final goal is to assign the unpacked content to another byte[] array, and then store it into the SQL table.

The core of the question is how to allocate the output byte[] array and how to assign the result of deflating into it. So far, I am using another MemoryStream and its .ToArray() method to assign result, like this:

    byte[] content = row.Field<byte[]>("Content");

    using (var memstream = new MemoryStream(content, 0, content.Length))
    using (var defstream = new DeflateStream(memstream, CompressionMode.Decompress))
    using (var outstream = new MemoryStream())
    {
        defstream.CopyTo(outstream);
        byte[] deflated = outstream.ToArray();

        // ... write the deflated result to the SQL table.
    }

Is that a reasonably efficient solution? Or is there a cleaner way to save the DeflateStream result to byte[]?

I know that the DeflateStream implements the .Read(buffer, 0, bufferSize). However, the size of the result is not known in advance.

Context: The compressed content is a product image in JPEG or PNG. For the reason, the image is compressed once again by Microsoft.

pepr
  • 20,112
  • 15
  • 76
  • 139
  • 1
    `the image is compressed once again by Microsoft.` no, SQL Server doesn't ZIP-compress anything. If you refer to table compression, that's transparent to applications and *doesn't* work at the field level. It works at the row or page level. The application that wrote the data compressed those blobs. Needlessly too - JPEG and PNG are already compressed so compression didn't reduce the size and may have increased it. – Panagiotis Kanavos Jul 20 '23 at 12:02
  • 2
    As for efficiency, don't use LINQ to Dataset for starters. To create the DataTable you had to load all the blobs in memory. You can read blob data through a stream with [DbDataReader.GetStream](https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbdatareader.getstream?view=net-7.0). Streaming access in general is described [here](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sqlclient-streaming-support#streaming-support-to-sql-server) – Panagiotis Kanavos Jul 20 '23 at 12:05
  • What are you trying to do? Decompress data from one table and copy it to another? – Panagiotis Kanavos Jul 20 '23 at 12:08
  • 2
    You can also stream *into* SQL Server. The docs show how to [pass a stream as a parameter value](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sqlclient-streaming-support#sample----streaming-to-sql-server). You could remove all `MemoryStream` instances. Create `DeflateStream` over the stream returned by `GetStream` and pass `DeflateStream` as the input parameter value. – Panagiotis Kanavos Jul 20 '23 at 12:10
  • @PanagiotisKanavos: The compression is on the application level, and the application is by Microsoft (the Business Central). The image is only one of the possible media types. So, the compression may be important for texts and the like things. Anyway, all media are compressed the same way. – pepr Jul 20 '23 at 12:53
  • 2
    So you're working with D365. Why not use its APIs to retrieve the images? – Panagiotis Kanavos Jul 20 '23 at 13:06
  • @PanagiotisKanavos: Actually, I am writing the tool that is not part of the MS Business Central. I have only the access to their database, no access to their API. Our web application only uses their database to get the information. And the purpose of the tool that I am implementing is to get the images from their media database tables. – pepr Jul 20 '23 at 13:49

1 Answers1

1

You can just pass the stream directly as the Value for your command's parameter.

Note: do not dispose the stream with using until the command has been executed.

byte[] content = row.Field<byte[]>("Content");

using (var memstream = new MemoryStream(content))
using (var defstream = new DeflateStream(memstream, CompressionMode.Decompress))
{
    command.Parameters.Add("@yourVarBinary", SqlDbType.VarBinary, -1).Value = defstream;
    command.ExecuteNonQuery.....
}

Assuming row.Field is actually a SqlDataReader, you can do this in both directions.

using (var stream = row.GetStream(row.GetOrdinal("Content")))
using (var defstream = new DeflateStream(stream, CompressionMode.Decompress))
{
    command.Parameters.Add("@yourVarBinary", SqlDbType.VarBinary, -1).Value = defstream;
    command.ExecuteNonQuery.....
}

See also the documentation.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • This is good to know, thanks! Anyway, I am saving not only the original (unpacked) image, but also its thumbnail (and possibly more sizes). That is, I probably want to have the `byte[]` unpacked first, and process it more times. – pepr Jul 20 '23 at 12:47
  • 1
    @pepr you keep omitting important information so it's hard to offer any help. You're working either on or with D365, which means remote data and possibly local storage restrictions. You want to use the image data in memory, in which case you still need the last memory stream. You want to copy the images ... where? Back to D365? Or to a local database? It looks like the only improvement may be to use `GetStream` to read the data. – Panagiotis Kanavos Jul 20 '23 at 13:03
  • Or use the D365 APIs to retrieve the images instead of a SQL connection. – Panagiotis Kanavos Jul 20 '23 at 13:05
  • @PanagiotisKanavos: The code is for the command-line utility designed for preparing the images from the 3rd party (the MS Business Central). As the images are packed, they have to be unpacked first. The result is to be used to generate few smaller images (thumbnail, some bigger, one of the "normalized" big size). The source data are on Azure SQL. The result is to be stored into another database on Azure SQL. I have to get the image into memory as the SQL is not capable of the transformations. – pepr Jul 20 '23 at 13:43
  • 1
    Then if so you'll have to keep it as a `MemoryStream`. You can still stream it into SQL like the first half. – Charlieface Jul 20 '23 at 13:46