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

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));

Solution 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
)

Solution 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();

Solution 3:

It looks like you are trying to set the image data to a column that is set to the NVARCHAR (a basic text) data type. Either set the image data to the correct column that is VARBINARY(MAX)--or add that column to your table if it doesn't exist yet. Or you can change the current column you are using to the VARBINARY(MAX) data type via an ALTER TABLE command, if that is indeed the correct column and it was just created with the wrong data type to start with.

Solution 4:

Have a look at these two articles:

• Download and Upload images from SQL Server via ASP.Net MVC
• FILESTREAM MVC: Download and Upload images from SQL Server

They show not only how to do it, but how to do it efficiently using stream semantics. The naive solution of loading the entire image into an in memory byte[] will consume too much memory in your ASP process. The code shown is using MVC, but you can easily adapt it to APS Forms.