ASP.Net Abysmal File Uploads (to Database)

[joshua_jacobsen]

I am using Visual Studio.Net 2003, Visual Basic, Dot-Net 1.1
framework.

I have a function to upload files into a database, which worked great
when I was developing my application, but got slow and eventually died
after I had uploaded a few hundred files into my database.

The error in the event viewer was:
"aspnet_wp. exe (PID: 6952) was recycled because memory consumption
exceeded the 613 MB (60 percent of available RAM)."

Using Task manager, I watched as the aspnet_wp process hiked from
around 25 MB up to 600+ MB and crashed. Since I didn't seem to have
any loop problems, nested function calls, and the like, I was pretty
perplexed

Public Function File_Upload(ByR ef HTML_Upload_Con trol As
HtmlInputFile) As Integer
Dim sqlstring As String
Dim SQLAdapterObjec t As SqlDataAdapter
Dim SQLDataSetObjec t As DataSet
Dim SQLCommandBuild erObject As SqlCommandBuild er

Dim RecordID As Integer
Dim File_Name As String
Dim File_Size As Integer
Dim File_MimeType As String

File_Name = HTML_Upload_Con trol.PostedFile .FileName
File_Name = File_Name.Subst ring(File_Name. LastIndexOf("\" ) +
1)
File_Size = HTML_Upload_Con trol.PostedFile .ContentLength
File_MimeType = HTML_Upload_Con trol.PostedFile .ContentType
Dim File_Binary(Fil e_Size) As Byte
HTML_Upload_Con trol.PostedFile .InputStream.Re ad(File_Binary,
0, File_Size)

sqlstring = "SELECT * FROM [File_Store] ORDER BY RecordID
DESC"
SQLDataSetObjec t = New DataSet
SQLAdapterObjec t = New SqlDataAdapter( sqlstring,
SQLConnectionOb ject)
SQLCommandBuild erObject = New
SqlCommandBuild er(SQLAdapterOb ject)
SQLAdapterObjec t.Fill(SQLDataS etObject, "Table")
Try
Dim SQLNewRow As DataRow
SQLNewRow = SQLDataSetObjec t.Tables("Table ").NewRow()
SQLNewRow("File _Name") = File_Name
SQLNewRow("File _MimeType") = File_MimeType
SQLNewRow("File _Size") = File_Size
SQLNewRow("File _Binary") = File_Binary
SQLDataSetObjec t.Tables("Table ").Rows.Add(SQL NewRow)
SQLAdapterObjec t.Update(SQLDat aSetObject, "Table")
Catch ex As Exception
End Try

sqlstring = "SELECT Max(RecordID) as File_ID from
[File_Store];"
OpenQuery(sqlst ring)
If SQLDataReaderOb ject.Read() Then
RecordID = getInteger(SQLD ataReaderObject ("File_ID"))
Else
RecordID = 0
End If
CloseQuery()

Return RecordID
End Function

I managed to correct the symptoms by replacing this one line of code:

sqlstring = "SELECT TOP 1 * FROM [File_Store] ORDER BY
RecordID DESC"

Apparently, before I even iterate through the dataset or the
dataadapter, the entire contents of the [file_store] table is being
sent to my server. I had originally believed that this statement only
retrieved the schema of the table, until I actually iterated through
the data. This is not the case, apparently.

My solution, obviously, isn't ideal, because I'm still downloading a
file that I don't need. I tried putting a condition that would NEVER
return a file, but... new files didn't upload if my DataSet didn't
include at least one record from the query.

I hope this helps somebody, but if someone has a better way of solving
this type of problem, I would appreciate details.

Thanks,
Joshua

[Mark Fitzpatrick]

DataSets are downloaded in a one shot. Essentially the schema and all data
returned by the query is loaded into the dataset.

Can you give some more understanding into the process involved, basic flow
or the like. In this case, seeing the exact how you're doing it isn't as
helpful as the why as we understand the problem you're facing, just not sure
if there is a better design choice that would go completely around this
issue.
--
Hope this helps,
Mark Fitzpatrick
Former Microsoft FrontPage MVP 199?-2006

<jo************ *@oxy.comwrote in message
news:11******** *************@n 76g2000hsh.goog legroups.com...

>I am using Visual Studio.Net 2003, Visual Basic, Dot-Net 1.1
framework.

I have a function to upload files into a database, which worked great
when I was developing my application, but got slow and eventually died
after I had uploaded a few hundred files into my database.

The error in the event viewer was:
"aspnet_wp. exe (PID: 6952) was recycled because memory consumption
exceeded the 613 MB (60 percent of available RAM)."

Using Task manager, I watched as the aspnet_wp process hiked from
around 25 MB up to 600+ MB and crashed. Since I didn't seem to have
any loop problems, nested function calls, and the like, I was pretty
perplexed

Public Function File_Upload(ByR ef HTML_Upload_Con trol As
HtmlInputFile) As Integer
Dim sqlstring As String
Dim SQLAdapterObjec t As SqlDataAdapter
Dim SQLDataSetObjec t As DataSet
Dim SQLCommandBuild erObject As SqlCommandBuild er

Dim RecordID As Integer
Dim File_Name As String
Dim File_Size As Integer
Dim File_MimeType As String

File_Name = HTML_Upload_Con trol.PostedFile .FileName
File_Name = File_Name.Subst ring(File_Name. LastIndexOf("\" ) +
1)
File_Size = HTML_Upload_Con trol.PostedFile .ContentLength
File_MimeType = HTML_Upload_Con trol.PostedFile .ContentType
Dim File_Binary(Fil e_Size) As Byte
HTML_Upload_Con trol.PostedFile .InputStream.Re ad(File_Binary,
0, File_Size)

sqlstring = "SELECT * FROM [File_Store] ORDER BY RecordID
DESC"
SQLDataSetObjec t = New DataSet
SQLAdapterObjec t = New SqlDataAdapter( sqlstring,
SQLConnectionOb ject)
SQLCommandBuild erObject = New
SqlCommandBuild er(SQLAdapterOb ject)
SQLAdapterObjec t.Fill(SQLDataS etObject, "Table")
Try
Dim SQLNewRow As DataRow
SQLNewRow = SQLDataSetObjec t.Tables("Table ").NewRow()
SQLNewRow("File _Name") = File_Name
SQLNewRow("File _MimeType") = File_MimeType
SQLNewRow("File _Size") = File_Size
SQLNewRow("File _Binary") = File_Binary
SQLDataSetObjec t.Tables("Table ").Rows.Add(SQL NewRow)
SQLAdapterObjec t.Update(SQLDat aSetObject, "Table")
Catch ex As Exception
End Try

sqlstring = "SELECT Max(RecordID) as File_ID from
[File_Store];"
OpenQuery(sqlst ring)
If SQLDataReaderOb ject.Read() Then
RecordID = getInteger(SQLD ataReaderObject ("File_ID"))
Else
RecordID = 0
End If
CloseQuery()

Return RecordID
End Function

I managed to correct the symptoms by replacing this one line of code:

sqlstring = "SELECT TOP 1 * FROM [File_Store] ORDER BY
RecordID DESC"

Apparently, before I even iterate through the dataset or the
dataadapter, the entire contents of the [file_store] table is being
sent to my server. I had originally believed that this statement only
retrieved the schema of the table, until I actually iterated through
the data. This is not the case, apparently.

My solution, obviously, isn't ideal, because I'm still downloading a
file that I don't need. I tried putting a condition that would NEVER
return a file, but... new files didn't upload if my DataSet didn't
include at least one record from the query.

I hope this helps somebody, but if someone has a better way of solving
this type of problem, I would appreciate details.

Thanks,
Joshua

[joshua_jacobsen]

Thanks for your response. I'm not sure that I understand your
question, though. I have a file upload control, and when the page
posts, I want a file to be saved into a database. I have no special
interest in a dataset or any other objects. The example I saw on a
website showed the code above (or a close approximation) as the way to
accomplish this. I would be happy to do it in any other way.