.net IHTTPHandler Streaming SQL Binary Data

I am trying to implement an ihttphandeler for streaming files. files may be tiny thumbnails or gigantic movies

the binaries r stored in sql server

i looked at a lot of code online but something does not make sense

isn't streaming supposed to read the data piece by piece and move it over the line?

most of the code seems to first read the whole field from mssql to memory and then use streaming for the output writing

wouldn't it b more efficient to actually stream from disk directly to http byte by byte (or buffered chunks?)

heres my code so far but cant figure out the correct combination of the sqlreader mode and the stream object and the writing system

Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
     context.Response.BufferOutput = False
    Dim FileField=safeparam(context.Request.QueryString("FileField"))
     Dim FileTable=safeparam(context.Request.QueryString("FileTable"))
     Dim KeyField=safeparam(context.Request.QueryString("KeyField"))
     Dim FileKey=safeparam(context.Request.QueryString("FileKey"))                 
    Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("Main").ConnectionString)
        Using command As New SqlCommand("SELECT " & FileField & "Bytes," & FileField & "Type FROM " & FileTable & " WHERE " & KeyField & "=" & FileKey, connection)
            command.CommandType = Data.CommandType.Text

end using end using end sub

please be aware that this sql command also returns the file extension (pdf,jpg,doc...) in the second field of the query

thank you all very much

EDIT:

i managed to find some more code, and now the page shows up intermittently. sometimes it brings the pdf file and sometimes it does not

i cant understand the pattern here

i think that the main problem is when the request is from a different page and i click on "show in new tab" then it never worked. when i do "show in new window" it mostly works but not always.

btw. the code ALWAYS runs. never breaks or errors or anything like that. it runs like a good boy from start to end on each request

sometimes IE after a long time, gives me a message (from the new tab) "There is a problem with Adobe/Acrobat reader. Please exit Adobe Acrobat/Reader and try again."

what can the matter be?

heres my current code

Shared Sub ProccessMedia(ByVal context As HttpContext)
    If CurPerson Is Nothing OrElse Not CurPerson.PersonExts.FirstOrDefault.LetAllFiles Then Exit Sub
    context.Response.BufferOutput = False
    Dim FileField = SafeParam(context.Request.QueryString("FileField"))
    Dim FileTable = SafeParam(context.Request.QueryString("FileTabl开发者_如何学JAVAe"))
    Dim KeyField = SafeParam(context.Request.QueryString("KeyField"))
    Dim FileKey = SafeParam(context.Request.QueryString("FileKey"))
    Dim oSqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("Main").ConnectionString)
    Dim oSqlCommand = New SqlCommand("SELECT " & FileField & "Type," & FileField & "Bytes FROM " & FileTable & " WHERE " & KeyField & "=" & FileKey, oSqlConnection)
    oSqlConnection.Open()
    Dim oSqlDataReader = oSqlCommand.ExecuteReader(CommandBehavior.SequentialAccess)
    If oSqlDataReader.Read() Then
        context.Response.ContentType = GetMIMEType(oSqlDataReader.GetString(0))
        Dim bufferSize = 8040
        Dim chunk = New Byte(bufferSize - 1) {}
        Dim retCount As Long
        Dim startIndex As Long = 0
        retCount = oSqlDataReader.GetBytes(1, startIndex, chunk, 0, bufferSize)
        While retCount = bufferSize
            context.Response.BinaryWrite(chunk)
            startIndex += bufferSize
            retCount = oSqlDataReader.GetBytes(1, startIndex, chunk, 0, bufferSize)
        End While
        oSqlDataReader.Close()
        oSqlConnection.Close()
        Dim actualChunk = New Byte(retCount - 2) {}
        Buffer.BlockCopy(chunk, 0, actualChunk, 0, CInt(retCount) - 1)
        context.Response.BinaryWrite(actualChunk)
    End If
End Sub

thank you very much

the intermittance has stopped. dont know why.

but now its working ok