Introduction to ADO.NET

Introduction to ADO.NET

In this article, I am going to give you a brief introduction to ADO.NET. As a .NET developer, you should be aware of ADO.NET. At the end of this article, you will understand the following pointers in detail.

  1. What is ADO.NET?
  2. What types of Applications use ADO.NET?
  3. Components of ADO.NET
  4. What is .NET Data Providers?
What is ADO.NET?

ADO stands for Microsoft ActiveX Data Objects. ADO.NET is one of Microsoft’s Data Access technology using which we can communicate with different data sources. It is a part of the .Net Framework which is used to establish a connection between the .NET Application and data sources. The Data sources can be SQL Server, Oracle, MySQL, and XML, etc. ADO.NET consists of a set of classes that can be used to connect, retrieve, insert, update and delete data (i.e. performing CRUD operation) from data sources. ADO.NET mainly uses System.Data.dll and System.Xml.dll.

What types of Applications use ADO.NET?

ADO.NET can be used to develop any type of .NET application. The following are some of the .NET applications where you can ADO.NET Data Access Technology to interact with a data source.

  1. ASP.NET Web Form Applications
  2. Windows Applications
  3. ASP.NET MVC Application
  4. Console Applications
  5. ASP.NET Web API Applications
Components of ADO.NET

Components are designed for data manipulation and faster data access. Connection, Command, DataReader, DataAdapter, DataSet, and DataView are the components of ADO.NET that are used to perform database operations. ADO.NET has two main components that are used for accessing and manipulating data. They are as follows:

  1. Data provider and
  2. DataSet.
What is .NET Data Providers?

The Database can not directly execute our C# code, it only understands SQL. So, if a .NET application needs to retrieve data or to do some insert, update, and delete operations from or to a database, then the .NET application needs to

  1. Connect to the Database
  2. Prepare an SQL Command
  3. Execute the Command
  4. Retrieve the results and display them in the application

And this is possible with the help of .NET Data Providers.

ADO.NET code to connect to SQL Server Database

The following image shows the sample ADO.NET code which is connecting to SQL Server Database and retrieves data. If you notice in the below image, here, we are using some classes such as SQLConnection, SQLCommand, and SQLDataReader. These classes are called Provider classes and these classes are responsible for interacting with the database and performing the CRUD operation. If you further notice all the classes are prefixed with the word SQL, it means these classes are going to interact with only the SQL Server database.

ADO.NET code to connect to SQL Server Database

All these classes are present in System.Data.SqlClient namespace. We can also say that the .NET data provider for the SQL Server database is System.Data.SqlClient.

ADO.NET code to connect to Oracle Database

The following code is for connecting to Oracle Database and retrieve data. If you notice, here we are using OracleConnection, OracleCommand, and OracleDataReader classes. That means all these classes have prefixed the word Oracle and these classes are used to communicate with the Oracle database only.

OracleConnection connection = new OracleConnection("data source=.; database=TestDB; integrated security=SSPI");
OracleCommand command = new OracleCommand("Select * from Customers", connection);
connection.Open();
OracleDataReader myReader = command.ExecuteReader();

while (myReader.Read())
{
     Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
}

connection.Close();

All the above classes are present in System.Data.OracleClient namespace. So, we can say that the .NET data provider for Oracle is System.Data.OracleClient.

Note: Similarly, if you want to communicate with OLEDB data sources such as Excel, Access, etc. then you need to use OleDbConnection, OleDbCommand, and OleDbDataReader classes. So, the .NET data provider for OLEDB data sources is System.Data.OleDb.

Different .NET Data Providers

ADO.NET Data Providers for Different Data Sources

ADO.NET Data Providers

Please have a look at the following image to understand the ADO.NET Data Providers in a better manner. As you can see, we have divided the diagram into three sections. The first section is the .NET Applications, the second section is the .NET Data Providers and the third section is the data sources. Based on the data source, you need to use the appropriate provider in your application.

.NET Data Providers

The point that you need to remember is depending on the provider, the ADO.NET objects (Connection, Command, DataReader, and DataAdapter) have a different prefix as shown below.

  1. Connection – SQLConnection, OracleConnection, OleDbConnection, OdbcConnection, etc.
  2. Command – SQLCommand, OracleCommand, OleDbCommand, OdbcCommand, etc.
  3. DataReader – SQLDataReader, OracleDataReader, OleDbDataReader, OdbcDataReader, etc.
  4. DataAdapter – SQLDataAdapter, OracleDataAdapter, OleDbDataAdapter, OdbcDataAdapter, etc.
DataSet:

The DataSet object in ADO.NET is not provider-specific. Once you connect to a database, execute the command, and retrieve the data into the .NET application. The data can then be stored in a DataSet and work independently of the database. So, it is used to access data independently from any data source. The DataSet contains a collection of one or more DataTable objects.

In our upcoming articles, we will discuss each of these ADO.NET objects in detail with simple as well as real-time examples. In the next article, I am going to discuss ADO.NET using SQL Server with Examples. Here, in this article, I try to give an overview of ADO.NET. I hope you enjoy this article.

Leave a Reply

Your email address will not be published. Required fields are marked *