Deserialize SQL Server image field back in Excel format-CodePudding

I have a SQL Server table that contains serialized Excel files, with 3 fields:

IdDocument -> int (PK)

DataFile -> image

FileName -> nvarchar

where DataFile contains the Excel file serialized, and FileName the name of the file (with path).

Something like this:

0xD0CF11E0A1B11AE100.....

U:\SAP_R3V4_Validation_Documents\March2012.xls

Now I need to get these files back in Excel format.

How can I accomplish this?

Using C# console application or SQL Server features could be fine.

Thank you in advance.

Luis

CodePudding user response：

Excel files are binary. The xls format is obsolete, replaced since 2007 (15 years ago) by xlsx, a ZIP package containing XML files. What the question shows is how binary data looks in SSMS, not some kind of serialized format.

BTW the image is deprecated, replaced by varbinary(max) in 2005 or 2008 (can't remember).

In any case, reading binary data is the same as reading any other data. A DbDataReader is used to retrieve the query results and strongly typed methods are used to read specific fields per row. In this particular case GetStream() can be used to retrieve the data as a Stream that can be saved to disk:

using var con=new SqlConnection(connectionString)
{
    using (var cmd=new SqlCommand(sql,con))
    {
        using (var reader=cmd.ExecuteReader())
        {
            while(reader.Read())
            {
                var path=reader.GetString(2);
                var finalPath=Path.Combine(root,Path.GetFileName(path))

                using(var stream=reader.GetStream(1))
                {
                    using(var fileStream=File.Create(finalPath))
                    {
                        stream.CopyTo(fileStream);
                    }
                }
            }
        } 
    }
}

The only thing that's different is the code that reads the field as a stream and saves it to disk

using(var stream=reader.GetStream(1))
{
    using(var fileStream=File.Create(finalPath))
    {
        stream.CopyTo(fileStream);
    }
}

The using clauses are used to ensure the data and file streams are closed even in case of error. The path itself is constructed by combining a root folder with the stored filename, not the full path