Creating my first applications with a database (SQL Server), I make the connection by passing my string and using the SQLCommand class to pass the query.
But by searching other sites, I see that access to the database is done via DataSet.
What would a DataSet be and what would it do?
First you must understand what ADO.NET is. It is an access layer between some data warehouse (not limited to the database) and your .NET application. In your case, to access the SQL Server information there is the ADO.NET layer that will do the intermediate between your bank and your application. The SQLCommand you cited is an ADO.NET architecture class that helps in this process.
The DataSet does not serve to access the database . DataSet abstracts these data from the outside of the framework when they are in memory, being independent of the data source . For example, when you make a request to the database:
SELECT * FROM PESSOAS WHERE PESSOAS.NAME LIKE 'COLETTA'
The database will process this SQL, and when it reaches the data access layer, it will store this information in memory, as an example in a DataSet or DataReader, according to the implementation.
With the DataSet structure, I can manipulate my data in memory with its DataTable and DataRelation, just like the relational structure of the databases. Example:
//criando o dataSet;
DataSet exemplo = new DataSet("EXEMPLO");
DataTable peopleTable = exemplo.Tables.Add("PESSOAS");
peopleTable.Columns.Add("Name", typeof(string));
Notice in this example that I created a table called PEOPLE with a column named Name. Now I can add records within this structure:
DataRow myRow = exemplo.Tables["PESSOAS"].NewRow();
myRow["Name"] = "COLETTA";
My example is really simple and would not be very useful, but the idea is to exemplify how the structure is created. By creating these structures, you could insert into your Sql Server database using SqlDataAdapter.Update(exemplo);
The dataset , as the name says, is just a dataset. It is finite and has a specific function and feature.
We use it to treat data that came from the database through some query, often through an SQL command, then comes the columns and rows of the tables that you determine. All that matters for what you will do in that operation is in the dataset mounted by the query you made.
A dataset can be mounted manually or through a framework , such as the Entity Framework that is heavily used with .NET. ADO.NET has its own DataSet . It is considered more or less obsolete. Not that it can not be used and is not useful, but in most cases if it is used heavily it is very heavy that it makes sense to use EF.
When you need to do something simpler it is often more interesting to have just one DataReader ( specific to SQL Server ) and then write to your hand what you need, in a way called disconnected, perhaps through a DataAdapter .
Then it is an abstraction to access data that came from a data source, probably a database, which can be SQL Server, so it accesses the database data indirectly through a "mirror" in memory .
I would avoid using it unless it makes a lot of sense. Some people like it.