Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: VB7
Expertise: Advanced
Nov 17, 2003

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
Francesco Balena
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap