Solved

Datareader vs dataset over Wan link

Posted on 2013-12-06
4
338 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 63

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 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