Insert a file as binary data into a database

Reverend Jim

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.

1,684 Views
About the Author

I completed my Computer Science degree at the University of Manitoba in 1976. I did two and a half years of programming in medical research followed by twenty-nine years at Manitoba Hydro (electric utility). Most of that was spent on doing development and maintenance on an AGC/SCADA (real-time programming/process control) system. The last ten years of that was spent doing application and infrastructure support and development. I have programmed in FORTRAN (mostly), APL, PL/1, COBOL, Lisp, SNOBOL, ALGOL, Assembler (several flavours), C, C++, Paradox, VB, vbScript and more recently, Python. I am married with two grown children of whom I am very proud, and a most beautiful wife. I am currently retired (and loving it).

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 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.