Datareader vs dataset over Wan link

Hi,

I have trying to do some connections to a remote database and do a whole lot of reads. No no writing back to the db. So I am doing this in .net using a SQLreader. However occasionally it seems to cause a timeout with other connections to the database from a separate application.

So I always have to open the db execute the sql reader then close the connection.

what I am wondering is if I am using the dataset method even though I might get lower speed would it be gentler on the db and less likely to cause issues. The other apps like my app connect over a wan link as does my application.

The issue they get seem to be timeout related as well.

I am also thinking since the sql reader has to iterate through each result it might then a dataset that gets it all back in one hit.

So please advise thanks on my best options.

Thanks,

Ward
LVL 1
whorsfallAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jacques Bourgeois (James Burger)Connect With a Mentor PresidentCommented:
You do not need a DataSet to replace a DataReader. You need a DataTable.

A DataSet is a collection of tables with relations between them. If you have only one table, or have many tables but without the relations, then a DataSet just had useless overhead.

As for the question, it depends on the data and how you use it.

If you use the DataReader to accumulate the data in memory, then it is possible that what you are doing is the same as filling a DataTable.

However, if you do not keep the data between reads, things will be different. If the amount of data returned is not to big, you might not feel a difference between using a DataReader or a DataAdapter to Fill a DataTable. But if there is a lot of data, you have to think that the DataTable has to reside completely in memory, and this can be detrimental when there is a lot of data.

"I always have to open the db execute the sql reader then close the connection". You seem to say that as if usually, you do not close the connection. As someone said, you should always close a connection when you have finished with it. In ADO.NET, the connection is not automatically closed when you lose the connection variable as it did in classic ADO. If you don't the number of connections on the server increases, this overwork the server, and you end up with timeouts.
0
 
QuinnDexCommented:
Basically they will be the same, you will still be executing the same query on the SQL server

it is allways good practice to close your connections no matter how you are calling the DB
0
 
Fernando SotoRetiredCommented:
It would seem that their might be an issue with connecting to the database. Because this can be caused by different issues please have a look at this article, SQL Server Connection Pooling (ADO.NET), and see if this can resolve your issues.
0
 
käµfm³d 👽Commented:
A DataSet doesn't actually connect to a database; it is just a container for a set of DataTables. You seem to be using incorrect terminology.

Now, if you are referring to a DataAdapter, well that uses a DataReader under the hood, so there is no difference with respect to what you are talking about.
0
All Courses

From novice to tech pro — start learning today.