How to store images to a varbinary(max) column?

Question

I am getting this follwing sql exception while inserting an Image into sql server 2008.

Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query

In the database Image column datatype is Varbinary(MAX).

Edit

Code lifted from comment

paramaters.Add(getParam("@imageFilePath", DbType.AnsiString, imageFilePath));

Answer 1

Use this to read the file into a byte array:

    // Old fashioned way
    public static byte[] ReadFile(string filePath)
    {
        byte[] buffer;
        FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
        try
        {
            int length = (int)fileStream.Length;  // get file length
            buffer = new byte[length];            // create buffer
            int count;                            // actual number of bytes read
            int sum = 0;                          // total number of bytes read

            // read until Read method returns 0 (end of the stream has been reached)
            while ((count = fileStream.Read(buffer, sum, length - sum)) > 0)
                sum += count;  // sum is a buffer offset for next reading
        }
        finally
        {
            fileStream.Close();
        }
        return buffer;
    }

or

    // Thanks Magnus!
    byte[] data = System.IO.File.ReadAllBytes(filePath);

Then save the image data using this (I am using an image class "instance" that contains my image information and byte array in instance.Data):

   using(SqlCommand cm = new SqlCommand("SaveImage", connection, transaction)){
       cm.CommandType = CommandType.StoredProcedure;
       cm.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int,0, ParameterDirection.InputOutput, false, 10, 0, "Id", DataRowVersion.Current, (SqlInt32)instance.Id));
       cm.Parameters.Add(new SqlParameter("@Title", SqlDbType.NVarChar,50, ParameterDirection.Input, false, 0, 0, "Title", DataRowVersion.Current, (SqlString)instance.Title));
       if (instance.Data.Length > 0)
       {
           cm.Parameters.Add(new SqlParameter("@Data", SqlDbType.VarBinary,instance.Data.Length, ParameterDirection.Input, false, 0, 0, "Data", DataRowVersion.Current, (SqlBinary)instance.Data));
       }
       else
       {
           cm.Parameters.Add(new SqlParameter("@Data", SqlDbType.VarBinary,0, ParameterDirection.Input, false, 0, 0, "Data", DataRowVersion.Current, DBNull.Value));                    
       }

       cm.ExecuteNonQuery();
   )

And here is an example stored procedure:

CREATE PROCEDURE SaveImage
(
@Id int OUTPUT 
,@Title nvarchar(50)
,@Data varbinary(MAX)
)
AS
SET NOCOUNT ON
SET XACT_ABORT ON

IF @Id IS NULL OR @Id <= 0
BEGIN
SELECT @Id = ISNULL(MAX([Id]),0) + 1 FROM [dbo].[Images]
END

INSERT INTO [dbo].[Images] (
[Id]
,[Title]
,[Data]
) VALUES (
@Id
,@Title
,@Data
)

Answer 2

You are getting the error because you are trying to insert text into a varbinary(max) column; therefore, you are not storing the image but rather the PATH to the image.

If you only want to store the PATH, change your column type from varbinary(max) to varchar(max) If you do want to store the IMAGE BYTES then you need code to read the image from the file as a byte array and then you insert the the data like so:

byte [] buffer = File.ReadAllBytes("Path/to/your/image/");
...

SqlCommand command = ....
command.CommandType=CommandType.StoredProcedure;
command.Parameters.AddWithValue("@image",buffer);
command.ExecuteNonQuery();

or

SqlCommand command = ....
command.Text="INSERT INTO YOUR_TABLE_NAME (image) values (@image)";
command.Parameters.AddWithValue("@image",buffer);
command.ExecuteNonQuery();