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