SQL Image to Crystal Report

Question

I want to pull image data from SQL Server 2008 Express and display it directly in a Crystal Report. I am using Visual Studio 2012 along with Crystal Reports for Visual Studio (also a full version of SAP Crystal Reports 2013).

I have attempted to use samples from Google/SO searches but I seem to be missing at least one key element. In my project I have a dataset with one field which is of System.Byte(), a Crystal Report, and a viewer.

Here is the snippet of code which queries the database, reads the memorystream, and puts the image data into the dataset.

 Dim ds As New DataSet1
   Dim row As DataRow

   Dim objConn As New SqlConnection(DatabaseConnection.CTLDataConnectionString)
   Dim objCommand As SqlCommand = objConn.CreateCommand
   objCommand.CommandText = "SELECT Content FROM Report WHERE HandlingUnitID = " & HandlingUID
   Dim dr As SqlDataReader = Nothing
   If objConn.State = ConnectionState.Closed Then objConn.Open()

   dr = objCommand.ExecuteReader

   If dr.HasRows Then
       dr.Read()
       Dim b() As Byte = DirectCast(dr("Content"), Byte())
       Using ms As New MemoryStream(b)
         ds.DataTable1.Rows.Add(b)
       End Using

   End If

   Dim rpt As New ShipLabel
   rpt.SetDataSource(ds.Tables("DataTable1"))

   Dim frm As New CRviewer
   frm.CRvwr.ReportSource = rpt
   frm.ShowDialog()

This will bring up the Crystal Viewer but it is a blank report. I want to know if I have the correct sequence for getting the data, and if I am storing it correctly in the dataset.

Answer 1

After a lot more reading/researching I have the following snippets of code which appear to have the correct sequencing, and correctly storing the data in the dataset. This snippet is from a cellclick event.

 Dim ds As New DataSet1
   Dim row As DataRow

   Dim img As Bitmap

   Dim objConn As New SqlConnection(DatabaseConnection.CTLDataConnectionString)
   Dim objCommand As SqlCommand = objConn.CreateCommand
   objCommand.CommandText = "SELECT Content FROM Report WHERE HandlingUnitID = " & HandlingUID
   Dim dr As SqlDataReader = Nothing
   If objConn.State = ConnectionState.Closed Then objConn.Open()

   dr = objCommand.ExecuteReader

   If dr.HasRows Then
       dr.Read()
       Dim b() As Byte = dr("Content")

    Using ms As New MemoryStream(b)
        img = Image.FromStream(ms)
        b = ConvertImageToByte(img)
        ds.DataTable1.Rows.Add(b)
    End Using

   End If
   objConn.Close()

   Dim rpt As New ShipLabel
   rpt.SetDataSource(ds.Tables("DataTable1"))
   rpt.SetParameterValue("JobNumber", OrderNumber.ToString)

   Dim frm As New CRviewer
   frm.CRvwr.ReportSource = rpt
   frm.ShowDialog()

And this function:

Public Shared Function ConvertImageToByte(ByVal Value As Image) As Byte()
If Value IsNot Nothing Then
    Dim fs As MemoryStream = New MemoryStream()
    CType(Value, Bitmap).Save(fs, ImageFormat.Jpeg)
    Dim retval As Byte() = fs.ToArray()
    fs.Dispose()
    Return retval
End If

Return Nothing

End Function