' This is a very simple example on how to load binary file such a JPEG, Word
' document etc into SQL Server, and how to retrieve it. It's based on an
' article by Michael in SQL Server Magazine. The article is available at
' http://www.sqlmag.com and enter 92995 in the Instant DocID box. The article
' as such requires a subscription, but there is a zip file with the article
' you can download, and includes a Visual Studio project of a slightly versatile
' program than the code below. There is also an SQL script that shows to load
' a BLOB with the BULK rowset provider (SQL 2005 only).

' To run the program below as-is, create this table in the tempdb in your
' local server:
'
' CREATE TABLE BLOBtable (filename nvarchar(255)  NOT NULL,
'                         BLOB     varbinary(MAX) NOT NULL)
'
' If you are using SQL 2000, change "varbinary(MAX)" to "image".
'
' Compile the program from the .Net Framework command prompt with
'    vbc blobload.txt
' You need .Net Framework 2.0 (which comes with SQL Server.)
'
' To run it you need to provide a filename and a direction "in" or "out".
' Note that "out" does not write the file, it merely retrieves to get the
' size of the BLOB.
'
' In practice you may want to change connection string, table, add write-to-file
' etc.


Imports System.IO
Imports System.Data
Imports System.Data.SqlClient

Module BLOB

   Dim ConnectionString As String = _
         "Data Source=(local);Trusted_Connection=yes;Initial Catalog=tempdb"

   Private Sub ImportBLOB(filename As String)

        Dim fs As New FileStream _
            (filename, FileMode.Open, FileAccess.Read)
        Dim bloblen As Integer = CInt(fs.Length)
        Console.Write ("BLOB size: " & bloblen.ToString() & vbCrLf)
        Dim BLOB(bloblen) As Byte
        fs.Read(BLOB, 0, bloblen)
        fs.Close()

        Dim cn As New SqlConnection(ConnectionString)

        Dim cmd As SqlCommand = New SqlCommand _
                   ("INSERT INTO BLOBtable (filename, BLOB) " & _
                         "VALUES(@filename, @blob_data)", cn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.Add("@filename", SqlDbType.VarChar)
        cmd.Parameters("@filename").Direction = ParameterDirection.Input
        cmd.Parameters.Add("@blob_data", SqlDbType.Image)
        cmd.Parameters("@blob_data").Direction = ParameterDirection.Input

        ' Store the file name
        cmd.Parameters("@filename").Value = filename
        ' Store the byte array in the image field
        cmd.Parameters("@blob_data").Value = BLOB

        cn.Open()
        Dim Start As Long = System.DateTime.Now.Ticks
        cmd.ExecuteNonQuery()
        Console.Write("BLOB stored in " & _
                     ((System.DateTime.Now.Ticks - Start) / 10000) & _
                     " ms." & vbCrLf)
        cn.Close()

    End Sub

    Sub RetrieveBLOB(filename As String)

        Dim cn As New SqlConnection(ConnectionString)
        cn.open()

        Dim cmd As New SqlCommand  _
           ("SELECT BLOB FROM BLOBtable WHERE filename = @filename", cn)
        cmd.Parameters.Add("@filename", SqlDbType.VarChar)
        cmd.Parameters("@filename").Direction = ParameterDirection.Input
        cmd.Parameters("@filename").Value = filename

        Dim BLOB As Byte()
        Dim Start As Long = System.DateTime.Now.Ticks
        BLOB = cmd.ExecuteScalar()
        Console.Write("BLOB retrieved in " & _
                     ((System.DateTime.Now.Ticks - Start) / 10000) & _
                     " ms." & vbCrLf)
        Console.Write("Size of retrieved BLOB: " & BLOB.Length() & vbCrLf)

        cn.Close()
        cn = Nothing
    End Sub


    Public Sub Main(ByVal args() AS String)
       If args.Length <> 2 Then
          MsgBox("USAGE: blobload filename in|out")
          Exit Sub
       End If

       If args(1).ToLower() = "in" Then
          Call ImportBLOB(args(0))
       Else If args(1).ToLower() = "out" Then
          Call RetrieveBLOB(args(0))
       Else
          MsgBox("USAGE: blobload filename in|out")
       End If
     End Sub

End Module