save image to sql server 2012 using c#

Question

My aim is to insert images into my sql server database. image column in database defined as varbinary(max) type but when I execute the command, the Image column contains only the first value in the byte array!

Insert Image Script:

public void InsertImage()
    {
        string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

        Image img = Image.FromFile(@"D:\MyProjects\Registry Biometrics Insight\FingerPrintImages\1_2.bmp");
        byte[] arr;
        using (MemoryStream ms = new MemoryStream())
        {
            img.Save(ms, System.Drawing.Imaging.ImageFormat.Bmp);
            arr = ms.ToArray();
        }

        DBLayer dblayer = new DBLayer(CS);

        SqlParameter[] sqlParams = new SqlParameter[1];
        sqlParams[0] = new SqlParameter("@Image", SqlDbType.VarBinary);
        sqlParams[0].Value = arr;

        dblayer.M_ExecuteSQLCommand("prc_Fingers_InsertImage", sqlParams);
    }

ExecuteSQLCommand Script:

public int M_ExecuteSQLCommand(string StoredProcedureName, SqlParameter[] Parameters)
    {
        SqlCommand comm;
        try
        {
            using (SqlConnection conn = new SqlConnection(_P_ConnectionString))
            {

                comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandType = System.Data.CommandType.StoredProcedure;
                comm.CommandText = StoredProcedureName;
                comm.CommandTimeout = 0;

                comm.Parameters.Clear();
                for (int i = 0; i < Parameters.Length; i++)
                {
                    comm.Parameters.Add( Parameters[i] );
                }

                conn.Open();
                comm.ExecuteNonQuery();

                return 1;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

My Sorted Procedure:

Procedure [dbo].[prc_Fingers_InsertImage]
(@Image varbinary = null)
 as
 begin


INSERT INTO [dbo].[Fingers]
           ([Image])
     VALUES
           (@Image)
 end

Why did I get this result instead of saving all values from byte array??

ms.Rewind() after writing to it, before converting to an array. — Eric J., Mar 09 '16 at 14:38
Here is a good example http://stackoverflow.com/questions/744589/how-do-you-store-a-picture-in-an-image-column/744603#744603 — Juan Carlos Oropeza, Mar 09 '16 at 14:39
This may be relevant : http://stackoverflow.com/questions/29824377/saving-bytearray-to-varbinary-column-in-sql-server-inserts-only-one-byte — PaulF, Mar 09 '16 at 14:39
@Mohamad You might want to do this now, but tbh in my experience it's not worth it in the long run, especially when you end up with a scenario of having thousands of images being stored.the DB will increase exponentially in size over time, which makes DB backups and management a pain to deal with. Instead, consider saving the images to the filesystem and simply store the path to the images in the DB. — user1666620, Mar 09 '16 at 14:44
I said the method wrong from memory. It's @DaEkstrim's answer. Also I agree it's a pain to have large binary data in the DB long-term. — Eric J., Mar 09 '16 at 15:11

score 0 · Answer 1 · answered Mar 09 '16 at 14:50

You might want to use the method "Read" to convert the stream into an array of bytes

ms.Read(arr, 0, ms.Length);

I would even go as far as declare the size of the "arr" byte array just before, you read the stream into the array

arr = new byte[ms.length];

You might also have to reset the stream pointer, since after the Save method, the pointer is at the end of the stream:

ms.Seek(0, SeekOrigin.Begin);

The final code block would result in replacing

arr = ms.ToArray();

with

ms.Seek(0, SeekOrigin.Begin);
arr = new byte[ms.length];
ms.Read(arr, 0, ms.Length);

save image to sql server 2012 using c#

1 Answers1