' 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 92955 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
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
