ADO.Net Overview

ADO.NET Components

The two main components of ADO.NET 3.0 for accessing and manipulating data are the .NET Framework data providers and the DataSet.

.NET Framework Data Providers

The .NET Framework Data Providers are components that have been explicitly designed for data manipulation and fast, forward-only, read-only access to data. The Connection object provides connectivity to a data source. The Command object enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information. The DataReader provides a high-performance stream of data from the data source. Finally, the DataAdapter provides the bridge between the DataSet object and the data source. The DataAdapter uses Command objects to execute SQL commands at the data source to both load the DataSet with data and reconcile changes that were made to the data in the DataSet back to the data source. For more information, see .NET Framework Data Providers (ADO.NET) and Retrieving and Modifying Data in ADO.NET.

The DataSet

The ADO.NET DataSet is explicitly designed for data access independent of any data source. As a result, it can be used with multiple and differing data sources, used with XML data, or used to manage data local to the application. The DataSet contains a collection of one or more DataTable objects consisting of rows and columns of data, and also primary key, foreign key, constraint, and relation information about the data in the DataTable objects. For more information, see DataSets, DataTables, and DataViews (ADO.NET).

Following grid shows the description of ADO.net components. Courtesy: Ritvik Pandya (rtvk05@gmail.com)

S.N.	Class	Description
1	DataSet	The DataSet is a local bugger of tables or a collection of disconnected record sets.
2	DataTable	A DataTable is used to contain data in tabular form using rows and columns.
3	DataRow	Represents a single record or row in DataTable.
4	DataColumn	Represents a column or field of a DataTable.
5	DataRelation	Represents the relationship between different tables in a data set.
6	Constraint	Represents the constraints or limitations that apply to a particular field or column.

The following diagram illustrates the relationship between a .NET Framework data provider and a DataSet.

ADO.net Architecture

Performing Common Data Access Tasks with ADO.net

ADO.net also contains some database specific classes. This means that different database system providers may provide classes (or Drivers) optimized for their particular database system. Microsoft itself has provided the specialized and optimized classes for their SQL Server database system. The name if these classes start with “Sql” and are contained in the System.Data.SqlClient namespace.

Similarly, Oracle has also providers its classes (drivers) optimized for the Oracle DB system. Microsoft has also provided the general classes which can connect your application to any OEL supported database server. The name of these classes start with ‘OleDb’ and these are contained in the System.Data.OleDb namespace.

In fact, we can use OldeDb classes to connect to SQL Server or Oracle Database; using the database specific classes generally provides optimized performance, however.

S.N.	Class	Description
1	SqlConnection, OleDbConnection	Represents a connection to the database system.
2	SqlCommand, OleDbCommand	Represents SQL Query.
3	SqlDataAdapter, OldeDbDataAdapter	A class that connects to the database system, fetches the records and fills the dataset.
4	SqlDataReader, OleDbDataReader	A stream that reads data from the database in a connected design.
5	SqlParameter, OldeDbParameter	Represents a parameter to a stored procedure.

Access Data using ADO.net

Data access using ADO.Net involves the following steps:

·         Defining the connection string for the database server.

·         Defining the connection (SqlConnection or OleDbConnection) to the database using the connection string.

·         Defining the command (SqlCommand or OleDbCommand) or command string that contains the query.

·         Defining the data adapter (SqlDataAdapter or OleDbDataAdapter) using the command string and the connection object.)

·         Creating a new DataSet object.

·         If the command is SELECT, filling the dataset object with the result of the query through the data adapter.

·         Reading the records from the DataTables in the datasets using the DataRow and DataColumn objects.

·         If the command is UPDATE, INSERT or DELETE, then updating the dataset through the data adapter.

·         Accepting to save the changes in the dataset to the database.