' 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