CODEWITHSUNDEEP
c#sql-server
kraft1
kraft1

Reputation: 51

How to convert Binary data in column to a file?

I have a SQL Server table that contains a varbinary(max) column. When I query for this data with SSMS, the column looks like this in what I understand is hexadecimal:

0x255044462D312E37

So I copied this data and tried writing it to file like this with C#:

string hexString = "255044462D312E37"
byte[] file = StringToByteArray(hexString);

public static byte[] StringToByteArray(String hex)
{
    int NumberChars = hex.Length;
    byte[] bytes = new byte[NumberChars / 2];

    for (int i = 0; i < NumberChars; i += 2)
        bytes[i / 2] = Convert.ToByte(hex.Substring(i, 2), 16);

    return bytes;
}

File.WriteAllBytes(@"C:\file.pdf", file);

However I cannot open this file with Adobe, it says the file is damaged.

However, I also have an API that is connected to this Database and that serializes and deserialize responses using Newtonsoft. When it does this, it will convert the varbinary column to base64. And copying this base and the writing it to disc works just fine like this:

string b64 = "JVBERi0xLjcKJeLjz9M";
byte[] file = Convert.FromBase64String(b64);
File.WriteAllBytes(@"C:\file.pdf", file);

My question is: why can't I convert the hexadecimal string myself?

Upvotes: 4

Views: 9956

Answers (2)

Amirhossein
Amirhossein

Reputation: 1251

You can use stored procedure in sql-server like below :

I can describe this model by one example. if we have table Pictures column [PictureData] and need convert this binary to file use below SP :

Create PROCEDURE [dbo].[usp_ExportImage] (
   @PicName NVARCHAR (100)
   ,@ImageFolderPath NVARCHAR(1000)
   ,@Filename NVARCHAR(1000)
   )
AS
BEGIN
   DECLARE @ImageData VARBINARY (max);
   DECLARE @Path2OutFile NVARCHAR (2000);
   DECLARE @Obj INT
 
   SET NOCOUNT ON
 
   SELECT @ImageData = (
         SELECT convert (VARBINARY (max), PictureData, 1)
         FROM Pictures
         WHERE pictureName = @PicName
         );
 
   SET @Path2OutFile = CONCAT (
         @ImageFolderPath
         ,'\'
         , @Filename
         );
    BEGIN TRY
     EXEC sp_OACreate 'ADODB.Stream' ,@Obj OUTPUT;
     EXEC sp_OASetProperty @Obj ,'Type',1;
     EXEC sp_OAMethod @Obj,'Open';
     EXEC sp_OAMethod @Obj,'Write', NULL, @ImageData;
     EXEC sp_OAMethod @Obj,'SaveToFile', NULL, @Path2OutFile, 2;
     EXEC sp_OAMethod @Obj,'Close';
     EXEC sp_OADestroy @Obj;
    END TRY
    
 BEGIN CATCH
  EXEC sp_OADestroy @Obj;
 END CATCH
 
   SET NOCOUNT OFF
END

you can use this model for any type of file like JPG,PDF,ZIP and etc.

I hope this helpful for you

Upvotes: 4

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131463

varbinary is a binary type. It stores bytes, not a hex string. 0x255044462D312E37 is how your client tool, eg SSMS or Azure Data Studio display the binary data.

You can read binary data from a query's results through SqlDataReader.GetBytes or as a stream. Copying from the docs :

using (SqlConnection connection = new SqlConnection(connectionString)) 
using (SqlCommand command = new SqlCommand(
        "SELECT [bindata] FROM [Streams] WHERE [id]=@id", connection)) 
{
    command.Parameters.Add("id", SqlDbType.Int64).Value==1;
    await connection.OpenAsync();
    using (SqlDataReader reader = await command.ExecuteReaderAsync(
                   CommandBehavior.SequentialAccess)) 
     {
        if (await reader.ReadAsync()) 
        {
           if (!(await reader.IsDBNullAsync(0))) 
           {
              using (FileStream file = new FileStream(filePath, FileMode.Create, FileAccess.Write)) 
              {
                 using (Stream data = reader.GetStream(0)) 
                 {
                    await data.CopyToAsync(file);
                 }
              }
           }
        }
     }
}

ExecuteReaderAsync must be called with SequentialAccess, to avoid caching the entire file in memory.

Update

If you can't connect remotely, you can start a Powershell session from SSMS and use Invoke-SqlCmd to execute a query. By default Invoke-SqlCmd truncates long columns, so you need to use the -MaxBinaryLength parameter.

Something like this (haven't tested it) :

Invoke-Sqlcmd -Query "SELECT binaryField from someTable" -ServerInstance "." 
-MaxBinaryLength 1000000000 | Out-File -FilePath "someFile.pdf"

Upvotes: 5

Related Questions