Click here to Skip to main content
11,481,838 members (65,292 online)
Click here to Skip to main content
Articles » Database » Database » SQL Server » Downloads
Add your own
alternative version

How To Store Any File into SQL Database

, 22 May 2009 CPOL 114.4K 5K 58
An easy technique to store any file into SQL database
storeMyFiles-Database.zip
storeMyFiles.bak
storeMyFiles-Script.zip
--CREATE DATABASE myFiles
--GO
-- 
--Create table script
USE myFiles
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblBinaryFiles]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblBinaryFiles](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[File] [varchar](max) NOT NULL,
	[Path] [varchar](max) NOT NULL,
	[Ext] [varchar](50) NOT NULL,
	[Size] [bigint] NOT NULL,
	[Binary] [varbinary](max) NOT NULL,
	[Created] [datetime] NOT NULL CONSTRAINT [DF_tblBinaryFiles_Created]  DEFAULT (getdate())
) ON [PRIMARY]
END
GO
-- Create storedProcedure script
-- =============================================
-- Author:		<Author,,Md. Marufuzzaman>
-- Create date: <Create Date,,09-04-09>
-- Description:	<Description,,Return the file name from a given location>
-- =============================================

/*

DECLARE @VAL VARCHAR(MAX) 
EXEC dbo.spGetDocumentName 'E:\Guideline & Programming Tools & Resources\Resource - 2\Microsoft Visual Basic 6.0 Source\myFile.pdf', @VAL OUTPUT
PRINT @VAL

*/
CREATE PROCEDURE [dbo].[spGetDocumentName]
	-- Add the parameters for the stored procedure here
	 @FILE_PATH   VARCHAR(MAX)	
	,@NAME        VARCHAR(MAX) OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

--DECLARE @FILE_PATH VARCHAR(MAX)
DECLARE @FILE_LENGTH INT
DECLARE @FILE_NAME VARCHAR(MAX)

	
	SET @FILE_LENGTH = LEN(@FILE_PATH)

	WHILE (@FILE_LENGTH <> 0)
	BEGIN
		IF SUBSTRING(@FILE_PATH, @FILE_LENGTH, 1) = '\' 
			BEGIN
				SET @FILE_NAME = RIGHT(@FILE_PATH, LEN(@FILE_PATH) - @FILE_LENGTH)
				BREAK
			END
	SET @FILE_LENGTH = @FILE_LENGTH - 1
	END
   
	SET @NAME = @FILE_NAME

	RETURN 

END





GO
/****** Object:  StoredProcedure [dbo].[spGetDocumentNature]    Script Date: 05/20/2009 16:20:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		<Author,,Md. Marufuzzaman>
-- Create date: <Create Date,,09-04-09>
-- Description:	<Description,,Return the nature of the file from a given location>
-- =============================================

/*

DECLARE @VAL VARCHAR(MAX) 
EXEC dbo.spGetDocumentName 'E:\Guideline & Programming Tools & Resources\Resource - 2\Microsoft Visual Basic 6.0 Sourc\myFile.pdf', @VAL OUTPUT
PRINT @VAL

*/
CREATE PROCEDURE [dbo].[spGetDocumentNature]
	-- Add the parameters for the stored procedure here
	 @FILE_PATH   VARCHAR(MAX)	
	,@EXTN       VARCHAR(10) OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

--DECLARE @FILE_PATH VARCHAR(MAX)
DECLARE @FILE_LENGTH INT
DECLARE @FILE_NATURE VARCHAR(10)

	
	SET @FILE_LENGTH = LEN(@FILE_PATH)

	WHILE (@FILE_LENGTH <> 0)
	BEGIN
		IF SUBSTRING(@FILE_PATH, @FILE_LENGTH, 1) = '.' 
			BEGIN
				SET @FILE_NATURE = RIGHT(@FILE_PATH, LEN(@FILE_PATH) - @FILE_LENGTH)
				BREAK
			END
	SET @FILE_LENGTH = @FILE_LENGTH - 1
	END
    
	SET @EXTN = @FILE_NATURE

	RETURN 

END





GO
/****** Object:  StoredProcedure [dbo].[spStoreBinaryFiles]    Script Date: 05/20/2009 16:20:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Md. Marufuzzaman>
-- Create date: <Create Date,, >
-- Description:	<Description,, Insert the Binary data >
-- =============================================
-- EXEC dbo.spStoreDocuments 'C:\eFaxFiles\eFaxPromo.pdf;D:\eFaxFiles\eFaxPromo.pdf;'
CREATE PROCEDURE [dbo].[spStoreBinaryFiles]
	-- Add the parameters for the stored procedure here
 @FILE_PATH	VARCHAR(MAX)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @FILE_LENGTH  BIGINT
	DECLARE @FILE_DATA    VARBINARY(MAX)
	DECLARE @FILE_NAME	  VARCHAR(100)
	DECLARE @DOCUMENT_NAME VARCHAR(100) 
	DECLARE @DOCUMENT_NATURE VARCHAR(5)  

	DECLARE @VAL1 VARCHAR(100)
	DECLARE @VAL2 VARCHAR(100)

DECLARE curDOCUMENTS CURSOR FOR SELECT *  FROM dbo.SPLIT ( ';', @FILE_PATH )
OPEN curDOCUMENTS
FETCH NEXT FROM curDOCUMENTS 
INTO @VAL1,@VAL2

WHILE @@FETCH_STATUS = 0
BEGIN

	IF OBJECT_ID('#ORStable') IS NULL 
		BEGIN
			-- Temporary stored into a hash table for avoid data error     
			CREATE TABLE #ORStable (Length BIGINT, vDocument VARBINARY(MAX))
			
			DECLARE @SQL_QUERY	  NVARCHAR(1000)

			SET @SQL_QUERY= '
			INSERT INTO #ORStable
			SELECT len(bulkcolumn), *
			FROM OPENROWSET(BULK '''+@VAL2+''', SINGLE_BLOB) AS BinaryData'
			exec SP_executesql @SQL_QUERY
 
		END
	
		EXEC dbo.spGetDocumentNature @VAL2, @DOCUMENT_NATURE  OUTPUT
		EXEC dbo.spGetDocumentName @VAL2, @DOCUMENT_NAME  OUTPUT
        
      SELECT TOP 1 @FILE_LENGTH = Length, @FILE_DATA = vDocument FROM #ORStable
	  INSERT INTO dbo.tblBinaryFiles
			(
				 [File]
				,[Path]	
				,[Ext]
				,[Size]
				,[Binary]
			)

	  VALUES(
				 @DOCUMENT_NAME
				,@VAL2
				,@DOCUMENT_NATURE
				,@FILE_LENGTH
				,@FILE_DATA
			)
     
     DROP TABLE dbo.#ORStable

  -- Get the next document.
    FETCH NEXT FROM curDOCUMENTS 
    INTO @VAL1,@VAL2

END

CLOSE curDOCUMENTS
DEALLOCATE curDOCUMENTS
 

END

GO
-- Create Function script

CREATE FUNCTION [dbo].[SPLIT] 
   (  @DELIMITER VARCHAR(5), 
      @LIST      VARCHAR(MAX)
   ) 
   RETURNS @TABLEOFVALUES TABLE 
      (  ROWID   SMALLINT IDENTITY(1,1), 
         [VALUE] VARCHAR(MAX) 
      ) 
AS 
   BEGIN
    
      DECLARE @LENSTRING INT 
 
      WHILE LEN( @LIST ) > 0 
         BEGIN 
         
            SELECT @LENSTRING = 
               (CASE CHARINDEX( @DELIMITER, @LIST ) 
                   WHEN 0 THEN LEN( @LIST ) 
                   ELSE ( CHARINDEX( @DELIMITER, @LIST ) -1 )
                END
               ) 
                                
            INSERT INTO @TABLEOFVALUES 
               SELECT SUBSTRING( @LIST, 1, @LENSTRING )
                
            SELECT @LIST = 
               (CASE ( LEN( @LIST ) - @LENSTRING ) 
                   WHEN 0 THEN '' 
                   ELSE RIGHT( @LIST, LEN( @LIST ) - @LENSTRING - 1 ) 
                END
               ) 
         END
          
      RETURN 
      
   END 

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Md. Marufuzzaman
Architect
Bangladesh Bangladesh
A highly experienced leader with successful track record of software development, product innovations, brand management and corporate communication etc. Some successful product innovations have also achieved and awards “Most Valuable Professional” (MVP) at 2010 and 2011 by codeproject.com and also selected as a mentor of codeproject.com. Published over 100 technical articles in various software development resource sites (i.e., codeprojetc.com, Microsoft MSDN, and IEEE & IBM (In progress)) and various IT Forums, Blogs etc.

Over ten years of professional experiences in ICT field having extensive experience in formulating corporate vision and long term strategy. Leading development related functions including design, development, services, data management and analytics, customer experience management, content services, digital analytics and optimization.

An individual with results-driven approach and relentless in pursuit of excellence from a business and organizational standpoint. Believes in transparency, commitment and teamwork.

Expertise: Software/Solution Architect, SaaS platform base application, Large scale win32/web based business software solutions, enterprise applications, integration, etc.

Technologies/Tools: Microsoft.Net version 05/08/10/12, Microsoft SQL Server version 7/2K/05/08/12 , Oracle version 10/11, MySql version 5.1, 5.5, PS2, Visual C#, R, VB.NET, ASP.NET, PHP, API, MVC, WebAPI , Add-In Visual Basic etc.,. I have also more than two years’ of strong experience in mobile-VAS (platform development).

Points of Interest: Technology and research & development especially focused on business functionalities and social business areas as well, few stuff including:

1.R&D on new techniques as required to increase business revenue.
2.Urban and rural sectors to improve people’s lifestyle, better medical facilities, education, social business etc., using innovative technical solutions.
3.Research and innovative product development.
4.Etc.
Follow on   Twitter   LinkedIn

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150520.1 | Last Updated 23 May 2009
Article Copyright 2009 by Md. Marufuzzaman
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid