FileToBlob - Loading a file into a SQL Server's BLOB

FileToBlob - Loading a file into a SQL Server's BLOB

FileToBlob - Loading a file into a SQL Server's BLOB
' Reusable routine that loads a file into a SQL Server's BLOB.
' Requires Imports for System.Data and System.Data.SqlClient
' (It supports only tables with one key field.)
'
' Example:
' FileToBlob(cn, "pub_info", "logo", "pub_id", "0877", "c:\Example.bmp")
Sub FileToBlob(ByVal cn As SqlConnection, ByVal tableName As String, _
ByVal blobField As String, ByVal keyField As String, _
ByVal keyValue As Object, ByVal filename As String)
' Get the value of the pointer to the BLOB field.
Dim sql As String = String.Format _
("SELECT TEXTPTR({0}) FROM {1} WHERE {2}=@id", blobField, tableName, _
keyField)
Dim cmd As New SqlCommand(sql, cn)
cmd.Parameters.Add("@id", keyValue)
' The pointer is actually a byte array, but we don't really care here.
Dim pointer As Object = cmd.ExecuteScalar()
cmd.Dispose()
' Open the file, throw if not found.
Dim stream As New System.IO.FileStream(filename, FileMode.Open, _
FileAccess.Read)
' Prepare the receiving buffer.
Const CHUNK_SIZE As Integer = 200
Dim buffer(CHUNK_SIZE - 1) As Byte
' Prepare the UPDATETEXT command.
sql = String.Format("UPDATETEXT {0}.{1} @pointer @offset 0 @bytes", _
tableName, blobField)
cmd = New SqlCommand(sql, cn)
cmd.Parameters.Add("@pointer", pointer)
Dim offsetPar As SqlParameter = cmd.Parameters.Add("@offset", SqlDbType.Int)
Dim bufferPar As SqlParameter = cmd.Parameters.Add("@bytes", _
SqlDbType.Image, CHUNK_SIZE)
Dim offset As Integer = 0
Try
Do
' Read a bunch of bytes from the stream.
Dim count As Integer = stream.Read(buffer, 0, buffer.Length)
If count = 0 Then Exit Do
' Execute the UPDATETEXT command with this offset and these bytes.
offsetPar.Value = offset
bufferPar.Value = buffer
cmd.ExecuteNonQuery()
offset += count
Loop
Finally
stream.Close()
End Try
End Sub