CODEWITHSUNDEEP
sqlvb.nett-sql
Lynchie
Lynchie

Reputation: 1149

Saving Image via Web Service to SQL and Retrieving It

I have created a Web Service that is intended to receive an image from a 3rd party, which will then gets imported into a SQL table.
After that, I retrieve the image and save it to a directory from an Azure Database at my end.

Everything works and I receive no errors, except when I retrieve the image into a 'test' picturebox, I get a 'Red X' for the image which signify's an error with the image.

Is anyone able to look at my code and see what I am doing wrong? This is the first time for me doing this kind of stuff.

Web Service:

Public Function Wunelli_SendImage(ByVal PolicyRef As String, ByVal ImagePath As String) Implements FreshCloud.Wunelli_SendImage  
    Using cn As New SqlConnection(sqlConn)
        'SqlConnection.ClearAllPools()
        Using cmd As New SqlCommand("INSERT INTO [dbo].[Autosaint_Wunelli_Engineer_Images] VALUES(@policyref,GETDATE(),@photo)", cn)
            Try
                With cmd
                    .Parameters.AddWithValue("@policyref", "LYJX01PC01")

                    Using MS As New MemoryStream(), BM As New Bitmap(ImagePath)
                        BM.Save(MS, System.Drawing.Imaging.ImageFormat.Jpeg)
                        Dim data As Byte() = MS.GetBuffer()
                        Dim p As New SqlParameter("@photo", SqlDbType.Image)
                        p.Value = data
                        .Parameters.Add(p)
                    End Using

                    .Connection.Open()

                    Return cmd.ExecuteNonQuery()

                    .Connection.Close()
                End With
            Catch ex As Exception
                MsgBox(ex.Message & " " & ex.StackTrace)
            End Try
        End Using
    End Using
End Function

WPF Test Harness to Retrieve Image:

Try
    Using cn As New SqlConnection(sqlConn)
        'SqlConnection.ClearAllPools()
        Using cmd As New SqlCommand("SELECT [Image] From [dbo].[Autosaint_Wunelli_Engineer_Images] WHERE [PolicyRef] = 'LYJX01PC01'", cn)
            Try
                With cmd
                    .Connection.Open()
                    Dim imageData As Byte() = DirectCast(cmd.ExecuteScalar(), Byte())

                    If Not imageData Is Nothing Then
                        Using ms As New MemoryStream(imageData, 0, imageData.Length)
                            ms.Write(imageData, 0, imageData.Length)
                            PictureBox1.BackgroundImage = Image.FromStream(ms, True)
                        End Using
                    End If

                    .Connection.Close()

                End With

            Catch ex As Exception
                MsgBox(ex.Message & " " & ex.StackTrace)
            End Try
        End Using
    End Using
Catch ex As Exception
    MsgBox(ex.Message & " " & ex.StackTrace)
End Try

Upvotes: 1

Views: 468

Answers (2)

Solomon Rutzky
Solomon Rutzky

Reputation: 48864

Regarding the Wunelli_SendImage method:

  1. Don't use Parameters.AddWithValue. Instead, create the parameter explicitly and give it a datatype, just like with @photo.
  2. Do not use the IMAGE datatype if using SQL Server 2005 or newer, either in the DB or in this app code via SqlDbType.Image. Instead, use VARBINARY(MAX) in the DB and here use SqlParameter("@photo", SqlDbType.VarBinary, -1) where -1 equates to MAX.
  3. You don't need to create the MemoryStream or Bitmap objects. Instead, use p.Value = File.ReadAllBytes(ImagePath) and not waste memory, or at least get rid of some unnecessary steps.
  4. The INSERT statement should include the field names:
    INSERT INTO [dbo].[Autosaint_Wunelli_Engineer_Images] ([PolicyRef], [Date], [Image]) VALUES (@policyref, GETDATE(), @photo);

Upvotes: 0

Mark
Mark

Reputation: 8160

From the Image.FromStream docs:

You must keep the stream open for the lifetime of the Image.

So, change this:

Using ms As New MemoryStream(imageData, 0, imageData.Length)

to

Dim ms As New MemoryStream(imageData, 0, imageData.Length)

and remove the End Using, so that the stream is not disposed, and it should work OK.

Upvotes: 1

Related Questions