Inserting screenshot into a database

Question

I read up on inserting images into SQL server databases but I couldn't make my code work. Here is my initial code.

public void InsertResults(string Id, Bitmap bitmap)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand("INSERT INTO Results VALUES(@ResultID, @HasSucceeded, @ScenarioID, @Screenshot)", conn))
                     {

                        cmd.Parameters.AddWithValue("@ResultID", 0);
                        cmd.Parameters.AddWithValue("@HasSucceeded", 0);
                        cmd.Parameters.AddWithValue("@ScenarioID", Id);
                        cmd.Parameters.AddWithValue("@Screenshot", bitmap);

                        cmd.ExecuteNonQuery();
                     }
                }
            }
            catch (SqlException ex)
            {
                //Log exception
            } 
        }

I know that you can't just insert a Bitmap image like I tried to above. How do I go about converting the Bitmap into something I can insert?

I know some SQL like this below has to be used but I'm not sure how to integrate it into my code.

INSERT INTO DatabaseImageTable ([image name], [image])
SELECT 'SQL Server Image', *
FROM OPENROWSET(BULK N'C:\images\sql-server-image-file.jpg', SINGLE_BLOB) image;

You don't use OPENROWST/BULK from code. Instead, just write to the BLOB directly (exposed in ADO.NET as `byte[]`; convert the Bitmap to/from). Alternatively, use the [FILESTREAM](http://msdn.microsoft.com/en-us/library/gg471497.aspx) type (which is well suited to [large] file storage). Some hints here: http://stackoverflow.com/questions/697779/custom-streaming-to-read-blobs-from-ms-sql-how-should-i-handle-the-connection and a stream blob wrapper http://stackoverflow.com/questions/2101149/how-to-i-serialize-a-large-graph-of-net-object-into-a-sql-server-blob-without-c — user2246674, Jul 18 '13 at 22:51
If you just need to convert the bitmap to a byte array to insert into a blob column, this should help: http://stackoverflow.com/questions/7350679/convert-a-bitmap-into-a-byte-array-in-c — David, Jul 18 '13 at 22:51

score 2 · Accepted Answer · answered Jul 18 '13 at 23:08

2

Write the bitmap as binary: most commonly, save it to a MemoryStream, then call ToArray() on the MemoryStream() to get a byte[]. This byte[] can be trivially stored in a column of type "image" or (and preferably) "varbinary(max)".

answered Jul 18 '13 at 23:08

Marc Gravell

1,026,079
266
2,566
2,900

4

If you go this route, definitely use `varbinary`, as `image` (`ntext`, and `text`) are deprecated and will be removed in newer versions of sql-server. – Matthew Jul 18 '13 at 23:28

Inserting screenshot into a database

1 Answers1