如何从SQL Server Filestream打开文档 [英] how to open documents from SQL Server Filestream

查看:89
本文介绍了如何从SQL Server Filestream打开文档的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

大家好,
我已经在网上搜索了几天,却没有发现我认为有帮助的内容.我正在编写我的第一个C#应用程序.该应用程序将科学文章文件(通常为PDF)保存在Filestream中,并将标题,摘要和关键字保存在另一个表中.我将文件和数据保存到SQL Server没问题.该应用程序具有一个搜索表单,该表单允许用户(我的妻子)输入单词,然后该应用程序将在标题,摘要和关键字字段中为其搜索单词.匹配项将返回到ListView控件.然后,她突出显示了要查看的文章,并(应具有功能)单击一个打开按钮,该按钮可打开Adobe Reader(或其他适当的应用程序)并显示该文章. Filestream读/写代码取自Murach的SQL Server 2008 for Developers,2008,P 641,该文件将流保存到数组中.然后可以使用该数组打开适当的应用程序,还是我需要朝另一个方向进行?有问题的方法如下.
谢谢您的协助.
杰夫

VS 2008,C#,SQL Server 2008 R2,.NET 3.51 SP1

Hello All,
I have been searching the web for days and have not found anything I recognize as helpful. I am coding my first C# applicaiton. The application saves scientific article files (typically PDF) in Filestream and title, abstract and keywords in another table. I have no problem saving the files and data to SQL Server. The application has a search form that allows the user (my wife) to enter words for which the application then searches in the title, abstract and keyword fields. Matches are returned to a ListView control. She then highlights the article she wants to view and (supposed to function) clicks an open button that opens Adobe Reader (or other appropriate app) and displays the article. The Filestream read/write code has been taken from Murach''s SQL Server 2008 for Developers, 2008, P 641, which saves the stream to an array. Can the array then be used to open the appropriate app or do I need to proceed in a different direction? The method in question is below.
Thank you for any assistance.
Jeff

VS 2008, C#, SQL Server 2008 R2, .NET 3.51 SP1

private static Byte[] ReadFile(int intArticleID)
{
    /*
     * adapted from Murach's SQL Server 2008 for Developers.
     * 2008. B Syverson & J Murach. P. 641.
    */

    //begin getting file
    SqlConnection connection = null;
    SqlTransaction transaction = null;

    try
    {
        connection = PublicationDB.GetConnection();
        connection.Open();
        transaction = connection.BeginTransaction();

        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.Transaction = transaction;
        command.CommandText =
            "SELECT fsArticleFile.PathName(), " +
            "     GET_FILESTREAM_TRANSACTION_CONTEXT() " +
            "FROM tblArticleFile " +
            "WHERE Article_ID = @Article_ID";

        SqlParameter articleIDParam = new SqlParameter();
        articleIDParam.ParameterName = "@Article_ID";
        articleIDParam.Value = intArticleID;
        command.Parameters.Add(articleIDParam);

        SqlDataReader reader = command.ExecuteReader();
        if (reader.Read() == false)
        {
            throw new Exception("Unable to get path and context for file");
        }
        string path = (string)reader[0];
        byte[] context = (byte[])reader[1];
        int length = context.Length;
        reader.Close();

        //get file handle with read access
        SafeFileHandle handle = OpenSqlFilestream(
            path, DESIRED_ACCESS_READ, OPEN_NO_FLAGS, context, (UInt32)length, 0);

        //set up the input stream from the database
        FileStream sourceStream = new FileStream(handle, FileAccess.Read);
        int blockSize = 1024 * 512;
        byte[] buffer = new byte[blockSize];
        List<byte> fileBytes = new List<byte>();
        int bytesRead = sourceStream.Read(buffer, 0, buffer.Length);
        while (bytesRead > 0)
        {
            bytesRead = sourceStream.Read(buffer, 0, buffer.Length);
            foreach (byte b in buffer)
                fileBytes.Add(b);
        }
        sourceStream.Close();
        transaction.Commit();

        return fileBytes.ToArray();


    }
    catch (Exception exx)
    {
        throw exx;
    }
    finally
    {
        if (connection != null)
            connection.Close();
    }
}

推荐答案

您将无法与其他应用程序一起打开应用程序的内存流或数组.最简单的方法是使用原始文件扩展名将流写入临时文件,然后让外壳程序将其打开.您也可以将sql服务器配置为允许通过共享访问文件流,而不需要保存数据. Google,但您也可以从此处开始: http://www.mssqltips.com/sqlservertip/1489/using-filestream-to-store-blobs-in-the-ntfs-file-system-in-sql-server-2008/ [ ^ ]
You will not be able to open a memory stream or array of your application with an other application. The simplest way is to use the original file extension to write the stream to a temporary file, and let the shell open it. You can also configure the sql server to let filestreams be accessible via share, than you will not need to save the data. Google for it, but you can also start here: http://www.mssqltips.com/sqlservertip/1838/different-ways-to-enable-filestream-feature-of-sql-server-2008/[^] and here http://www.mssqltips.com/sqlservertip/1489/using-filestream-to-store-blobs-in-the-ntfs-file-system-in-sql-server-2008/[^]


我们过去使用的一个简单解决方案是将文件上传为varbinary,然后在需要的时候上传wnload,将其保存到应用程序目录中的临时文件中,然后将其打开.

IE:
A simple solution we have used in the past is to upload the file as varbinary and then when it is time to download it, save it to a temporary file in the application directory then open it.

IE:
outputFile = new System.IO.FileStream("TempFile." + fExtension, System.IO.FileMode.Create, System.IO.FileAccess.Write);
outputFile.Write(fContent, 0, fContent.Length);
outputFile.Close();

System.Diagnostics.Process.Start(Directory.GetCurrentDirectory() + "\\TempFile." + fExtension);


在这种情况下,文件扩展名(即pdf,xls,txt)将与varbinary文件一起保存到服务器. outputFile变量是System.IO.FileStream,因此您可以在代码中从那里开始.对我来说,我使用数据读取器直接从服务器获取基本的Byte [],然后从那里去了.

这是我写了一段时间的简单测试程序,以测试实现.这样,您基本上可以保存任何文件类型,Windows会决定使用该文件打开什么类型.


In this case, the file extension (ie pdf, xls, txt) is saved to the server with the varbinary file. The outputFile variable is a System.IO.FileStream, so you may be able to start from there in your code. For me, I used a datareader to grab the basic Byte[] straight from the server and went from there.

This was a simple test program I wrote a while back to test the implementation. This allows you to save basically any file type and Windows will decide what to open it with.


这篇关于如何从SQL Server Filestream打开文档的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆