Re: streaming binary data from sql 2005

Steve, do you have some links to some samples on how to develop an isapi
filter in .net 2.0? I have never attempted that before and dont know where
to begin. We are working on a large app where pdf files have to be securely
stored in a sql database & streamed down to users in their browser. We are
expecting upto 10,000+ simultaneous users at any given time.

TIA!

"Steven Cheng[MSFT]" <stcheng@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:06TH7xN8FHA.3380@xxxxxxxxxxxxxxxxxxxxxxxx
> Hi Param,
>
> I think the Buffering Bruce mentioned means the OutputBuffer setting for
> asp.net web page. We can set it in the @Page directive like:
>
> <%@Page ..... Buffer="true". .... %>
>
> or in code through:
>
> Page_load(...)
> {
> Response.BufferOutput = true;
> }
>
> The default should be ture. Setting Buffer can help us buffer the response
> data at serversdie so as to flush them once all the data is complete.
> However, this will cause the server memory be comsumed seriously when lots
> of request executing for large data downloading.....
>
> Also, I think it'll be better to implement file download in raw IIS isapi
> filter or extension and seems .NET 2.0 support developing IIS isapi
> components through managed code..
>
> Thanks,
>
> Steven Cheng
> Microsoft Online Support
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
>
> --------------------
> | From: <param@xxxxxxxxxxxxxxxx>
> | References: <unE2osD8FHA.3416@xxxxxxxxxxxxxxxxxxxx>
> <elPATQF8FHA.2192@xxxxxxxxxxxxxxxxxxxx>
> | Subject: Re: streaming binary data from sql 2005
> | Date: Wed, 23 Nov 2005 20:19:04 -0600
> | Lines: 156
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
> | X-RFC2646: Format=Flowed; Response
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
> | Message-ID: <OvBSy1J8FHA.808@xxxxxxxxxxxxxxxxxxxx>
> | Newsgroups: microsoft.public.dotnet.framework.aspnet
> | NNTP-Posting-Host: corp2.lazardgroup.com 70.182.148.88
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl
> microsoft.public.dotnet.framework.aspnet:360609
> | X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
> |
> | 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!
> | >>
> | >
> | >
> |
> |
> |
>


.