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 pointer 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. 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 and delete data 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 which 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 fast access to data. 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. 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.

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

Please have a look at the following image to understand the data providers in a better manner.

.NET Data Providers

Note: 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 is not specific to provider-specific. Once you connect to a database, execute the command, and retrieve 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 step by step. 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 *