SQL Server：如何将存储在 varbinary(max) 字段中的文件（pdf、doc、txt...）复制到 CLR 存储过程中的文件？

Question

我问这个问题是 这个问题。

使用 bcp 和 xp_cmdshell 的解决方案（这不是我想要的解决方案）已发布 此处。

我是 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

有没有办法直接将数据传递到 CLR 存储过程，这样我就不需要使用临时表？

是的，将二进制文件传递给 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;
}

包含此代码的程序集当然需要有一个 PERMISSION_SETEXTERNAL_ACCESS 的代码>。

在这两种情况下，您都可以按以下方式执行它们：

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

“0x2A20202A”应该为您提供一个包含以下 4 个字符（星号、空格、空格、星号）的文件：

* *

Answer 2

请参阅http://www.mssqltips.com/tip.asp?tip=1662

Answer 3

为什么要把这些文件放入数据库？如果您有 http/https 连接，您可以将文件上传到服务器，写入受保护的目录并创建一个页面来列出这些文件并提供下载链接。如果你想存储一些额外的信息，你可以将其写入数据库。您只需要在服务器端更改文件名（使用唯一的名称）。

Answer 4

经过一番研究，我得出结论，这没有意义，最好放弃 2005 的支持并使用 2008 fielstream 功能。我可以有一个条件逻辑来在 2005 年和 2008 年之间进行选择，并仅在 2005 年使用文件流。