If you have gone through my previous article, i.e., What is ADO.NET? , you must know that ADO.NET is nothing but a set of libraries. It wouldn't be wrong to say that it is a collection of classes, and to communicate with any data source, we simply need to create the related object and call the required methods.
Different data providers help interact with various data sources. For example, to communicate with SQL Server, we have System.Data.SqlClient and similarly for Oracle, we have System.Data.OracleClient.
Each of these data providers contains four main classes, which are the pillars of ADO.NET:
Connection Class
It is used to establish the connection to the corresponding data source. The Microsoft ADO.NET team has created this class as a Sealed class, which means other classes cannot inherit it. Depending on the provider, the different connection classes are:
- SQLConnection: to establish an open connection to the SQL Server database.
- OracleConnection : to establish an open connection to the Oracle database.
- OleDbConnection: to establish an open connection to the OleDb data sources, like Excel and Access.
- OdbcConnection: to establish an open connection to the Odbc data source.
Command Class
This class is used to store and execute SQL statements for the corresponding database. Like the SqlConnection Class, it is also a sealed class, so other classes cannot inherit it. Depending on the provider, the different command classes are:
- SQLCommand: to store and execute SQL statements for SQL Server database.
- OracleCommand: to store and execute SQL statements for the Oracle database.
- OleDbCommand: to store and execute SQL statements for OleDb data sources like Excel and Access.
- OdbcCommand: to store and execute SQL statements for Odbc data source.
DataReader Class
This class is used to read data from the corresponding database. It reads data in a forward-only stream of rows from a SQL Server database. It is also a sealed class, like SqlConnection and SqlCommand, which means it cannot be inherited as well. It implements the IDisposable interface; always remember that classes implementing the IDisposable interface are automatically disposed of if used inside a 'using' statement. Depending on the provider, the different DataReader classes are:
- SQLDataReader: to read data from SQL Server database.
- OracleDataReader: to read data from the Oracle database.
- OleDbDataReader: to read data from an OleDb data source like Excel and Access.
- OdbcDataReader: to read data from Odbc Data Source.
DataAdapter Class
The DataAdapter sits between the DataSet and data sources like SQL Server and acts as a bridge between them, which opens the connection, fills the DataSet, and closes the connection. The DataAdapter is a class that wraps the SqlCommand and SqlConnection, meaning that a DataAdapter instance requires a SqlConnection and a SqlCommand object. The DataAdapter is not only used to fill the DataSet, but it is also capable of updating the data source. This means that any changes made to DataSets can be saved to the data source with the help of the DataAdapter, which we will discuss in a later article. Depending on the provider, the different DataAdapter classes are:
- SQLDataAdapter: bridge between a DataSet and SQL Server database.
- OracleDataAdapter: bridge between a DataSet and Oracle database.
- OleDbDataAdapter: bridge between a DataSet and an OleDb Data Source like Excel and Access.
- OdbcDataAdapter: bridge between a DataSet and Odbc Data Source.
We will discuss each of these one by one in the upcoming articles.
