How To Store Any File into SQL Database

Hello friends, I am posting a solution to store any file in SQL server table and use it any where in an application as downloadable file

using OPENROWSET you can insert a file data in binary format to SQL table

Lets take one example to understand completely

One simple scenario is to just store the file in table and read from it from an application/ website in WCF or .NET

Create one table with a column as varbinay type

CREATE TABLE [dbo].[FileTable](
 [FileName] [nvarchar](50) NOT NULL,
 [FileData] [varbinary](max) NOT NULL
) 
GO

Using above query table will be created

Now to store file in this table use below SQL query

DECLARE @Data varbinary(MAX) = (SELECT * FROM OPENROWSET(BULK N'D:\JP\Bloger\PDFFile.pdf',SINGLE_BLOB) AS AnyFile);
INSERT INTO [JPDB].[dbo].[FileTable]
VALUES('PDFFile.pdf',@Data)

Above SQL Query will store binary data of file: 'D:\JP\Bloger\PDFFile.pdf' in the column 'FileData' of table 'FileTable'

Now if we execute below query

SELECT [FileName]
      ,[FileData]
  FROM [JPDB].[dbo].[FileTable]

We will get the result

This table can be used in any application to download this stored file easily

For example in C# you can use Response.BinaryWrite to download the file from SQL DB

I would like to explain one more thing here as per my experience in IT industry, many projects does not allow varbinary cloumn data type in a table

In these type of cases, we can insert data existing schema of database in varchar type column

To insert file data in a varchar type column we can use same approach as explained above

Suppose we have one table with following schema

To insert file data in this table having column varchar type, we need two steps:

First insert binary data in a temporary Table in varbinary column using below query

DECLARE @Data varbinary(MAX) = (SELECT * FROM OPENROWSET(BULK N'D:\JP\Bloger\PDFFile.pdf',SINGLE_BLOB) AS AnyFile);
SELECT 'PDFFile.pdf' AS [FileName],@Data AS [FileData]
INTO [JPDB].[dbo].[temporaryTable] 

SELECT [FileName]
      ,[FileData]
  FROM [JPDB].[dbo].[temporaryTable]

Above query will insert file data in table 'temporaryTable'

Now in second step copy this binary data of file in the table which has varchar column data type

Best way to insert binary data (actually this is hexadecimal string) in varchar type column is, generate sql script from temporaryTable

Follow below steps to generate script of table with data. Also you can refer my blog "How to generate Insert Script of a table in SQL Server"

Right click on DB -> Task -> GenerateScripts

A new Window will open as shown below, then click on 'Next' button

After clicking on Next button, a new wizard will open for selecting object for which you want to create script, select second radio button for selecting specific database object and then select the check-box of your table as shown below

After clicking on 'Next' button, Click on 'Advance' button

In the advance option change general settings of 'Types of data to script' -> Schema and data and then click on Ok button

After clicking on Ok button, you can change directory path where you want to store your script and then click on 'Next' button

Click on 'Next' button

Click finish button, your script will be stored in the given location

Open the script in SQL Server

Copy the hex string as highlighted in the above image to insert in the table which has column of varchar type, as shown in below query

SELECT TOP 1000 [FileName]
      ,[FileData]
  FROM [JPDB].[dbo].[FileTable2]

Now table 'FileTable2' has PDF file data in hexadecimal string, we can convert hexadecimal string into Byte to read this file in C# or any other language

SQL Server 2012 provide better functionality to store files in DB with File Table, to see more detail refer msdn