Click here to Skip to main content
12,623,882 members (31,599 online)
Click here to Skip to main content
Articles » Database » Database » SQL Server » Downloads

Stats

141.7K views
5.9K downloads
60 bookmarked
Posted

How To Store Any File into SQL Database

, 22 May 2009 CPOL
An easy technique to store any file into SQL database
--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 well 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.

You may also be interested in...

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