在SQL Server中查看存储在Varbinary（MAX）中的文件

Let's say I inserted a file into a varbinary(max) field like so:

假设我将文件插入到varbinary(max)字段中,如下所示:

CREATE TABLE myTable
(
      FileName nvarchar(60),
      FileType nvarchar(60), 
      Document varbinary(max)
)
GO

INSERT INTO myTable(FileName, FileType, field_varbinary)
    SELECT 
        'Text1.txt' AS FileName, 
        '.txt' AS FileType,
        * 
    FROM 
        OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document
GO

Of course my file now looks like this:

当然我的文件现在看起来像这样:

0xFFD8FFE000104A46494600010101004800....

Is there a simple and elegant way to retrieve this file?

是否有一种简单而优雅的方法来检索此文件?

My preference is to open it right away in a temp folder rather than saving and then viewing and deleting. In MS Access, this is as simple as using an Attachment field and double clicking to upload/download.

我的偏好是立即在临时文件夹中打开它,而不是保存,然后查看和删除。在MS Access中,这就像使用“附件”字段并双击上载/下载一样简单。

2 个解决方案

#1

Since there is no built-in functionality in SSMS for this task I usually write a simple LINQPad script that extracts my varbinary column and writes it to the file system.

由于SSMS中没有用于此任务的内置功能,因此我通常编写一个简单的LINQPad脚本来提取我的varbinary列并将其写入文件系统。

Something like this:

像这样的东西:

var results = from p in myTable 
                       where p.ID == ... //your condition here
                       select p;

foreach (var item in results)
{ 
    File.WriteAllBytes("C:\\" + item.FileName + item.FileType , item.Document.ToArray());
}

#2

I am working with C# and ASP.NET, and I ended up doing this using a Generic Handler, later linking to it elsewhere in my website project:

我正在使用C#和ASP.NET,最后我使用Generic Handler执行此操作,稍后在我的网站项目中的其他位置链接到它:

public class ImageProvider : IHttpHandler {
    public string connString = "...";

    public void ProcessRequest(HttpContext context)
    {
        context.Response.ContentType = "image/jpeg";

        string sqlSelectQuery = "select img from table"
        SqlConnection conn = new SqlConnection(connString);
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlSelectQuery, conn);

        byte[] img = (byte[])cmd.ExecuteScalar();
        context.Response.BinaryWrite(img);

    }

#1