Re: streaming binary data from sql 2005

Hi, could you please shed some light on "buffering the download" ? Here is
my code right now. I call the dbchunks2stream method which essentially
chunks the data to response.outputstream

Public Sub WriteChunks2DB(tablename as string, columnname as string,
pkcolumnname as string, pkcolumnval as string, s as stream)
Dim BUFFER_LENGTH As Integer = 131072 ' chunk size

' Make sure that Photo is non-NULL and return TEXTPTR to it.
Dim sqlstr as string = "SET NOCOUNT ON;UPDATE " + tablename + " SET " +
columnname + " = 0x0 WHERE " + pkcolumnname + "= '" +
replacequotes(pkcolumnval) + "';SELECT @Pointer=TEXTPTR(" + columnname + ")
FROM " + tablename + " WHERE " + pkcolumnname + " = '" +
replacequotes(pkcolumnval) + "'"
Dim cmdGetPointer As New SqlCommand(sqlstr, objConnect)
Dim PointerOutParam As SqlParameter =
cmdGetPointer.Parameters.Add("@Pointer", SqlDbType.VarBinary, 100)
PointerOutParam.Direction = ParameterDirection.Output
if objConnect.state = ConnectionState.Closed Then objConnect.Open

cmdGetPointer.ExecuteNonQuery()

' Set up UPDATETEXT command, parameters, and open BinaryReader.
sqlstr = "UPDATETEXT " + tablename + "." + columnname + " @Pointer
@Offset @Delete WITH LOG @Bytes"
Dim cmdUploadBinary As New SqlCommand(sqlstr, objConnect)
Dim PointerParam As SqlParameter =
cmdUploadBinary.Parameters.Add("@Pointer", SqlDbType.Binary, 16)
Dim OffsetParam As SqlParameter =
cmdUploadBinary.Parameters.Add("@Offset", SqlDbType.Int)
Dim DeleteParam As SqlParameter =
cmdUploadBinary.Parameters.Add("@Delete", SqlDbType.Int)
DeleteParam.Value = 1 ' delete 0x0 character
Dim BytesParam As SqlParameter =
cmdUploadBinary.Parameters.Add("@Bytes", SqlDbType.Binary, BUFFER_LENGTH)
Dim br As New BinaryReader(s)
Dim Offset As Integer = 0
OffsetParam.Value = Offset

' Read buffer full of data and execute the UPDATETEXT statement.
Dim Buffer() As Byte = br.ReadBytes(BUFFER_LENGTH)
Do While Buffer.Length > 0
PointerParam.Value = PointerOutParam.Value
BytesParam.Value = Buffer
cmdUploadBinary.ExecuteNonQuery()
DeleteParam.Value = 0 ' don't delete any other data
Offset += Buffer.Length
OffsetParam.Value = Offset
Buffer = br.ReadBytes(BUFFER_LENGTH)
Loop

br.Close()
s.Close()
objConnect.Close()

End Sub

Public Sub DBChunks2Stream(tablename as string, columnname as string,
pkcolumnname as string, pkcolumnval as string, byref s as stream)
Dim ImageCol As Integer = 0 ' position of image column in DataReader
Dim BUFFER_LENGTH As Integer = 131072 ' chunk size

' Make sure that Photo is non-NULL and return TEXTPTR to it.
Dim sqlstr as string = "SELECT @Pointer=TEXTPTR(" + columnname + "),
@Length=DataLength(" + columnname + ") FROM " + tablename + " WHERE " +
pkcolumnname + " = '" + pkcolumnval + "'"
Dim cmdGetPointer As New SqlCommand(sqlstr, objConnect)
Dim PointerOutParam As SqlParameter =
cmdGetPointer.Parameters.Add("@Pointer", SqlDbType.VarBinary, 100)
PointerOutParam.Direction = ParameterDirection.Output
Dim LengthOutParam As SqlParameter =
cmdGetPointer.Parameters.Add("@Length", SqlDbType.Int)
LengthOutParam.Direction = ParameterDirection.Output
if objConnect.state = ConnectionState.Closed Then objConnect.Open
cmdGetPointer.ExecuteNonQuery()
If PointerOutParam.Value Is DBNull.Value Then
objConnect.Close()
' Add code to deal with NULL BLOB.
Exit Sub
End If

' Set up READTEXT command, parameters, and open BinaryReader.
sqlstr = "READTEXT " + tablename + "." + columnname + " @Pointer @Offset
@Size HOLDLOCK"
Dim cmdReadBinary As New SqlCommand(sqlstr, objConnect)
Dim PointerParam As SqlParameter =
cmdReadBinary.Parameters.Add("@Pointer", SqlDbType.Binary, 16)
Dim OffsetParam As SqlParameter =
cmdReadBinary.Parameters.Add("@Offset", SqlDbType.Int)
Dim SizeParam As SqlParameter = cmdReadBinary.Parameters.Add("@Size",
SqlDbType.Int)
Dim dr As SqlDataReader
Dim Offset As Integer = 0
OffsetParam.Value = Offset
Dim Buffer(BUFFER_LENGTH - 1) As Byte

' Read buffer full of data and write to the file stream.
Do
PointerParam.Value = PointerOutParam.Value
' Calculate the buffer size - may be less than BUFFER_LENGTH for the
last block.
If Offset + BUFFER_LENGTH >= Cint(LengthOutParam.Value) Then
SizeParam.Value = Ctype((Cint(LengthOutParam.Value) - Offset),
Object)
Else
SizeParam.Value = Ctype(BUFFER_LENGTH, Object)
End If
dr = cmdReadBinary.ExecuteReader(CommandBehavior.SingleResult)
dr.Read()
dr.GetBytes(ImageCol, 0, Buffer, 0, Cint(SizeParam.Value))
dr.Close()
s.Write(Buffer, 0, Cint(SizeParam.Value))
Offset += Cint(SizeParam.Value)
OffsetParam.Value = Ctype(Offset, Object)
Loop Until Offset >= Cint(LengthOutParam.Value)

s.Close()
objConnect.Close()

End Sub



TIA!


"Bruce Barker" <brubar_nospamplease_@xxxxxxxxxx> wrote in message
news:elPATQF8FHA.2192@xxxxxxxxxxxxxxxxxxxxxxx
> the technique is the same. you will gain a performance boost if you switch
> to a varbinary(max) column over image. there are improvements in
> transferring the data.
>
> note: using asp.net for downloads is problematic. asp.net has a limited
> number of request threads and a limited number of connection from the
> asp.net worker process to .net isapi filter. this means you can not
> support a lot of simultaneous downloads. even though its heavy on
> resources, you might fiond buffering the download is best. this fress up
> the asp.net worker thread quicker.
>
>
> -- bruce (sqlwork.com)
>
>
> <param@xxxxxxxxxxxxxxxx> wrote in message
> news:unE2osD8FHA.3416@xxxxxxxxxxxxxxxxxxxxxxx
>> Hi all, is there a better way to stream binary data stored in a table in
>> sql 2005 to a browser in .net 2.0? Or is the code same as in .net 1.1? We
>> noticed that in certain heavy load scenarios, every now and then the
>> client would timeout and have to re-initiate the request...
>>
>> TIA!
>>
>
>


.