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
