ADO.NET is described by a set of classes that describe different elements of the data. The DataSet object derives from System.Data.DataSet class, which is used to describe a subset of the overall database.
The dataset is made up of tables, columns, and rows and is also described by the relations between these objects and the constraints under which they are created.
The Dataset includesDataColumn, Constraints, and DataRow are under the DataTable. DataTable, DataSet, and DataRelation are under the System.Data.
A database table is composed of a set of rows containing data, divided into columns that define that data. In ADO.NET, a DataTable object is used to represent the data of one database table.
DataRelation objects describe the relationships between the data columns in different data tables. For example, if one table tracks inventory and another records suppliers, there may be a set of DataRelation objects describing commodities that appear on both tables.
Each instance of the DataColumn class describes the data in one column of a DataTable object. Each column can have any number of extended properties, which define the data types and values that can appear in the column.
The first column in a data table is the primary key for that data table.
The name of the column is set using the ColumnName property. If no name is specified, a column number will be generated automatically.
You use a Constraint object to enforce a constraint on one or more DataColumn objects.
Each instance of the DataRow class describes the data in one row of a data table. A DataColumn object describes a property that a record must have, and a DataRow object describes the set of values that make up a particular record.
The DataRow class allows you to maintain different versions of the data in a column. This means you can roll back changes made to the table.
The DataSet, DataTable and DataRelation objects describe the schema definition of a dataset, but this framework must be populated with actual data from the database before it can be worked on.
This involves the DataAdapter class and the managed providers. Each .NET data provider has a corresponding DataAdapter that you use as a bridge between a data source and a dataset.
A DataAdapter object represents a set of data commands and a database connection that are used to fill the dataset and update the data source.
Depending on which database you are using, you can use an instance of the SqlDataAdapter or OleDbAdapter classes to transfer information between the dataset and database.
You use the System.Data.SqlClient namespace when you want to work with a SQL Server .NET data provider or the System.Data.OleDb namespace when you want to work with an OLE DB .NET data provider.
Both have methods for populating the dataset and updating the database with changes made to the dataset:
- Database Connections
- Database Commands
- Data Readers
A database connection object maintains a connection between a dataset and data source. You use OleDbConnection for an OLE DB .NET data provider and SqlConnection for a SQL Server.NET data provider.
A database command object lets you send a command, usually a SQL statement or a stored procedure, to the database. You use OleDbCommand for an OLE DB .NET data provider and SqlCommand for a SQL Server .NET data provider.
A data reader object allows you to read information directly from the database, without creating a DataSet object. It is not as flexible as using a dataset, because it only allows you to step through each record in turn, but takes up less bandwidth than populating a dataset. You use OleDbDataReader for an OLE DB .NET data provider and SqlDataReader for a SQL Server .NET data provider.