Solved

Datareader vs dataset over Wan link

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
export sql results to csv 6 35
Linked Server Issue with SQL2012 3 26
Help with preventing downloading a zip file 10 37
Expression Evaluater 3 25
In this article I will describe the Detach & Attach 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.
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

770 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