SQL Server: How to copy a file (pdf, doc, txt...) stored in a varbinary(max) field to a file in a CLR stored procedure?

I ask this question as a followup of this question.

A solution that uses bcp and xp_cmdshell, that is not my desired solution, has been posted here.

I am new to c# (since I am a Delphi developer) anyway I was able to create a simple CLR stored procedure by following a tutorial.

My task is to move a file from the client file system to the server file system (the server can be accessed using remote IP, so I cannot use a shared folder as destination, this is why I need a CLR stored procedure).

So I plan to:

1. store from Delphi the file in a varbinary(max) column of a temporary table
2. call the CLR stored procedure to create a file at the desired path using the data contained in the varbinary(max) field

Imagine I need to move C:\MyFile.pdf to Z:\MyFile.pdf, where C: is a harddrive on local system and Z: is an harddrive on the server. C is in New York, Z is in London and there is no VPN between them, just https connection.

I provide the code below (not working) that someone can modify to make it work? Here I suppose to have a table called MyTable with two fields: ID (int) and DATA (varbinary(max)). Please note it doesn't make a difference if the table is a real temporary table or just a table where I temporarly store the data. I would appreciate if some exception handling code is there (so that I can manage an "impossible to save file" exception).

I would like to be able to write a new file or overwrite the file if already existing.

[Microsoft.SqlServer.Server.SqlProcedure]
public static void VarbinaryToFile(int TableId)
{
   using (SqlConnection connection = new SqlConnection("context connection=true"))
   {
      connection.Open();
      SqlCommand command = new SqlCommand("select data from mytable where ID = @TableId", connection);
      command.Parameters.AddWithValue("@TableId", TableId);
      // This was the sample code I found to run a query
      //SqlContext开发者_JAVA百科.Pipe.ExecuteAndSend(command);
      // instead I need something like this (THIS IS META_SYNTAX!!!):
      SqlContext.Pipe.ResultAsStream.SaveToFile('z:\MyFile.pdf');
   }
}

(one subquestion is: is this approach correct or there is a way to directly pass the data to the CLR stored procedure so I don't need to use a temp table?)

If the subquestion's answer is No, could you describe the approach of avoiding a temp table? So is there a better way then the one I describe above (=temp table + Stored procedure)? A way to directly pass the dataastream from the client application to the CLR stored procedure? (my files can be any size but also very big)

is [there] a way to directly pass the data to the CLR stored procedure so I don't need to use a temp table?

Yes, it is both possible and rather simple to pass a binary file to a SQLCLR stored procedure and have it write the contents to disk, and not require first placing those contents into a table--temporary or real.

[Microsoft.SqlServer.Server.SqlProcedure]
public static void SaveFileToLocalDisk([SqlFacet(MaxSize = -1)] SqlBytes FileContents,
    SqlString DestinationPath)
{
    if (FileContents.IsNull || DestinationPath.IsNull)
    {
        throw new ArgumentException("Seriously?");
    }

    File.WriteAllBytes(DestinationPath.Value, FileContents.Buffer);

    return;
}

Or, since you said that the files are sometimes large, the following should be much easier on memory usage as it makes use of the streaming functionality:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void SaveFileToLocalDiskStreamed(
    [SqlFacet(MaxSize = -1)] SqlBytes FileContents, SqlString DestinationPath)
{
    if (FileContents.IsNull || DestinationPath.IsNull)
    {
        throw new ArgumentException("Seriously?");
    }

    int _ChunkSize = 1024;
    byte[] _Buffer = new byte[_ChunkSize];

    using (FileStream _File = new FileStream(DestinationPath.Value, FileMode.Create))
    {
        long _Position = 0;
        long _BytesRead = 0;

        while (true)
        {
            _BytesRead = FileContents.Read(_Position, _Buffer, 0, _ChunkSize);

            _File.Write(_Buffer, 0, (int)_BytesRead);
            _Position += _ChunkSize;

            if (_BytesRead < _ChunkSize || (_Position >= FileContents.Length))
            {
                break;
            }

        }

        _File.Close();

    }

    return;
}

The assembly containing this code will, of course, need to have a PERMISSION_SET of EXTERNAL_ACCESS.

In both cases, you would execute them in the following manner:

EXEC dbo.SaveFileToLocalDiskStreamed 0x2A20202A, N'C:\TEMP\SaveToDiskTest.txt';

And "0x2A20202A" should give you a file containing the following 4 characters (asterisk, space, space, asterisk):

* *

See http://www.mssqltips.com/tip.asp?tip=1662

Why are you putting these files into database? If you have http/https connection you can upload the file to the server, write into a protected dierctory and create a page to list those files and give a link to download it. If you want to store some extra information you can write it into database. You just need to change the name of file at server side (use a unique name).

After some research I conclude that it makes no sense, it is better to drop the support of 2005 and use 2008 fielstream feature. I can have a conditional logic to choose between 2005 and 2008 and use filestream only for 2005.