Convert and save photo in BD

Question

I am working on a project where I have to save some photos. So I am studying the best way to accomplish the task.

I thought about saving in the database, because these photos will be accessed both locally (Winforms application) and via internet (MVC Asp.Net Application).

So I found a solution that consists of turning the photo into a Array of Byte, according to the code below:

MemoryStream stream = new MemoryStream();

meuPictureBox1.Image.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg);

byte[] foto = stream.ToArray();

And then save in the database, where the table has the following characteristics:

CREATE TABLE [dbo].[Crm_Fotos]
    (
    [id] [int] IDENTITY(1,1) NOT NULL,
    [nCrm] [int] NULL,
    [Bfoto] [image] NULL,
    PRIMARY KEY CLUSTERED 
    (

However, I’m having some doubts:

1. This would be the best way to save a photo?
2. Ref. the quality? We have loss of quality in the photo?
3. If it’s a high-resolution photo (e.g.: 6000x4000 pixels, 24mb), I can perform this process normally?
4. Any type of image works? (Jpeg, PNG, Bitmap and others?)
5. The reverse process, namely to catch the array of Byte and convert to photo, it would not be a slow process?

Thank you.

Answer 1

Thomas, I suggest you declare the column that will store the photo image as varbinary(max). Is that the data type image will be disabled (someday...), according to Microsoft.

If the average image size is 1 MB or more, Microsoft suggests using FILESTREAM for storing the images. In this case, the files of each image are not inside the database, but in the Windows file system. This improves manager performance by considering memory allocation. You can access the files using Transact-SQL transactions or using Win32 API.

Reading suggestion:

• Enable and configure FILESTREAM
• Create a FILESTREAM-enabled database
• Create a table to store FILESTREAM data
• Access FILESTREAM data with Transact-SQL
• Create client applications for FILESTREAM data

Answer 2

1-This would be the best way to save a photo?

I prefer, because being in the database, is accessible in any environment (local or remote / application or web), and eliminates the problem of controlling 2 backups.

2-Ref. the quality? we have quality lose in the photo?

No, unless you use compression in the middle of the algorithm.

3-If it is a photo with high resolution (ex: 6000x4000 pixels, 24mb), I can perform this process normally?

Yes. It might slow you down by file size, but try to use different threads to load the image so the application doesn’t crash while uploading. If errors occur during Select, which may be due to lack of memory or timeout, SGDB will have settings to get around the situation.

4-Any type of image works? (Jpge, PNG, Bitmap and others?)

I believe so, I can’t tell you for sure about the "image" type of sql server, but it should be based on VARBINARY. In another table field, record the extension of the file, there when the user is saved, already saved in the correct format. Note: VARBINARY you can save any type of file: EXE, DLL, ZIP, etc.

5-The opposite process, that is, take the Byte array and convert to photo, would not be a slow process?

It depends on the size of the array, but you will only notice slowness if it is absurdly large.

I prefer to always save the images in the database, and when giving the select, I do not look for the image column. When loading the information on the screen, I show a progress "Loading image" and open the thread that will give the select in this field. The same I do for files.

In the case of images, here is an example of the functions I use:

byte[] for Image

    public static Image ConvertByteToImage(byte[] pic)
    {
        if (pic != null)
        {
            try
            {
                MemoryStream ImageDataStream = new MemoryStream();
                ImageDataStream.Write(pic, 0, pic.Length);
                System.Drawing.Image img = System.Drawing.Image.FromStream(ImageDataStream,true);
                return img;
            }
            catch
            {
                return null;
            }

        }
        else return null;
    }

Image to byte[]

public static byte[] ConvertImageToByte(System.Drawing.Image foto, System.Drawing.Imaging.ImageFormat format )
        {
            if (foto != null)
            {
                using (MemoryStream stream = new MemoryStream())
                {
                    foto.Save(stream, format);
                    //stream.Flush();
                    byte[] pic = stream.ToArray();
                    return pic;
                }
            }
            else return null;
        }