This is the second part of a two part article on working with file binaries in a database. In part one of this article I presented a Common Language Runtime (CLR) user defined function (UDF) to be used to capture the binary data from a file on a file server. I also presented the methods used to create the dll from a C# source file. Included in the article were the methods used to register the assembly with Sql Server and a stored procedure to store the relevant data about the file.

In this article I will address the additional CLR UDF used to retrieve the binary data from a table and restore the binary blob to its original state. 

The process of adding a new user defined function is relatively easy. Add the function SaveFileToDisk to the source file you created in part one.  Remember that C# code is case sensitive. Then rebuild the assembly with the additional function.  Follow the instructions in part one, on compiling the assembly. This is the new source file code for the CLR user defined functions.

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;
        }
    [SqlFunction()]
    public static int SaveFileToDisk(SqlString sFileName, SqlBytes Blob)
        {
        using (System.IO.FileStream file = new System.IO.FileStream(sFileName.Value, System.IO.FileMode.CreateNew, System.IO.FileAccess.Write))
            {
            try
                {
                byte[] Buffer = new byte[Blob.Length];
                for (int i = 0; i < Blob.Length; i++) Buffer = Blob;
                System.IO.MemoryStream stream = new System.IO.MemoryStream(Buffer);
               
                byte[] bytes = new byte[stream.Length];
                stream.Read(bytes, 0, bytes.Length);
             
                file.Write(bytes, 0, bytes.Length);
                stream.Close();
                return 1;
                }
            catch (System.Exception ex)
                {
                using (System.IO.StreamWriter sw = new System.IO.StreamWriter(@"C:\temp\error.txt", true))
                    {
                    sw.Write(ex.Message);
                    }
                return 0;
                }
             }
        }
    }
  

Copy the dll created above to the appropriate location on the file server. In my example I placed it in 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\SqlFileProcessor.dll'

Since you are making change to the source code and recompiling the assembly you will need to re-register the Assembly.  In order to do this you will first need to deregister and then register the assembly as well as recreate the user defined functions in SQL Server Management Studio.

If Exists(select * from sys.Assembly_modules where Assembly_Method='GetBytesFromFile')
 Drop function dbo.GetBytesFromFile
If Exists(select * from sys.Assembly_modules where Assembly_Method='SaveFileToDisk')
 DROP FUNCTION dbo.SaveFileToDisk
IF Exists(Select * from sys.assemblies where Name = 'SqlFileProcessor')
 DROP ASSEMBLY SqlFileProcessor
GO

Below is the code to create the objects. Note the EXECUTE AS clause in the function and procedure. You need to replace the domain and username values with valid objects from your environment.

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
CREATE Function GetBytesFromFile( @FileName NVarchar(128)) RETURNs varbinary(max) WITH EXECUTE AS 'domain\user_name'
 AS EXTERNAL NAME SqlFileProcessor.FileProcessor.GetBytesFromFile;
GO
CREATE Function SaveFileToDisk( @sFileName Nvarchar(128), @Blob varbinary(max))Returns int WITH EXECUTE AS 'domain\user_name'
 AS EXTERNAL NAME SqlFileProcessor.FileProcessor.SaveFileToDisk
GO

Once to you’ve completed these task, review your permissions relative to the source and target folders and test your solution using code similar to the below code.

declare  @filePath varchar(128)=<your source file path plus filename>'
Declare  @filePathReverse varchar(128) = REVERSE(@filePath)
Declare  @FileName varchar(128) = left(@filePathReverse, PatIndex('%\%', @filePathReverse)-1)
EXEC FileStorage.dbo.SaveFileToStorage @FilePath
set @fileName='<Your target file path>' + Reverse(@fileName)
declare @Blob varbinary(max)
select @Blob=FileBlob from FileStorage where FileName=@FilePath
Execute AS user = 'domain\user_name'
Exec dbo.SaveFileToDisk @FileName, @Blob

That's it. You shold have a file back on your filesystem.

In these two articles I have provided methodologies to store a file’s binaries in a database table and restore those binaries to their original form.  I have included the source file for the CLR user defined functions and a compiled dll.  Have fun!