ADO.NET is the standard .NET data access technology. There are other technologies that can sit on top of that and provide extra features, but ADO.NET still does the grunt work under the hood.
There are three main ways to retrieve data from a database using ADO.NET:
1. Create a DbCommand and call ExecuteScalar to retrieve a single value. That will execute a query and return the first column from the first row of the result set.
2. Create a DbCommand and call ExecuteReader to create a DbDataReader. That will allow you to read the result set of a query row by row and use it as you go.
3. Create a DbDataAdapter and call Fill to populate a DataTable. That will populate the DataTable with the result set of a query, making it easy to make changes and save them back to the database.
There are two main ways to save changes to a database using ADO.NET:
1. Create a DbCommand and call ExecuteNonQuery. That will make changes directly to records in the database.
2. Create a DbDataAdapter and call Update to save changes from a DataTable. That would usually be the same DbDataAdapter and DataTable from option 3 above.
Follow the CodeBank link in my signature below and check out my thread on Retrieving & Saving Data for examples of these and other common ADO.NET scenarios.
Note that you need to create a DbConnection to actually connect to the database. That is then associated with a DbCommand to execute SQL code over that connection. A DbDataAdapter is a way to group DbCommands representing the four CRUD operations. Its SelectCommand is executed when you call Fill and its InsertCommand, UpdateCommand and DeleteCommand are executed as needed when you call Update.
When you create a typed DataSet using the Data Source Wizard, you're still using ADO.NET but the system extends and enhances the types you use for working specifically with your data. The type DataSet itself inherits the standard DataSet class and adds a property for each table. Each typed DataTable inherits the standard DataTable class and adds methods for working with data with the specific schema of the database table it corresponds to. Each typed DataRow inherits the standard DataRow class and adds a property for each column in the table. Each table adapter wraps up a DbDataAdapter, complete with commands and connection. The Fill method of the table adapter internally calls the Fill method of the wrapped DbDataAdapter and likewise for the Update methods. You can add additional queries and table adapters in the DataSet designer and that will generate additional methods that may call ExecuteScalar or the like.
For more info on table adapters, check this out:
http://msdn.microsoft.com/en-us/library/7zt3ycf2.aspx