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 uses ADO.NET?
  3. Components of ADO.NET
  4. What are .NET Data Providers?
What is ADO.NET?

ADO stands for Microsoft ActiveX Data Objects. ADO.NET is one of the Microsoft’s Data Access technology. It is a part of .Net Framework which is used to establish 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 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 uses ADO.NET?

ADO.NET can be used to develop any type of .NET Applications. 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 and DataAdapter 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 are .NET Data Providers?

The Database can not directly execute our C# code, it only understand 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 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 retrieve 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 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 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 are prefixed the word Oracle and these classes are used to communicate with 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 command, and retrieve data into .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 sources. 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 *