' 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