Convert byte data to image and save? Using SQL query

Question

I have a table like below

IMAGEID          COVERPAGEIMAGE       IMAGENAME
------------------------------------------------
LME111201908576  0x89504E470D0A1...   NULL

I want to convert all the COVERPAGEIMAGE is of varbinary(max) datatype to a png/jpeg image and update the name in IMAGENAME using a SQL query.

The table includes almost 10000 rows of data. I couldn't find a faster way to do it yet any help or guidance would be appreciated.

Do you know exactly with format are the pictures? png or jpeg? — Ygalbel, Apr 21 '20 at 07:07
What do you mean by "*convert to png/jpg*", do you want to *extract* all content from the database and save as local files and file names should be stored in IMAGENAME column? — user2316116, Apr 21 '20 at 07:24
I think @Ygalbel was asking if you already know the image format for `COVERPAGEIMAGE` in each and every row of your table. The sample row you've posted has a PNG image, for example, because it starts with the signature bytes `0x89 0x50 0x4e 0x47`. — AlwaysLearning, Apr 21 '20 at 07:25

user2316116 · Answer 1 · 2020-04-21T07:46:14.947

0

There is File.WriteAllBytes method that could help to create file from blob data. Example,

...
using (var r =  cmd.ExecuteReader())
{
    while (r.Read())
    {
         File.WriteAllBytes("C:\temp\" + (string)r["IMAGEID"]+ ".png",
              (byte[])r["COVERPAGEIMAGE"]);
     }

}

Assigning IMAGENAME will depend on how target file names will look like. If filename will be always equal to IMAGEID+".png" like in example above, you might not need that column at all.

Same can be done with only sql: fastest way to export blobs from table into individual files

edited Apr 21 '20 at 07:46

answered Apr 21 '20 at 07:39

user2316116

6,726
1
21
35

I want to add different name while saving the images and update that name in the table – marceloo1223 Apr 21 '20 at 08:52
in the sample above the file name will correspond to the imageid. you can change this to any other own logic. – user2316116 Apr 21 '20 at 09:07

Convert byte data to image and save? Using SQL query

1 Answers1