Insert a file as binary data into a database

Updated Reverend Jim 0 2K Views

Several people have asked about storing binary data in a database. I coded up an example today that uses an ADODB.Stream to copy the binary data but after some fiddling I found a more direct way. If you are using MS SQL Server, the easiest way I have found is to let the DBMS do all the dirty work. This example uses SqlClient but as long as the DBMS supports the operation, the same query should work under ADO or OleDB on other platforms. The example uses a database with two columns.

Dim filename As String = "D:\test.pdf"

Dim con As New SqlConnection("Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")
Dim cmd As New SqlCommand("", con)

cmd.CommandText = "INSERT INTO PDFStore (filename,Contents) " _
                & "SELECT '" & filename & "', * FROM " _
                & "OPENROWSET(BULK '" & filename & "', SINGLE_BLOB) SOMEALIAS"

con.Open()
cmd.ExecuteNonQuery()
con.Close()


The example database was created by

USE [mydb]

CREATE TABLE [dbo].[PDFStore](
	[FileName] [varchar](500) NOT NULL,
	[Contents] [varbinary](max) NULL,
 CONSTRAINT [PK_PDFStore] PRIMARY KEY CLUSTERED 
(
	[FileName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

An update to an existing record can be similarly done by

    cmd.CommandText = "UPDATE PDFStore " _
                    & "   SET Contents = (SELECT * FROM " _
                    & "       OPENROWSET(BULK '" & filename & "', SINGLE_BLOB) SOMEALIAS) " _
                    & " WHERE FileName = '" & filename & "'"
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Edit Preview