Save binary data to SQL Server with VB.NET

Saving and reading textual or numerical data from SQL Server is quite trivial. You can use standard SQL clauses like SELECT and INSERT, use databound controls or LINQ.

However, saving binary data to SQL Server with standard SQL is not so trivial. Basically you need three objects to obtain this, namely connection object, command object and parameter object. Below is a VB.NET sample which inserts a byte array to SQL Server. VB.NET sample below is a template how to do it. You have to provide your own connection string, table name and field name are hard coded. You may want to pass them as parameter rather than use hard coded names.

Imports System.Data.SqlClient

''' <summary>
''' Save a byte array to database
''' </summary>
''' <param name="ByteArr">Contains bytes to be saved</param>
''' <remarks></remarks>
Public Sub SaveByteArray(ByVal ByteArr() As Byte)
 '
 Dim strSQL As String
 Dim oConn As SqlConnection
 Dim oCmd As SqlCommand
 Dim oBLOBParam As SqlParameter

 Try
   ' Create and open connection object
   oConn = New SqlConnection("<Here goes your connection string>")
   oConn.Open()
   ' Insert statement
   ' Notice that @BLOBValue is a placeholder for the actual data
   strSQL = "INSERT INTO MyImageTable (BlobField) VALUES (@BLOBValue)"
   ' Create a command object
   oCmd = oConn.CreateCommand()
   ' Set SQL statement
   oCmd.CommandText = strSQL
   ' Create a command parameter
   oBLOBParam = New SqlParameter("@BLOBValue", SqlDbType.Binary, _
     ByteArr.Length, ParameterDirection.Input.ToString)
   ' Finally, set the actual data
   oBLOBParam.Value = ByteArr
   ' Add this parameter to the command
   oCmd.Parameters.Add(oBLOBParam)
   ' Execute SQL statement
   oCmd.ExecuteNonQuery()
   ' Close the connection
   oConn.Close()
   Catch ex As Exception

   End Try

End Sub

Notice that the "BlobField" in the SQL statement should be declared as Image-type in your database table.

If your table has an Identity field as a primary key, you may want to obtain primary key for this newly inserted image. All you have to do is change the SQL statement and the way the statement is executed.

Declare a variable to hold the new primary key:

Dim NewIdentity As Integer

Change the SQL statement to read new identity value:

strSQL = "INSERT INTO MyImageTable (BlobField) VALUES (@BLOBValue)"
strSQL = strSQL & "; SELECT @@IDENTITY AS 'Identity' "

Change "ExecuteNonQuery" as below:

NewIdentity = CInt(oCmd.ExecuteScalar())

Of course you may want to return this value either as a ByRef-parameter or by changing the code to function that returns primary key value.

Convert image to byte array and vice versa

.NET environment provides a great set of methods to handle images and pictures. Sometimes you may need to "serialize" an image to simple array of bytes and later "deserialize" byte array back to the image.

VB.NET snippets below do the trick. Code uses MemoryStream object to convert image to bytes and back.

Imports System.Drawing.Imaging
Imports System.IO

  ''' <summary>
''' Convert a byte array to an Image
''' </summary>
''' <param name="NewImage">Image to be returned</param>
''' <param name="ByteArr">Contains bytes to be converted</param>
''' <remarks></remarks>
Public Sub Byte2Image(ByRef NewImage As Image, ByVal ByteArr() As Byte)
  '
  Dim ImageStream As MemoryStream

  Try
    If ByteArr.GetUpperBound(0) > 0 Then
      ImageStream = New MemoryStream(ByteArr)
      NewImage = Image.FromStream(ImageStream)
    Else
      NewImage = Nothing
    End If
  Catch ex As Exception
    NewImage = Nothing
  End Try

End Sub

''' <summary>
''' Convert an image to array of bytes
''' </summary>
''' <param name="NewImage">Image to be converted</param>
''' <param name="ByteArr">Returns bytes</param>
''' <remarks></remarks>
Public Sub Image2Byte(ByRef NewImage As Image, ByRef ByteArr() As Byte)
  '
  Dim ImageStream As MemoryStream

  Try
    ReDim ByteArr(0)
    If NewImage IsNot Nothing Then
      ImageStream = New MemoryStream
      NewImage.Save(ImageStream, ImageFormat.Jpeg)
      ReDim ByteArr(CInt(ImageStream.Length - 1))
      ImageStream.Position = 0
      ImageStream.Read(ByteArr, 0, CInt(ImageStream.Length))
    End If
  Catch ex As Exception
  
  End Try

End Sub

Notice that Image2Byte uses Jpeg-format. ImageFormat-class supports some other formats too, like Gif and Bmp. You may want to add required format as parameter, if needed.