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 De

Solution 1:

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]
publicstaticvoidSaveFileToLocalDisk([SqlFacet(MaxSize = -1)] SqlBytes FileContents,
    SqlString DestinationPath)
{
    if (FileContents.IsNull || DestinationPath.IsNull)
    {
        thrownew 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]
publicstaticvoidSaveFileToLocalDiskStreamed(
    [SqlFacet(MaxSize = -1)] SqlBytes FileContents, SqlString DestinationPath)
{
    if (FileContents.IsNull || DestinationPath.IsNull)
    {
        thrownew ArgumentException("Seriously?");
    }

    int _ChunkSize = 1024;
    byte[] _Buffer = newbyte[_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):

* *

Solution 2:

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

Solution 3:

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).

Solution 4:

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.