Re: streaming binary data from sql 2005


Thanks for your response Param,

Regarding on developing IIS components through managed code in .net 2.0,
seems there hasn't detailed public reference. I'll contact some other IIS
experts to see whether they have any information on this. Also, for
developing raw ISAPI extension or filters, here're some reference:


#Taking the SplashDiving into ISAPI Programming
http://www.microsoft.com/mind/0197/isapi.asp

#ISAPI Extensions: Creating a DLL to Enable HTTP-based File Uploads with IIS
http://msdn.microsoft.com/msdnmag/issues/01/10/Upload/default.aspx

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>
<OvBSy1J8FHA.808@xxxxxxxxxxxxxxxxxxxx>
<06TH7xN8FHA.3380@xxxxxxxxxxxxxxxxxxxxx>
| Subject: Re: streaming binary data from sql 2005
| Date: Thu, 24 Nov 2005 08:24:43 -0600
| Lines: 238
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| Message-ID: <O3nORLQ8FHA.3760@xxxxxxxxxxxxxxxxxxxx>
| Newsgroups: microsoft.public.dotnet.framework.aspnet
| NNTP-Posting-Host: corp2.lazardgroup.com 70.182.148.88
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.dotnet.framework.aspnet:360730
| X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
|
| 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!
| > | >>
| > | >
| > | >
| > |
| > |
| > |
| >
|
|
|

.

Relevant Pages

  • Re: IIS (or Isapi) adds Connection header to response
    ... I would never rely on a bug in a browser implementation as the means to get ... > I dont currently have access to the ISAPI code. ... available on any other IIS version. ... NTLM header since it is no longer useful. ...
    (microsoft.public.inetserver.iis.security)
  • Re: ADSI Problem
    ... I suggest posting to IIS groups as well because if IIS will cause it to ... causing the script to stop working after it has been working. ... I have written a script to authenticate Active Directory users via ADSI ... Dim strUserName ...
    (microsoft.public.windows.server.active_directory)
  • Re: ISAPI vs. HTTPModule
    ... which parses the request and dispatches it to IIS in usermode. ... extension of the request is determined, and then sent to its handler. ... > ISAPI will not be able to access any .Net intrinsics/events. ...
    (microsoft.public.inetserver.iis)
  • Re: Images/CSS not resolving on dev machine testing
    ... enable ISAPI extensions for IIS. ... picture has a reference on how to do it. ... I am developing a website with VS2005 and VB. ... That is when I lose my images and css styles. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: IIS Isolation Mode
    ... We create some files and read some files in the ISAPI. ... something that requires local system privileges, and if it doesn't have it, ... would guess that the IIS 5 version will work correctly on IIS 6, since ADSI ...
    (microsoft.public.inetserver.iis.security)