Best Way To Store Images In SQL Server

Question

Which is the right way to store images in SQL Server? Is there any way to store them with ADO.NET Entity Framework? I need some good resources of how to do it in the best and modern way.

Answer 1

The right way if you ever decide to store images in SQL Server is to use the FILESTREAM type.

Is there any way to store them with ADO.NET Entity Framework?

The thing is that FILESTREAM is a SQL Server specific column whereas EF is designed to be database agnostic. So you could have a property of type byte[] on your model but EF will not take advantage of streaming. It will load it as standard varbinary(max).

You could also use the native SqlFileStream class to work directly with the FILESTREAM column type in ADO.NET. Here's a blog post which shows an example.

You might also decide to store files on the file system and save only the file path in the database.

Answer 2

You can store images in varbinary format. In the sql database table create two columns of types varbinary(MAX) and varchar(MAX) (You can use any size) and check "allow nulls".

To Your entity model add two properties:

public byte[] ImageData { get; set; } //Your image stored in binary data in the database
public string ImageMimeType { get; set; ) //just a string representation of Your image type,
                                          //this property is optional )

To ImageMimeType You can apply HiddenInputAttribute(DisplayValue=false). You don't need to apply it to ImageData cause the framework doesn't visualize editor for byte arrays.

To save image in the database: (As You can see here the model object is called "product")

if (image != null) {
product.ImageMimeType = image.ContentType; 
product.ImageData = new byte[image.ContentLength];
image.InputStream.Read(product.ImageData, 0, image.ContentLength);
}

Hope this helps.

p.s. I store images on the filestream and just keep the "file path" in the database.