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 am asking this question as the next one this question .
A solution using bcp and xp_cmdshell, not my desired solution, was posted here .
I am new to C # (since I am a Delphi developer), anyway I was able to create a simple CLR storage procedure by following the tutorial.
My task is to move a file from the client's file system to the server's file system (the server can be accessed using the remote IP, so I can't use the shared folder as the destination, so I need a CLR stored procedure).
So, I am planning:
- store from Delphi file in varbinary (max) column of temporary table
- calling a CLR stored procedure to create a file at the desired path using the data contained in the varbinary (max) field
Imagine that I need to move C: \ MyFile.pdf to Z: \ MyFile.pdf, where C: hard drive on the local system and Z: hard drive on the server. C is in New York, Z is in London and there is no VPN between them, only an https connection.
I provide below code (doesn't work) that someone can change to make it work? Here I am assuming to have a MyTable with two fields: ID (int) and DATA (varbinary (max)). Note that it doesn't matter if the table is a real temporary table or just a table where I am temporarily storing data. I would appreciate it if there was some kind of exception handling code in there (so that I can manage the "unable to save file" exception).
I would like to be able to write a new file or overwrite the file if it already exists.
[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.Pipe.ExecuteAndSend(command);
// instead I need something like this (THIS IS META_SYNTAX!!!):
SqlContext.Pipe.ResultAsStream.SaveToFile('z:\MyFile.pdf');
}
}
(one subquery: is this approach correct or is there a way to directly pass data to a CLR stored procedure so I don't need to use a temporary table?)
If the answer to the subquery is "No", could you please describe an approach for eliminating a temporary table? So is there a better way than the one I describe above (= temp table + Stored Procedure)? Is there a way to directly pass information flow from a client application to a CLR stored procedure? (my files can be any size, but also very large)
a source to share
is there [there] a way to directly pass data to a CLR stored procedure so I don't need to use a temporary table?
Yes, it is possible and quite simple to pass a binary file to an SQLCLR stored procedure and write it to disk without having to first put them in 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 files are sometimes large, the following should be much easier to use memory as it uses 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 must of course have PERMISSION_SET
of EXTERNAL_ACCESS
.
In both cases, you will execute them like this:
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):
* *
a source to share
Why are you putting these files into the database? If you have an http / https connection, you can upload the file to the server, write to the secure dierctory, and create a page to display these files and provide a download link. If you want to store additional information, you can write it to the database. You just need to change the filename on the server side (use a unique name).
a source to share