What variable type should I use to save an image?

Question

I'm going to be saving images into an SQL Database (don't know what type to use there), and I'm going to query the DB for an image of a students portrait shot.

What variable type should I use to store this image?

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Nana_s_Student_Gradebook
{
    class Student
    {
        public string name { get; set; }
        public string lastName { get; set; }
        public byte[] picture { get; set; }
        public DateTime dateOfBirth { get; set; }
    }
}

Any guidance please? :)

Answer 1

On the SQL Server side, by all means, use a VARBINARY(MAX) type - it's binary, it's up to 2 GB in size, it's fast, it's perfect for that use case. Up to an average image size of about 1 MB, this is probably your best bet - even better than using the SQL Server 2008 FILESTREAM attribute (which is great if you have lots of really really large images, larger than 1 MB on a regular basis; here, the binary file is itself stored in the server machine's file system under database control).

In your .NET code, you'll probably want to use Image or a derived classes - ultimately, you'll have to stream those bytes out to SQL Server one way or another, but you don't need to use a byte array from the beginning - all picture-related types in .NET offer some kind of streaming support.

Answer 2

System.Drawing.Image or System.Drawing.Bitmap

Answer 3

You have it correct - use a byte array in your class. For images less than 1 MB, use a varbinary(max) column in SQL server. If the images are over 1 MB and you're using SQL Server 2008, consider using a FILESTREAM column.

You can use the chart on this MSDN page to refer to C#/Sql data type mappings.

Answer 4

Consider storing the image as a file and then reference the filename in the DB, not the binary data itself. This has several advantages, such as allowing you to store images on other servers and reducing the load/traffic on the SQL server.