From time to time, we get questions on how to show images that are stored as BLOBs in SQLite. If you're having trouble displaying these images in ActiveReports, or another software, this blog article is written for you.
NOTE: This process applies only to BLOBs from a SQLite database. Using images from file locations or other data sources, such as SQL Server, is simple and does not require conversion. BLOB images from SQL Server, for example, can be displayed simply by setting the “Value” property of the Image control to the data set field containing the image.
Here's the code that converts BLOBs from SQLite:
Public Function BlobToByte(ByVal myImage) As Byte() Dim str As String = BlobToString(myImage) Return System.Text.Encoding.Default.GetBytes(str) End Function Public Function BlobToString(ByVal myImage) As String Dim str As String = HexToString(myImage.Substring(2, x.Length - 3)) 'remove envelope sqlite Return str.Substring(78, str.Length - 79) ' remove header Bitmap Image Paint.Picture End Function Function HexToString(ByVal hex As String) As String Dim text As New System.Text.StringBuilder(hex.Length \ 2) For i As Integer = 0 To hex.Length - 2 Step 2 text.Append(Chr(Convert.ToByte(hex.Substring(i, 2), 16))) Next Return text.ToString End Function
Next, place this code in the “Script” section of our RDL report:
Drop an Image control onto the design surface, and change its “Value” Property to:
This expression tells the RDL report to look in the Script section for a Function named BlobToByte, which takes the BLOB image as a parameter.
Here's how the report looks in the preview:
You can run this sample report by downloading the attached files. You'll need to edit the report’s connection string to point at the directory of the Northwind.sl3 database included in the download. And as with any other database provider, make sure you have the SQLite ODBC driver installed.