What's the purpose of Datasets?

Question

I want to understand the purpose of datasets when we can directly communicate with the database using simple SQL statements. Also, which way is better? Updating the data in dataset and then transfering them to the database at once or updating the database directly?

Pycnidium

Answer 1

I want to understand the purpose of datasets when we can directly communicate with the database using simple SQL statements.

Why do you have food in your fridge, when you can just go directly to the grocery store every time you want to eat something? Because going to the grocery store every time you want a snack is extremely inconvenient.

The purpose of DataSets is to avoid directly communicating with the database using simple SQL statements. The purpose of a DataSet is to act as a cheap local copy of the data you care about so that you do not have to keep on making expensive high-latency calls to the database. They let you drive to the data store once, pick up everything you're going to need for the next week, and stuff it in the fridge in the kitchen so that its there when you need it.

Also, which way is better? Updating the data in dataset and then transfering them to the database at once or updating the database directly?

You order a dozen different products from a web site. Which way is better: delivering the items one at a time as soon as they become available from their manufacturers, or waiting until they are all available and shipping them all at once? The first way, you get each item as soon as possible; the second way has lower delivery costs. Which way is better? How the heck should we know? That's up to you to decide!

The data update strategy that is better is the one that does the thing in a way that better meets your customer's wants and needs. You haven't told us what your customer's metric for "better" is, so the question cannot be answered. What does your customer want -- the latest stuff as soon as it is available, or a low delivery fee?

Cull

By the way, no customer. Just myself. It's just a stock management system I'm doing for practice. Since data is repeatedly used for viewing and updating, I guess datasets is the answer. But still, there's this problem that in case of a system failure, all the work done by the user will get lost unless the database is updated for each and every update done by the user. Is it possible to prevent this with datasets? – Pycnidium

@Lizzie: That's a great candidate for a new question. – Cull

Answer 2

Datasets support disconnected architecture. You can add local data, delete from it and then using SqlAdapter you can commit everything to the database. You can even load xml file directly into dataset. It really depends upon what your requirements are. You can even set in memory relations between tables in DataSet.

And btw, using direct sql queries embedded in your application is a really really bad and poor way of designing application. Your application will be prone to "Sql Injection". Secondly if you write queries like that embedded in application, Sql Server has to do it's execution plan everytime whereas Stored Procedures are compiled and it's execution is already decided when it is compiled. Also Sql server can change it's plan as the data gets large. You will get performance improvement by this. Atleast use stored procedures and validate junk input in that. They are inherently resistant to Sql Injection.

Stored Procedures and Dataset are the way to go.

See this diagram:

Edit: If you are into .Net framework 3.5, 4.0 you can use number of ORMs like Entity Framework, NHibernate, Subsonic. ORMs represent your business model more realistically. You can always use stored procedures with ORMs if some of the features are not supported into ORMs.

For Eg: If you are writing a recursive CTE (Common Table Expression) Stored procedures are very helpful. You will run into too much problems if you use Entity Framework for that.

Nibelung

I would like to disagree - I would say using an object-relational mapper (like Entity Framework, which also nicely supports stored procedures) to turn your relational data into proper objects is the way to go ..... – Leadwort

@marc_s: I would also agree. But this question is dealing with .Net framework 2.0 and I when I said "Stored Procedures and Dataset" I meant they are the way to go for Ado.Net along with good old SqlHelper class. – Nibelung

Answer 3

This page explains in detail in which cases you should use a Dataset and in which cases you use direct access to the databases

Irmgardirmina

Answer 4

I usually like to practice that, if I need to perform a bunch of analytical proccesses on a large set of data I will fill a dataset (or a datatable depending on the structure). That way it is a disconnected model from the database.

But for DML queries I prefer the quick hits directly to the database (preferably through stored procs). I have found this is the most efficient, and with well tuned queries it is not bad at all on the db.

Seafarer