MSSQL: How to copy a file (pdf, doc, txt...) stored in a varbinary(max) field to a file in a CLR sto
- by user193655
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: stackoverflow.com/questions/828749/ms-sql-server-2005-write-varbinary-to-file-system (sorry i cannot post a second hyperlink since my reputation is les than 10).
I am new to c# (since I am a Delphi developer) anyway I was able to create a simple CLR stored procedures 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.
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.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 coorect 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)
