SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Retrieving a Binary File from Your Database

By Eric Notheisen,

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[i] = Blob[i];
                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!

 

Resources:

SqlFileProcessor.dll | FileProcessor.txt
Total article views: 4534 | Views in the last 30 days: 3
 
Related Articles
FORUM

Creating assembly

I can no longer create assemblies on the sql server

FORUM

Cannot create assembly referencing system.core v3.5.0.0

Fail to create assembly using Linq

FORUM

Create GAC Assembly

How To Create Assembly from GAC and how to Deal with referenced assembly

FORUM

Errors on creating assembly from global dlls

Errors on creating assembly from global dlls

FORUM

create function

create function

Tags
blob storage    
file conversion    
sqlclr    
 
Contribute