Store SQL query results with SQLDataAdapter? with SQLDataReader? w

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Below you will see my code to read data from a database and store it into a
dataset. The first, commented code uses an SQLDataReader and the second,
uncommented code uses the Fill method of the SqlDataAdapter.
They both work perfectly, as long as I use a query that returns a small
amount of data. When I use a query that returns all the data I need, the
program slows to a crawl and begins using tons of memory.
Is there a better way to retrieve and store my "ton-o-data" that won't have
the users of my program hitting Ctrl-Alt-Del thinking that their machines
have frozen?



'''''''' With Datareader
'Try
' scnnDatabase.Open()
' sdr = scmd.ExecuteReader(CommandBehavior.CloseConnection)
' While sdr.Read
' AddResultForDataSource(arlResults)
' End While
'Catch ex As Exception
' MsgBox(ex.ToString, MsgBoxStyle.Critical, Me.Text)
'Finally
' ' Make sure the connection is closed even if an exception is
' ' thrown
' scnnDatabase.Close()
' frmStatusMessage.Close()
' sdr.Close()
'End Try


'' Add data from DataSet into ArrayList.
'dsResults.Tables.Add()
'For iWork = 0 To arlResults.Count - 1
' sArrayRow = CStr(arlResults(iWork))
' iWork2 = 1
' Do While sArrayRow <> ""
' drDataRow = dsResults.Tables(0).NewRow
' drDataRow(iWork2) = ExtractItem(sArrayRow, vbTab)
' iWork2 = iWork2 + 1
' Loop
' dsResults.Tables(0).Rows.Add(drDataRow)
'Next

''''''' With Dataset
Try
' Fill the DataSet and name the DataTable.
sda.Fill(gdsResults, "Results")
Catch expSQL As SqlException
MsgBox(expSQL.ToString, MsgBoxStyle.Critical, Me.Text)
Exit Sub
Finally
' Make sure the connection is closed even if an exception is
' thrown
scnnDatabase.Close()
frmStatusMessage.Close()
End Try
 
How many rows are we talking about here? You should not be pulling down
tons of data. Only the minimal amt for your operation, if we are talking
about potentially huge number of rows. There is no way to get around the
fact that if you are pulling down 500K rows or something, that your memory
and performance are going to get killed.
 
Back
Top