Solved

Datareader vs dataset over Wan link

Posted on 2013-12-06
4
317 Views
Last Modified: 2014-01-06
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
0
Comment
Question by:whorsfall
4 Comments
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39702833
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
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39703145
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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39703164
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
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 total points
ID: 39704017
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now