ADO.NET is a new database access technology created by Microsoft; its .NET Framework can access any kind of data source. It's a collection of object-oriented classes that provides a rich set of data components to create high-performance, reliable, and scalable database applications for client-server applications as well as distributed environments over the Internet and intranets.
In the ADO.NET model—unlike ADO (in connected state) and previous data access technologies—applications are connected to the data sources only when they are reading or updating the data. After that, the connection closes. This is important because in client-server or distributed applications, having connection resources open all the time is one of the most resource-consuming parts. You don't have to connect to a data source all the time; the only time you need to connect to a data source is when you are reading and writing final changes to a data source.
The old approach of having connections open all the time is problematic when considering the number of clients that access the same data source simultaneously. In larger systems, Microsoft Transaction Server (MTS)/COM+ was often used to pool ADO connections to address some of this resource consumption; however, ADO.NET provides a more optimal paradigm for large systems without needing to utilize MTS/COM+ enterprise services—unless, of course, you need to take advantage of such advanced techniques as a three-phase commit.
ADO.NET uses Structured Query Language (SQL) queries and stored procedures to read, write, update, and delete data from a data source. You use SQL queries through the ADO.NET Command object, which returns data in the form of DataReader or DataSet objects. After that connection is closed with the data source, you use DataSet objects to work with the data and connect to the data source again when you need to update the data source.
SQL queries are also called SQL statements. See Appendix B for some SQL query examples. See Chapter 11 for more details on stored procedures.
A DataSet is a collection of DataTable objects and the relationships among them. It works as a container that stores returned data from a database in cached form. You can fill a DataSet with the data retrieved from multiple tables of a database. Once you have a DataSet (which is disconnected data, stored on your local machine), you treat the DataSet as your data source and work with it. You call the Update method to make DataSet changes final to the actual database. You can even read and update a DataSet from different data sources.
You access a data source and fill a DataSet via data providers. The .NET Framework provides three different types of data providers that ship with the version 1.0 of Visual Studio .NET: Sql, OleDb, and Odbc. Microsoft and third parties have also developed data providers for the Oracle database. Some companies also have developed data providers for MySql and other databases. You use a DataAdapter object of a data provider and call its Fill method to fill a DataSet.
XML plays a major role in ADO.NET. The ADO.NET model utilizes XML to store the data in cache and transfer the data among applications. DataSets use XML schemas to store and transfer data among applications. You can even use this XML file from other applications without interacting with the actual DataSet. Because XML is an industry standard, data can be used among all kinds of applications and components; because of its text-based nature, data can be transferred via many protocols, such as Hypertext Transfer Protocol (HTTP). We discuss XML and ADO.NET integration and how you can take advantage of XML support in ADO.NET applications in Chapter 6.