devxlogo

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 TryEnd Sub

devx-admin

Share the Post: