c# - SQL Server : How to copy a file (pdf, doc, txt...) 存储在 CLR 存储过程中的文件的 varbinary(max) 字段中？

Question

我问这个问题是对 this question 的跟进.

已发布使用 bcp 和 xp_cmdshell 的解决方案，这不是我想要的解决方案 here .

我是 c# 的新手(因为我是 Delphi 开发人员)，无论如何我都能够按照教程创建一个简单的 CLR 存储过程。

我的任务是将文件从客户端文件系统移动到服务器文件系统(服务器可以使用远程 IP 访问，所以我不能使用共享文件夹作为目标，这就是为什么我需要一个 CLR 存储过程) .

所以我打算:

1. 从 Delphi 中将文件存储在临时表的 varbinary(max) 列中
2. 调用 CLR 存储过程使用 varbinary(max) 字段中包含的数据在所需路径创建文件

假设我需要将 C:\MyFile.pdf 移动到 Z:\MyFile.pdf，其中 C: 是本地系统上的硬盘驱动器，Z: 是服务器上的硬盘驱动器。 C 在纽约，Z 在伦敦，他们之间没有 VPN，只有 https 连接。

我提供了下面的代码(不工作)，有人可以修改它以使其工作吗？在这里，我假设有一个名为 MyTable 的表，其中包含两个字段:ID (int) 和 DATA (varbinary(max))。请注意，如果该表是真正的临时表或只是我临时存储数据的表，这没有什么区别。如果有一些异常处理代码(这样我就可以管理“无法保存文件”异常)，我将不胜感激。

我希望能够写入新文件或覆盖现有文件。

[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 存储过程的方法？ (我的文件可以是任何大小但也可以很大)

Answer 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?

是的，将二进制文件传递给 SQLCLR 存储过程并将其内容写入磁盘是可能的，而且相当简单，而不需要首先将这些内容放入表中——无论是临时的还是真实的。

[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;
}

或者，既然你说文件有时很大，下面的代码在内存使用上应该更容易，因为它使用了流功能:

[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;
}

当然，包含此代码的程序集需要具有 EXTERNAL_ACCESS 的 PERMISSION_SET。

在这两种情况下，您将按以下方式执行它们:

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

“0x2A20202A”应该给你一个包含以下 4 个字符(星号、空格、空格、星号)的文件:

* *