The ADO.NET programming model is built around two groups of objects:
Managed provider objects: These objects provide direct, connected access to a data source and the ability to directly interact with a data source. This group includes the Connection, Command, DataAdapter, and DataReader objects. The DataReader object provides streaming, forward-only, read-only access to a data source and is the fastest way to access a data source.
The DataSet object: This object provides disconnected access to a data source, that is, without a persistent connection to the parent data source. In fact, the DataSet object has no ability to directly connect to a data source. Instead, the DataAdapter object acts as the intermediary between the DataSet object and the data source. The DataSet object provides the ability to navigate through hierarchical data and to manipulate data and the underlying schema, all without a persistent connection to the parent data source. The DataSet object tracks all changes made to the data set so that they may be later merged with the parent data source. Finally, the DataSet object has the ability to serialize to Extensible Markup Language (XML) as an XML data document, which is simply an XML document that preserves relational information.
The .NET Framework provides a number of namespaces that relate both directly and indirectly to data access
NET Framework Namespaces Involved in Data Access are
System.Data : Provides base classes for ADO.NET, focused on the DataSet class and its child classes, such as DataRow, DataColumn, and DataRelation.
System.Data.SqlClient : The SQL Server .NET data provider.
System.Data.OleDb : The OLE DB .NET data provider.
System.Data.Common : Provides classes that are shared by all .NET data providers. Many of these classes are abstract and may be used to create custom data providers.
System.Data.SqlTypes : Provides classes for native data types in SQL Server.
System.Xml : Provides classes for processing XML.
System.Xml.Schema : Provides classes for processing XML Schema Definition (XSD) schema files.
System.Xml.Xsl : Provides classes for processing Extensible Stylesheet Transformation (XSLT) transforms.
The DataSet side of the architecture is contained within the System.Data namespace. The .NET data provider side of the architecture depends on the nature of the backend data source and is contained either within the System.Data.SqlClient namespace or within the System.Data.OleDb namespace. The DataSet is strongly integrated with XML. The DataSet has no direct link to the data source but instead communicates via the .NET data provider.
Important ADO.NET Objects
Connection: Provides connectivity to the data source.
Command: Enables access to database commands for returning data, modifying data, running stored procedures, and sending or retrieving parameter information. Includes explicit functionality such as the ExecuteScalar() method for queries that return one summarized value and the ExecuteNonQuery() method for commands that do not return a resultset.
DataReader: Provides highly optimized, non-cached, forward-only, read-only streaming data access.
XmlReader: Provides highly optimized, non-cached, forward-only, read-only access to an XML data stream. This object contains the resultset for SQL Server stored procedures that contain the FOR XML clause. This object cannot be used for stored procedures that return standard resultsets.
DataAdapter: Provides the bridge between the DataSet object and the data source. The DataAdapter object uses a set of Command objects for selecting, inserting, updating, and deleting data in the data source. The DataSet communicates directly with the DataAdapter object, which in turn communicates the request to the data source.
DataSet: An in-memory representation of one or more tables with records, constraints on columns, and relations among tables. You can use it for storing and manipulating disconnected relational data, flat data, or XML data.
Typed DataSet: A specialized class that inherits from the DataSet class and that provides access to data using strongly typed methods, properties, and events. You can access data tables and columns by name, rather than by using collection-based syntax. A typed DataSet is generated using XSD schema information. The .NET Framework SDK provides a command-line tool called XSD.exe for generating typed DataSets. Visual Studio.NET provides alternate access to this tool within the XML Designer.”
DataView: Provides an optimized, customized view of a DataTable. One DataSet object can have several associated DataView objects, each of which contains a different, filtered view of the same data set. DataView objects are also disconnected from the parent data source.