By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,592 Members | 1,457 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,592 IT Pros & Developers. It's quick & easy.

Store SQL query results with SQLDataAdapter? with SQLDataReader? w

P: n/a
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
Nov 21 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.

"Simon1234" <Si*******@discussions.microsoft.com> wrote in message
news:E5**********************************@microsof t.com...
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

Nov 21 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.