Introduction to ADO.NET
ADO stands for ActiveX Data Object. ADO.NET is one of the .Net Framework’s modules that is used to build a connection between application and data sources. Data sources can be such as SQL Server and XML (Extensible Markup Language). ADO.NET consists of classes that can be used to perform CRUD operation (Create, Read, Update and Delete).
Before ADO.NET we use ADO to access data from the database. Basically ADO has an automatic driver detection technique but it only provides a connected environment so the efficiency of the system may decrease.
ADO.NET is a database technology used by the .NET platform (introduced in January 2002).
In fact, it is a set of classes used to communicate between an application’s front end and a database.
It supports both connected and disconnection mode of data access.
Namespaces used in ADO.NET
System.Data
It contains the common classes for connecting and fetching data from the database. Classes are like DataTable, DataSet, DataView, etc.
System.Data.SqlClient
It contains classes for connecting and fetching data from the SQL Server database. Classes are like SqlDataAdapter, SqlDataReader, etc.
System.Data.OracleClient
It contains classes for connecting and fetching data from the Oracle database. Classes are like OracleDataAdapter, OracleDataReader, etc.
System.Data.OleDb
It contains classes for connecting and fetching data from any database (like MS Access, Oracle, SQL Server, MySQL). Classes are like OleDbDataAdapter, OleDbDataReader, etc.
ADO.NET Architecture
Component of ADO.NET Architecture
The two key components of ADO.NET are:
- Data Provider
- DataSet
1. Data Provider
A data provider is a set of ADO.NET classes that allow us to access a database. Basically, it is a bridge between our application and data source.
There are following Data Providers:
SqlServer Data Provider – It is used to access data from the SqlServer database (for version 7.0 or later).
Oracle Data Provider – It is used to access data from the Oracle database (for version 8i or later).
OleDb Data Provider – It is used to access data from any database (like MS Access, MySQL).
The four core objects from the .Net Framework provides the functionality of Data Providers in the ADO.NET:
-
Connection Object
The Connection object is used to create an open connection to a data source. Through this connection, we can access and manipulate the database.
-
Command Object
To execute a command the Command Object is used. It provides three methods which are used to execute commands on the database:
ExecuteNonQuery – Executes commands that have no return values (INSERT, UPDATE or DELETE).
ExecuteScalar – Executes commands that return a single value from a database query.
ExecuteReader – Returns a result set by way of a DataReader object.
-
Data Reader Object
The DataReader Object provides a forward-only, read-only and connected recordset.
Limitations of the DataReader – There is no possibility to sort, filter, or manipulate the data while using a DataReader since it is read-only and forward-only.
-
Data Adapter Object
The DataAdapter serves as a bridge between a DataSet and data source for retrieving and saving data.
The DataAdapter provides this bridge by using Fill to load data from the data source into the DataSet and using Update to send changes made in the DataSet back to the data source.
The data adapter objects connect command objects to a DataSet object.
They provide the means for the exchange of data between the data store and the tables in the DataSet.
An OleDbDataAdapter object is used with an OLE-DB provider and a SqlDataAdapter object uses tabular data services with MS-SQL Server.
2. DataSet
Basically DataSet is a small data structure that may contain multiple data tables from multiple sources.
The information in a DataSet is created in the form of XML and is stored with .xsd extension.
It supports the disconnected mode of data access. It has both scrolling mode means forward scrolling mode and backward scrolling mode (fetching of data).
DataSet can have multiple data tables from multiple sources but DataReader is able to read only a single data table at a time.
The typical steps for creating and using a DataSet are:
- Create a DataSet object
- Connect to a database
- Fill the DataSet with one or more table(s) or view(s)
- Disconnect from the database
- Use the DataSet in the application
