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(ByRef HTML_Upload_Control As
HtmlInputFile) As Integer
Dim sqlstring As String
Dim SQLAdapterObject As SqlDataAdapter
Dim SQLDataSetObject As DataSet
Dim SQLCommandBuilderObject As SqlCommandBuilder

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

File_Name = HTML_Upload_Control.PostedFile.FileName
File_Name = File_Name.Substring(File_Name.LastIndexOf("\") +
1)
File_Size = HTML_Upload_Control.PostedFile.ContentLength
File_MimeType = HTML_Upload_Control.PostedFile.ContentType
Dim File_Binary(File_Size) As Byte
HTML_Upload_Control.PostedFile.InputStream.Read(File_Binary,
0, File_Size)

sqlstring = "SELECT * FROM [File_Store] ORDER BY RecordID
DESC"
SQLDataSetObject = New DataSet
SQLAdapterObject = New SqlDataAdapter(sqlstring,
SQLConnectionObject)
SQLCommandBuilderObject = New
SqlCommandBuilder(SQLAdapterObject)
SQLAdapterObject.Fill(SQLDataSetObject, "Table")
Try
Dim SQLNewRow As DataRow
SQLNewRow = SQLDataSetObject.Tables("Table").NewRow()
SQLNewRow("File_Name") = File_Name
SQLNewRow("File_MimeType") = File_MimeType
SQLNewRow("File_Size") = File_Size
SQLNewRow("File_Binary") = File_Binary
SQLDataSetObject.Tables("Table").Rows.Add(SQLNewRow)
SQLAdapterObject.Update(SQLDataSetObject, "Table")
Catch ex As Exception
End Try

sqlstring = "SELECT Max(RecordID) as File_ID from
[File_Store];"
OpenQuery(sqlstring)
If SQLDataReaderObject.Read() Then
RecordID = getInteger(SQLDataReaderObject("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

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(ByRef HTML_Upload_Control As
HtmlInputFile) As Integer
Dim sqlstring As String
Dim SQLAdapterObject As SqlDataAdapter
Dim SQLDataSetObject As DataSet
Dim SQLCommandBuilderObject As SqlCommandBuilder

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

File_Name = HTML_Upload_Control.PostedFile.FileName
File_Name = File_Name.Substring(File_Name.LastIndexOf("\") +
1)
File_Size = HTML_Upload_Control.PostedFile.ContentLength
File_MimeType = HTML_Upload_Control.PostedFile.ContentType
Dim File_Binary(File_Size) As Byte
HTML_Upload_Control.PostedFile.InputStream.Read(File_Binary,
0, File_Size)

sqlstring = "SELECT * FROM [File_Store] ORDER BY RecordID
DESC"
SQLDataSetObject = New DataSet
SQLAdapterObject = New SqlDataAdapter(sqlstring,
SQLConnectionObject)
SQLCommandBuilderObject = New
SqlCommandBuilder(SQLAdapterObject)
SQLAdapterObject.Fill(SQLDataSetObject, "Table")
Try
Dim SQLNewRow As DataRow
SQLNewRow = SQLDataSetObject.Tables("Table").NewRow()
SQLNewRow("File_Name") = File_Name
SQLNewRow("File_MimeType") = File_MimeType
SQLNewRow("File_Size") = File_Size
SQLNewRow("File_Binary") = File_Binary
SQLDataSetObject.Tables("Table").Rows.Add(SQLNewRow)
SQLAdapterObject.Update(SQLDataSetObject, "Table")
Catch ex As Exception
End Try

sqlstring = "SELECT Max(RecordID) as File_ID from
[File_Store];"
OpenQuery(sqlstring)
If SQLDataReaderObject.Read() Then
RecordID = getInteger(SQLDataReaderObject("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.