Solved

How to stream from varbinary(max) field to browser

Posted on 2012-04-09
10
1,039 Views
Last Modified: 2012-06-27
I have a SQL database with fairly large varbinary(max) fields that are binary documents. I need to read that data from the SQL database and stream it down to a user's browser. I can open the whole field into memory and stream from there but some of the files are going to tax the server so I really want to sequentially access the data. I have found code on Microsoft that shows how to sequentially pull the data from the SQL database but I can't find any code to the sequentially push that down to the browser. The binary data is different document types such as pdf, doc files, xls files etc which I know at the time I pull them so I can set the content type. I have a simple aspx page that the user calls with a querystring identifying the ID of the document to pull. Can someone help me figure out how to pull this data out sequentially and not kill the server? thanks
Terry
0
Comment
Question by:VerticalOne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
10 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 37826958
how about writing the document to a file then forwarding the user to the url of the file?
0
 

Author Comment

by:VerticalOne
ID: 37827699
I really don't want to waste the resources on the server to make another copy of the file that is already in database. What I am searching for is an ability to stream the binary content directly out of the SQL database to a browser. Microsoft has some sample code how to use a SQLDataReader in sequential access mode to pull data in chunks rather than having to pull the whole file into memory. This is what I am looking for but I have been unable to find any direction of how to hook that to a stream down to the browser.
0
 
LVL 18

Expert Comment

by:deighton
ID: 37827989
what I've done before in asp.net for images is stuff like

in page_load()

Response.Clear()
dim b() as Byte = GenerateImage(....etc)
Response.ContentType = "image/png"
Response.BinaryWrite(b)
Response.end()


you'd need to change ContentType to the correct one for pdf

instead of GenerateImage, you'd read in your pdf and convert it to binary data, you might need memoryStream etc to do that - I forget how to do it exactly
0
Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

 
LVL 18

Expert Comment

by:deighton
ID: 37827997
the whole file would end up in memory though, for a short time.
0
 

Author Comment

by:VerticalOne
ID: 37845217
I still really need a solution to this issue but have not been able to find any sample code that allows me to sequentially pull from the db and send down to a browser.
0
 
LVL 18

Expert Comment

by:deighton
ID: 37850098
so you want to obtain one block of data, then send to the browser, then the next block and send to the browser etc?  I don't know how to do that - sorry.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 37873579
"Can someone help me figure out how to pull this data out sequentially and not kill the server?"

How big are these files and which server is killed? I think you have already options from comments above and here's one more suggestion for large files - use a file storage to store your download files (we use Samba fileserver for exactly that purpose and reason behind a busy ecommerce website with SQL backend) and store only the path to the file in SQL table then when user clicks on link to download the file (pdf, image, ISO, etc...) will get it from your file server not killing your DB or Web servers.
As much as you want a magic solution I don't think there's such thing and even though my suggestion means code changes, sometimes you must do whatever you must do instead of killing servers.
http://www.samba.org/
http://www.melbpc.org.au/pcupdate/2403/2403article7.htm
0
 
LVL 40

Accepted Solution

by:
lcohan earned 500 total points
ID: 37873628
Please see if this sample about .NET " SequentialAccess

Provides a way for the DataReader to handle rows that contain columns with large binary values BLOBs. Rather than loading the entire row, SequentialAccess enables the DataReader to load data as a stream.
" hels you:

http://www.akadia.com/services/dotnet_read_write_blob.html
0
 

Author Closing Comment

by:VerticalOne
ID: 37912231
Thanks, this got me working just fine.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question