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

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist