The question comes up every so often about storing image or other binary files in a database.  Among the many reasons given for using a database to store images or files is the ability to search for descriptions and other details that may apply to an image. Another reason is that files stored on a file server may become corrupted.

In 2003 I had to store files in a database to reduce email bandwidth consumption for a small not-for-profit organization.  The organization had to send out thousands of emails every month, and in order to save costs we stored email attachments in the database.  We then provided an html link to a web page that would allow the user to download the file if they were interested. You see this technique used frequently now. The code used to convert the file to a blob was strictly C#.NET as CLR stored procedures were not available in SQL Server 2000.  This part of a two part article will cover the storage of the file binaries using a CLR stored procedure to convert the file to a binary stream. Part two will cover the restoration of the stored data to a file.

The solution takes several steps.  We need to make infrastructure changes, building the Assembly housing the function used to convert the file to a binary stream, and then building the SQL Server objects to support the solution. We will walk through all of the steps below.

Infrastructure changes

We need to configure the files and folders that will be accessed by the SQL Server You need to ensure that you, as a user, have permissions to read and write to the source folder where the source file is stored.  You’ll ultimately have to have the same permissions to the target folder when you want to restore the file. If you intend to use a service account for the processes associated with the conversion, you will need to ensure the service account has the required permissions to read the file.

I will assume the reader knows how to do this.

We also need to make infrastructure changes to the SQL Server. You will need to enable CLR stored procedures. To do this run the following query:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

You will need to grant your user external access using the following query:

USE master;
GRANT EXTERNAL ACCESS ASSEMBLY TO [SBHR1\enotheisen]

Build the Assembly using C#.NET

We need to build a C# assembly. Here is the code we will use. This code will read a file and convert its contents into the binary equivalent of bits and bytes.

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System;
using System.IO;
public class FileProcessor
    {
    [SqlFunction()]
    public static SqlBytes GetBytesFromFile(string sFilePath)
        {
        System.IO.FileStream fs = System.IO.File.Open(sFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
        long lngLen = fs.Length;
        byte[] abytBuffer = new byte[(int)lngLen - 1];
        fs.Read(abytBuffer, 0, (int)lngLen - 1);
        fs.Close();
        SqlBytes b = new SqlBytes(abytBuffer);
        return b;
        }
    }

We need to compile the assembly using Visual C# Express or the command line compiler installed with the .NET Framework.  To use the command line compiler create a file text file with the code above named SqlFileProcessing.cs. Next, open the command line window cmd.exe

You need to change directories to “C:\Windows\Microsoft.NET\Framework64\v4.0.30319”. If you are doing the work on a x86 machine leave off the 64 in the Framework title. Now run this from the command line:

csc /target:library /out:C:\temp\sqlfileprocessor.dll c:\temp\SqlFileProcessor.cs

Copy the dll file to “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL”

For simplicity's sake I have included the source file and a dll as a download in the Resources section at the end of this article.

Build the SQL Server objects supporting the solution.

Let’s start with the table that will store the data:

IF EXISTS ( SELECT
                *
              FROM
                master.sys.tables
              WHERE
                name = 'FileStorage' )
  DROP TABLE FileStorage;
GO
CREATE TABLE FileStorage
  (
    [ID] INT IDENTITY(1, 1)
  , [Filename] VARCHAR(100) NOT NULL
  , [FileBlob] VARBINARY(MAX) NOT NULL
  , [CreatedDate] DATETIME NOT NULL
  , [ContentType] VARCHAR(50) NOT NULL
  , [ContentSize] BIGINT NOT NULL
  );
   GO
CREATE CLUSTERED INDEX cl_filestorage ON FileStorage (ID);
GO
CREATE NONCLUSTERED  INDEX csindx_filestorage ON FileStorage(Filename);
GO
ALTER DATABASE FileStorage SET TRUSTWORTHY ON;
GO

We next need to register the assembly. Your assembly must be copied to the file system on your SQL Server. If you use a different path, change it in the code below. Note that the permission set needs to be set to External_Access to work with objects that exist outside of the SQL Server instance.

IF EXISTS ( SELECT
                *
              FROM
                sys.assembly_modules
              WHERE
                assembly_method = 'GetBytesFromFile' )
  DROP FUNCTION GetBytesFromFile;
IF EXISTS ( SELECT
                *
              FROM
                sys.assemblies
              WHERE
                name = 'SqlFileProcessor' )
  DROP ASSEMBLY SqlFileProcessor;
GO
CREATE ASSEMBLY SqlFileProcessor AUTHORIZATION [Domain\user_name]
FROM 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\SqlFileProcessor.dll'
WITH PERMISSION_SET =EXTERNAL_ACCESS;
GO

Once the assembly is registered, we need to create the function that will call the method in the assembly. This function will load the file and return the binary stream of data.

CREATE FUNCTION GetBytesFromFile
  (
    @FileName NVARCHAR(128)
  )
RETURNS VARBINARY(MAX)
  WITH EXECUTE AS 'SBHR1\enotheisen'
AS EXTERNAL NAME
  SqlFileProcessor.FileProcessor.GetBytesFromFile;
GO

We will use a stored procedure to call the function and insert the record into the table with the file contents. This procedure extracts the extension from the file and inserts that as well into the row.

CREATE PROCEDURE SaveFileToStorage
  (
    @Filename NVARCHAR(100)
  )
AS
  DECLARE
    @FileBlob VARBINARY(MAX)
  , @CreatedDate DATETIME
  , @ContentType VARCHAR(50)
  , @ContentSize BIGINT;
  DECLARE @filePathReverse VARCHAR(128) = REVERSE(@Filename);
  SET @ContentType = REVERSE(LEFT(@filePathReverse,
                                  PATINDEX('%.%', @filePathReverse) - 1));
  SELECT
      @FileBlob = dbo.GetBytesFromFile(@Filename)
    , @CreatedDate = GETDATE()
    , @ContentSize = LEN(@FileBlob);
  BEGIN TRANSACTION;
  BEGIN TRY;
    INSERT INTO FileStorage
        SELECT
            @Filename
          , @FileBlob
          , @CreatedDate
          , @ContentType
          , @ContentSize;
  END TRY
  BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
      , ERROR_SEVERITY() AS ErrorSeverity
      , ERROR_STATE() AS ErrorState
      , ERROR_PROCEDURE() AS ErrorProcedure
      , ERROR_LINE() AS ErrorLine
      , ERROR_MESSAGE() AS ErrorMessage;
    IF @@TRANCOUNT > 0
      BEGIN
        ROLLBACK TRANSACTION;
      END;
  END CATCH;
  IF @@TRANCOUNT > 0
    BEGIN
      COMMIT TRANSACTION;
    END;

Let’s test the solution by passing a file in the table. If you wish to use a different picture, change the name of the file to one that’s stored on your SQL Server.

DECLARE @FileName VARCHAR(128)= 'C:\Users\enotheisen\Pictures\Iceberg.jpg';
EXEC SaveFileToStorage @FileName;
SELECT
    *
  FROM
    FileStorage;

You should see something like this in SSMS:

That’s the whole process. You can call this stored procedure each time you wish to load a binary file into your database.

In this article I have shown how to store the binaries of a file in a database.  In part two, I’ll show how to restore the file to its original form.