Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free.

Pros and cons and Usage

I have been preparing myself for an interview for the post of software developer in .net platform. For database related issues, I have to to a point about the connected ans disconnected classes of ADO .NET. I have read several articles but still not getting a perfect picture of those two terms. Hence, here I am, to the best place to get a concrete ans. Can any one explain me the pros and cons and the usage scenarios?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

I'll attempt to explain it very briefly.

When going the "disconnected" way, you use a DataAdapter to connect to the database, retrieve the data, and store it in a Dataset. Helper methods are provided for you to filter and sort your data, and saving data back to the database is as easy as asking "please update my data, thanks" :-)

You then work with your datasets stored in the memory, that is with a disconnected version of your data. You also can do some work very quickly if you use Visual Studio tools because it will generate strongly typed Datasets (example: you have a "Person" table, you'll obtain a "Person" class) with all relationships between tables already configured. Basically, you do very little SQL. There are other benefits such as generating XML documents, or reading data from XML documents into datasets, but I'll leave it to you to read more on that subject.
However, there are some drawbacks... I think that for professional applications it is far from being the right solution, because the generated classes are "heavy" and the memory footprint of datasets is big (very big compared to the data itself).

When going the "connected" way, you use the DataReader and RecordSets. A DataReader is the fastest tool to retrieve data from the database. As long as you are using a DataReader you are connected to the database and you may execute as many "commands" as you want. However, you're on your own as you have to write everyting yourself, that is your SQL queries, your database access layer, your Models/Domain classes, etc. But of course, you have much more control over what you are doing. It is I guess the preferred way for professional applications... when not working with ORM APIs such as NHibernate for example.

To resume:

  • Disconnected:

    • Advantages: Visual Studio tools (strongly typed classes --> intellisense works), writing code fast, fast refactoring (also with VS tools), nice XML support
    • Drawbacks: Slower than the "connected" mode, Heavy memory footprint, "big" generated classes
  • Connected:

    • Advantages: Performance, Total Control
    • Drawbacks: writing code is slower

Add-on: there is also the matter of Linq to SQL, and the Entity Framework... but I can't answer for that as I have only worked a little bit with Linq and not at all with the Entity Framework.

share|improve this answer
Thank you very much, I got my answers :) –  Pankouri Jul 27 '11 at 11:42

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.