473,700 Members | 2,484 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Store SQL query results with SQLDataAdapter? with SQLDataReader? w

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.Op en()
' sdr = scmd.ExecuteRea der(CommandBeha vior.CloseConne ction)
' While sdr.Read
' AddResultForDat aSource(arlResu lts)
' End While
'Catch ex As Exception
' MsgBox(ex.ToStr ing, MsgBoxStyle.Cri tical, Me.Text)
'Finally
' ' Make sure the connection is closed even if an exception is
' ' thrown
' scnnDatabase.Cl ose()
' frmStatusMessag e.Close()
' sdr.Close()
'End Try
'' Add data from DataSet into ArrayList.
'dsResults.Tabl es.Add()
'For iWork = 0 To arlResults.Coun t - 1
' sArrayRow = CStr(arlResults (iWork))
' iWork2 = 1
' Do While sArrayRow <> ""
' drDataRow = dsResults.Table s(0).NewRow
' drDataRow(iWork 2) = ExtractItem(sAr rayRow, vbTab)
' iWork2 = iWork2 + 1
' Loop
' dsResults.Table s(0).Rows.Add(d rDataRow)
'Next

''''''' With Dataset
Try
' Fill the DataSet and name the DataTable.
sda.Fill(gdsRes ults, "Results")
Catch expSQL As SqlException
MsgBox(expSQL.T oString, MsgBoxStyle.Cri tical, Me.Text)
Exit Sub
Finally
' Make sure the connection is closed even if an exception is
' thrown
scnnDatabase.Cl ose()
frmStatusMessag e.Close()
End Try
Nov 21 '05 #1
1 2802
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*******@disc ussions.microso ft.com> wrote in message
news:E5******** *************** ***********@mic rosoft.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.Op en()
' sdr = scmd.ExecuteRea der(CommandBeha vior.CloseConne ction)
' While sdr.Read
' AddResultForDat aSource(arlResu lts)
' End While
'Catch ex As Exception
' MsgBox(ex.ToStr ing, MsgBoxStyle.Cri tical, Me.Text)
'Finally
' ' Make sure the connection is closed even if an exception is
' ' thrown
' scnnDatabase.Cl ose()
' frmStatusMessag e.Close()
' sdr.Close()
'End Try
'' Add data from DataSet into ArrayList.
'dsResults.Tabl es.Add()
'For iWork = 0 To arlResults.Coun t - 1
' sArrayRow = CStr(arlResults (iWork))
' iWork2 = 1
' Do While sArrayRow <> ""
' drDataRow = dsResults.Table s(0).NewRow
' drDataRow(iWork 2) = ExtractItem(sAr rayRow, vbTab)
' iWork2 = iWork2 + 1
' Loop
' dsResults.Table s(0).Rows.Add(d rDataRow)
'Next

''''''' With Dataset
Try
' Fill the DataSet and name the DataTable.
sda.Fill(gdsRes ults, "Results")
Catch expSQL As SqlException
MsgBox(expSQL.T oString, MsgBoxStyle.Cri tical, Me.Text)
Exit Sub
Finally
' Make sure the connection is closed even if an exception is
' thrown
scnnDatabase.Cl ose()
frmStatusMessag e.Close()
End Try

Nov 21 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
11848
by: lakshmi | last post by:
Hi all I'm trying to traverse through the results from a query that returns more than 1 row. The data reader reads only the first row. The following code doesn't work. Let me know what's wrong. do { while(reader.read) { x += y;
1
275
by: Aaron | last post by:
My db table looks like this FILENAME FILE_CREATED FILE_MODIFIED test 9/12 test2 9/13 10/13 .. .. all FILE_CREATED field have value, only some FILE_MODIFIED have value. I need to get the value of FILENAME column and this is what i have
0
1902
by: Rob | last post by:
I doubt this is the best way to do it, but what I came up with was to hide the XML in an HTML Comment then edit the file deleting the HTML stuff and keep the XML results. If anyone has a better solution, I would be interested. Thank you. Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim cmd As System.Data.SqlClient.SqlCommand cmd = New System.Data.SqlClient.SqlCommand
4
14387
by: Chris Tremblay | last post by:
I am trying to figure out how to go about retrieving the number of results returned from my queries in SQL server from VB.NET without using a the Select Count(*) query. The method that I was using was the following: Take the query that I am executing, copy the query and turn it into a count query, run the count query, then execute the original query. The reason for this is so that I can implememt public paging on my website. The...
0
1658
by: Cailin | last post by:
I am trying to make a connection with my SQL server DB, and I got this error message: "BC30519: Overload resolution failed because no accessible 'New' can be called without a narrowing conversion". Here is my code. sub Page_Load dim myConnection as New SqlConnection("Provider=sqloledb;Data Source=123456;Initial Catalog=someDB;User Id=23456;Password=somePw;") dim myCommand as SqlDataAdapter = New SqlDataAdapter("SELECT symbol_id,
1
1308
by: nasirmajor | last post by:
i have a databound listbox that has a multilple selection made to true. when user select multiple values in listbox and clicks button i want them to show the results in the datagrid on the same page using panels.
6
2723
by: c676228 | last post by:
Hi everyone, I wrote a store procedure that fetch one row data in the database based on the parameter value I entered. After I created the store procedure, the store procedure code looks like this: ALTER proc getProductCommScale @product As varchar(30), @TISCommRate As Decimal(5,2) OUTPUT, @BrokerCommRate As Decimal(5,2) OUTPUT, @Fee As Decimal(5,2) OUTPUT As if RTRIM(@product)='Imed' Select @TISCommRate=TISComm,...
3
2095
by: Oenone | last post by:
I have a project that creates a SqlDataAdapter and uses its Fill method to fill a DataTable with data from a user-provided query. From there I can obviously access details about the rows and columns returned by the query. However, I need to be able to determine the size of the varchar fields that are returned by the query. The obvious answer appeared to be to check the DataColumn.MaxLength property. However, after using the Fill method,...
4
1656
by: K Viltersten | last post by:
Today, i run the code below and while it works, i can't stop wondering if it can be performed in a better way. Especially, i'd like to know if the declaration of the adapter is neccessary. SqlCommand command = new SqlCommand("MyStorProc", Connection); command.CommandType = CommandType.StoredProcedure; foreach (String key in form.Keys)
0
8647
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
8974
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8926
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7813
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6563
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4404
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4659
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3092
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2030
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.