Retrieve stored Word, PDF or Excel binary file from SQL Server 2008 database and display to user

I have worked through the development process and I can now upload a document or image and store it to a SQL 2008 database. The image is stored in a field of type varbinary (MAX). My final step is I will supply a user with another asp.net page. A link will display in a gridview or something similar. I will allow the user to pass the value to the final page and that is where I want to display the Word, PDF or Excel document. I am hoping that I can open the document within the browser but I will also be ok if the user has to save before opening. The strong preference by the business community is to open directly and through the browser. I am very, very new to ASP.Net. Luckily I am doing this application in .Net 4 (Visual Studio 2010) so much of the code needed has been generated. I am not the best code writer in the world so any help as always is very appreciated. I have seen only pieces of examples of displaying when it's an image but not a binary like a PDF.
derrekdeveloperEnterprise Solution ArchitectAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lazyberezovskyCommented:
Below is very simple storing and retrieving file from database.
Here is example how to write to response different file types: http://weblogs.sqlteam.com/randyp/archive/2008/11/20/60771.aspx
// Saving to database:
string connectionString; // Get connection string.
string insertCommandText; // E.g. INSERT INTO TableName VALUES (@file)
string fileName; // File to be saved.

using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(insertCommandText, conn))
{
    cmd.Parameters.Add(new SqlParameter("@file", File.ReadAllBytes(fileName)));                
    conn.Open();
    cmd.ExecuteNonQuery();
}

// Reading *.doc from database (and writing to response):
string connectionString;
string selectCommandText; 
string fileName;

using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(selectCommandText, conn))
{
    conn.Open();

    using (BinaryWriter writer = new BinaryWriter(Response.OutputStream))            
        writer.Write((byte[])cmd.ExecuteScalar());            

    Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
    Response.ContentType = "application/ms-word";
    Response.End();
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
derrekdeveloperEnterprise Solution ArchitectAuthor Commented:
Thanks lazyberezovsky. However, I think this is a C# solution. I need a VB.net solution.
0
lazyberezovskyCommented:
No problem. It's .Net
For future: http://converter.telerik.com/

And add VB.Net zone to question, when VB code expected :)
Dim connectionString As String
' Get connection string.
Dim insertCommandText As String
' E.g. INSERT INTO TableName VALUES (@file)
Dim fileName As String
' File to be saved.
Using conn As New SqlConnection(connectionString)
	Using cmd As New SqlCommand(insertCommandText, conn)
		cmd.Parameters.Add(New SqlParameter("@file", File.ReadAllBytes(fileName)))
		conn.Open()
		cmd.ExecuteNonQuery()
	End Using
End Using

' Reading *.doc from database (and writing to response):
Dim connectionString As String
Dim selectCommandText As String
Dim fileName As String

Using conn As New SqlConnection(connectionString)
	Using cmd As New SqlCommand(selectCommandText, conn)
		conn.Open()

		Using writer As New BinaryWriter(Response.OutputStream)
			writer.Write(DirectCast(cmd.ExecuteScalar(), Byte()))
		End Using

		Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName)
		Response.ContentType = "application/ms-word"
		Response.[End]()
	End Using
End Using

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.