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)
cn.Open()
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
dr.Read()
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
dr.Close()
cmd.Connection.Close()
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...
