SQL Server:如何将存储在varbinary(max)字段中的文件(pdf,doc,txt ...)复制到CLR存储过程中的文件?
我作为。
I ask this question as a followup of this question.
使用bcp和xp_cmdshell的解决方案,不是我想要的解决方案,已在此处发布。
A solution that uses bcp and xp_cmdshell, that is not my desired solution, has been posted here.
无论如何,我还是c#的新手(因为我是Delphi开发人员),因此我可以按照教程创建简单的CLR存储过程。
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.
我的任务是将文件从客户端文件系统移动到服务器文件系统(可以使用远程IP访问服务器,因此我不能将共享文件夹用作目标,这就是为什么我需要CLR存储过程的原因)。
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).
所以我打算:
- 从Delphi存储文件临时表的varbinary(max)列
- 调用CLR存储过程在以下位置创建文件使用varbinary(max)字段中包含的数据找到所需的路径
想象一下,我需要将C:\MyFile.pdf移至Z:\MyFile.pdf,其中C:是本地系统上的硬盘驱动器,Z:是服务器上的硬盘驱动器。 C在纽约,Z在伦敦,它们之间没有VPN,只有https连接。
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.
我在下面提供了代码(不起作用),有人可以修改使它工作?在这里,我假设有一个名为MyTable的表,该表具有两个字段:ID(int)和DATA(varbinary(max))。请注意,如果该表是一个真正的临时表,或者只是我临时存储数据的表,那都没有关系。如果有一些异常处理代码,我将不胜感激(以便我可以管理文件无法保存异常)。
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');
}
}
(一个子问题是:这种方法是正确的还是有一种直接将数据传递到CLR存储过程的方法,所以我不需要使用临时表?)
如果子问题的答案是否,您能描述避免使用临时表的方法吗?因此,有没有一种比我上面描述的方法更好的方法(=临时表+存储过程)?一种将数据流从客户端应用程序直接传递到CLR存储过程的方法? (我的文件可以是任何大小,但也可以很大)
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)
经过研究,我得出结论认为这没有任何意义,最好放弃对2005的支持并使用2008 fielstream功能。我可以有条件逻辑来选择2005和2008之间,并且仅将文件流用于2005。
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.