streaming binary data from sql 2005

G

Guest

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!
 
B

Bruce Barker

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)
 
G

Guest

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!
 
S

Steven Cheng[MSFT]

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: <[email protected]>
| References: <[email protected]>
<[email protected]>
| 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: <[email protected]>
| 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!
|
|
| | > 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)
| >
| >
| > | >> 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!
| >>
| >
| >
|
|
|
 
G

Guest

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!
 
S

Steven Cheng[MSFT]

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: <[email protected]>
| References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
| 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: <[email protected]>
| 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!
|
| | > 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: <[email protected]>
| > | References: <[email protected]>
| > <[email protected]>
| > | 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: <[email protected]>
| > | 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!
| > |
| > |
| > | | > | > 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)
| > | >
| > | >
| > | > | > | >> 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!
| > | >>
| > | >
| > | >
| > |
| > |
| > |
| >
|
|
|
 
S

Steven Cheng[MSFT]

Hi Param,

I've some further communiation with our IIS dev guys and seems the first
layer IIS extending development through pure .net managed code is still not
available for IIS6. We may still have to wait for vista/iis7 which will
support full IIS extending 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.)
--------------------
| X-Tomcat-ID: 38770351
| References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: (e-mail address removed) (Steven Cheng[MSFT])
| Organization: Microsoft
| Date: Fri, 25 Nov 2005 02:38:14 GMT
| Subject: Re: streaming binary data from sql 2005
| X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.aspnet
| Lines: 287
| Path: TK2MSFTNGXA02.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.dotnet.framework.aspnet:360830
| NNTP-Posting-Host: tomcatimport2.phx.gbl 10.201.218.182
|
|
| 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: <[email protected]>
| | References: <[email protected]>
| <[email protected]>
| <[email protected]>
| <[email protected]>
| | 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: <[email protected]>
| | 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!
| |
| | | | > 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: <[email protected]>
| | > | References: <[email protected]>
| | > <[email protected]>
| | > | 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: <[email protected]>
| | > | 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!
| | > |
| | > |
| | > | | | > | > 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)
| | > | >
| | > | >
| | > | > | | > | >> 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!
| | > | >>
| | > | >
| | > | >
| | > |
| | > |
| | > |
| | >
| |
| |
| |
|
|
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top