Saving image to the database

问题:

I have some simple entity which now needs to have a Profile image. What is the proper way to do this? So, it is 1 to 1 relationship, one image is related only to one entity and vice versa. This image should be uploaded through webform together with inserting related entity.

If anyone can point me to the right direction how to persist images to the db and related entity will be great.

回答1:

Just a side comment: I think is not a good idea to store images in db.

In general is not a good idea store images in db as dbs are designed to store text not big binary chunks. Is much better to store paths for images and have images in a folder. If you want to get sure of 1 to 1 relationship name image with ID of entity (1323.jpg).

If you want to have image paths you should follow some guidelines (In general code defensively):

• On upload of image check that image is valid (even made a binary check of image header)
• Don't allow to overwrite an existing image in case of a INSERT of a new entity.
• Name images as primary key (1.jpg, 2.jpg)
• On load of image don't assume that image is going to be there.
• Do not allow (if possible) manual interaction with images (No remoting in machine and copying images from one place to other). Manual interaction can cause inconsistencies.

But I assume that for some reason you should do it. So in order to achieve what you want:

DB design

• Create a binary column (binary or varbinary) in your table
• It is better if you create it in a different table with 1-1 relationship. However the idea is avoiding to load image when hydrating entity. Use a lazy load approach to load your image only when you want.
• You have to avoid to load images when you make a big select (for example if you want to load all your entities in a combo avoid SELECT * From whatever) as it will load thousands of images for nothing. As I said this can be done by having images in a different table, or loading only proper columns in SELECT or by making lazy load. (Or even better by NOT having images in DB, only paths)

C# Code

• Use BynaryReader to read it

• User Byte array to store it

• Check this link for code example: http://www.codeproject.com/Articles/21208/Store-or-Save-images-in-SQL-Server

回答2:

The code is trivial but why the DB? If this is a website why not save it to a location on disk where you can easily reference it?

Databases are optimised to store data of a known size and relatively small size. Youre image will most likely be more than 8KB in length (mearning its a MAX datatype). The image will be stored on a separate row/page from your "profile".

Personally I'd save the images in a known folder and use the id for the image name. For profiles that don't have an image and use a standard gif or similar, probably keep it simple / trim by having simlinks/hardlinks of the profile id to the common gif.

public class Profile
{
    public int Id {get;}
    public string Name {get; private set;}
    public Image Picture {get; private set;}

    public void Save()
    {
        using (var connection = new SqlConnection("myconnectionstring"))
        using (var command = new SqlCommand("", connection))
        {
            command.CommandText =
                "UPDATE dbo.TblProfile " +
                "SET " +
                    "Name = @name, " +
                    "Picture = @picture " +
                "WHERE ID = @id";
            command.Parameters.AddWithValue("@name", Name);
            command.Parameters.AddWithValue("@picture", Picture);
            command.Parameters.AddWithValue("@id", Id);
            command.ExecuteNonQuery();
        }
    }
}

回答3:

I think following link would give you the solution,

Upload Image and Save in DB