Click here to Skip to main content
16,016,290 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
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

C#
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();
    }
}
Posted

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:
C#
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);

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.
 
Share this answer
 
Comments
JSmith8242 31-Jul-12 17:05pm    
Dan,
Thank you. I was able to get a quick working solution with your code. Next I will next try using the Filestream Share name and Win32 API to pull directly from Filestream and open. One thing that wasn't apparent by many articles and posts was that Win32 API uses the Filestream share name. I didn't realize that mssqltips isn't returned in a google search. Again, than you very much, guys.
-Jeff
Dan Steuer 31-Jul-12 17:23pm    
Glad I helped get you in the right direction.
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/[^]
 
Share this answer
 
v2
Comments
JSmith8242 31-Jul-12 17:06pm    
Zoltan,
Thank you. Next I will next try using the Filestream Share name and Win32 API to pull directly from Filestream and open. One thing that wasn't apparent by many articles and posts was that Win32 API uses the Filestream share name. I didn't realize that mssqltips isn't returned in a google search. Again, than you very much, guys.
-Jeff
Zoltán Zörgő 1-Aug-12 3:16am    
You are welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900