Solved

Datareader vs dataset over Wan link

Posted on 2013-12-06
4
331 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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Load XML element 3 42
EF5: Update Model from Database not working 3 73
How do i create a simple array in a public class? 6 35
Need Help With GDI+ in VB.Net 8 37
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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

738 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