What is the purpose of the data set?

Question

I want to understand the purpose of datasets when we can communicate directly with a database using simple SQL statements. Also, which way is better? Updating data in a data set and then transferring it to the database immediately or updating the database directly?

Answer 1

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

Why do you have food in the refrigerator when you can just go straight 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 goal of DataSets is to avoid direct communication with the database using simple SQL statements. The goal of the DataSet is to act as a cheap local copy of the data you care about so you don't have to make expensive calls with high latency in the database. They allow you to enter the data warehouse once, pick up everything you need during the next week, and fill it in the refrigerator in the kitchen so that it is there when you need it.

Also, which way is better? Updating data in a data set and then transferring it to the database immediately or updating the database directly?

You order a dozen different products from a website. Which way is better: deliver items one at a time, as soon as they become available from their manufacturers, or until they become available and send them all at once? The first way: you receive each item as soon as possible; the second method has lower shipping costs. Which way is better? How the hell should we know? It's up to you!

A better data refresh strategy is one that does this thing in a way that fits with the needs and requirements of your client. You did not tell us that your client matters for the “best,” so the question cannot be answered. What does your customer want - the latest things, as soon as they are available, or a low shipping fee?

Answer 2

Datasets support a disabled architecture. You can add local data, delete it, and then using the SqlAdapter you can transfer everything to the database. You can even load the xml file directly into the dataset. It really depends on your requirements. You can even establish memory relationships between tables in a DataSet.

And btw, using direct sql queries built into your application, is really a very bad and bad way to develop an application. Your application will be subject to "Sql Injection". Secondly, if you write queries like those built into the application, Sql Server must execute this execution plan every time, while the stored procedures are compiled, and this execution is already accepted when it is compiled. Also, the Sql server can change its plan as the data becomes large. This way you get better performance. At least use stored procedures and check for unwanted input. They are inherently resistant to Sql injection.

Stored procedures and a data set are the way to go.

See diagram:

Edit: If you use .Net framework 3.5, 4.0, you can use a number of ORMs such as Entity Framework, NHibernate, Subsonic. ORMs present your business model more realistically. You can always use stored procedures with ORM if some of the functions are not supported in ORM.

For example: If you write a recursive CTE (Common Table Expression) Stored procedures are very useful. You will run into too big problems if you use the Entity Framework for this.

Answer 3

This page explains in detail when you should use Dataset and when you use direct access to databases.

Answer 4

I usually like to practice this, if I need to do a bunch of analytic processes on a large data set, I will populate the data set (or data depending on the structure). So this is an unrelated model from the database.

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