March 13, 2013

VB.Net: SQL Server Image column to PicturBox Bitmap

There's always a debate at the office whether images should be stored in the database or in the file structure. I prefer the database, using the SQL Server "image" column data type.
This way there is no need to set authentication on the file server folders who can and cannot access the location of the images.

Usually the 'location' is still saved in a database column. When accessing the image at the stored location you have to catch exceptions if someone deleted the image from the server without updating the 'location' in the database.

When reading and writing one uses a byte array to 'communicate' the data. Consider the table above where the image column is "FactuurHeader".

Check the example "read" code:

 Public Shared Function GetHeader(ByVal cnStr As String) As Bitmap  
     'default (empty) logo  
     Dim logo As New Bitmap(100, 108)  
     Using cn As New SqlConnection(cnStr)  
       Using cmd As New SqlCommand()  
         cmd.CommandText = "SELECT TOP 1 FactuurHeader FROM dbo.tblPraktijk WHERE Actief=1"  
         cmd.Connection = cn  
         Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)  
         If dr.HasRows Then  
           If Not dr.Item("FactuurHeader").Equals(DBNull.Value) Then  
             'Here's the voodoo: from image column data to bitmap... 
             Dim imgData() As Byte = DirectCast(dr.Item("FactuurHeader"), Byte())  
             If imgData.Length > 0 Then  
               Dim stream As New MemoryStream(imgData)  
               logo = New Bitmap(stream)  
             End If  
           End If  
         End If  
       End Using  
     End Using  
     Return logo  
   End Function  

The example code takes the first record from the database table (tblPraktijk), checks if the "FactuurHeader" isn't null and returns a bitmap object. When the record does not exist (empty table) or it's image column is empty, a default (empty) bitmap is returned (first line in the method)

When writing to the column the process is reversed by creating a byte array from your bitmap. I use two static methods to do the conversion:

   ''' <summary>  
   ''' Convert Image to byte array 
   ''' (From picturebox to database data)  
   ''' </summary>  
   ''' <param name="img">Image to be converted</param>  
   ''' <returns>A byte array representing the image</returns>  
   ''' <remarks></remarks>  
   Public Shared Function ImageToBytes(img As Image) As Byte()  
     If img Is Nothing Then Return Nothing  
     Dim ms As New MemoryStream()  
     img.Save(ms, System.Drawing.Imaging.ImageFormat.Png)  '<- Use correct image format!
     Return ms.ToArray()  
   End Function 
   ''' <summary>  
   ''' Convert byte atray to Image  
   ''' (From database data to picturebox bitmap) 
   ''' PicturBox1.Image = new Bitmap(myImage) 
   ''' </summary>  
   ''' <param name="bytes">Byte arry to be converted</param>  
   ''' <returns>Image from the byte array</returns>  
   ''' <remarks></remarks>  
   Public Shared Function BytesToImage(bytes As Byte()) As Image  
     If bytes Is Nothing Then Return Nothing  
     Dim ms As New MemoryStream(bytes)  
     Dim img As Image = Image.FromStream(ms)  
     Return img  
   End Function  

Note: my default image format is PNG. Tweak the ImageToBytes function if you want a little flexibility in using different image formats...

No comments:

Post a Comment