• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2703
  • Last Modified:

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.
0
derrekdeveloper
Asked:
derrekdeveloper
  • 2
1 Solution
 
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
 
derrekdeveloperAuthor 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now