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.