Jeffrey Smith

Jeffrey Smith

  • NA
  • 1
  • 4.8k

C# WinForm-how to open documents from SQL Server filestream

Jul 31 2012 1:15 PM

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


Answers (1)