What is ADO.NET

To understand ADO.NET, analyze the figure shown below:

From the above figure, it is very clear that every application, whether it is a web application like ASP.NET WebForms app, ASP.NET MVC app, or a desktop application like WPF or Winforms app, or a web service like Web API or WCF, needs to communicate with a data source like SQL Server. However, SQL Server doesn't understand .NET languages like C# or VB. A big question that arises in mind is how to communicate with data sources like SQL Server from any .NET application. Here comes ADO.NET.

ADO.NET is the core data access technology for .NET languages. With the help of ADO.NET, we can communicate with data sources like SQL Server using our preferred languages, such as C# or VB. You can think of ADO.NET as a set of classes or a framework that can be used to interact with data sources, such as databases and XML files. The data can then be consumed in any .NET application, whether it is a desktop application or a web application. ADO stands for Microsoft ActiveX Data Objects.

How Does Sql Server Understand C#?

Databases only understand SQL. If a .NET application (Web, Desktop, Console, etc.) has to retrieve data, the 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

ADO.NET provides some classes that enable us to accomplish all these tasks. For example, if we want to communicate with SQL Server, then we can use the following code:

SqlConnection con = new SqlConnection("data source=.; database=Sample; integrated security=SSPI");
SqlCommand cmd = new SqlCommand("Select * from tblEmplpoyee", con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
GridView1.DataSource = rdr;
GridView1.DataBind();
con.Close();

Please note that we are using objects of classes like SQLConnection, SQLCommand, and SQLDataReader, where all these objects are prefixed with the word SQL, because they represent the same data source that is SQL Server. All these classes are present in the System.Data.SqlClient namespace, so ensure to import this namespace before using these objects.

So, we can say that System.Data.SqlClient is the .NET data provider for SQL Server.

Similarly, follow the code below to connect to the Oracle Database and retrieve data:

OracleConnection con = new OracleConnection("Oracle Database Connection String");
OracleCommand cmd = new OracleCommand("Select * from tblEmployee", con);
con.Open();
OracleDataReader rdr = cmd.ExecuteReader();
GridView1.DataSource = rdr;
GridView1.DataBind();
con.Close();

Please note that here, we are using OracleConnection, OracleCommand, and OracleDataReader classes and their objects are prefixed with the word Oracle because the data provider is OracleClient, so don't forget to import the namespace System.Data.OracleClient as all these classes are present in this namespace only.

So, we can say that the .NET data provider for Oracle is System.Data.OracleClient.

Similarly, if we want to connect to OLEDB datasources like Excel, Access, etc, we can use OleDbConnection, OleDbCommand, and OleDbDataReader classes.

So, .NET data provider for OLEDB is System.Data.OleDb.

Thus, we have the following .NET data providers:

• Data provider for SQL Server is System.Data.SqlClient
• Data provider for Oracle is System.Data.OracleClient
• Data provider for OLEDB is System.Data.OleDb
• Data provider for ODBC is System.Data.Odbc

Please note that depending on the provider, the following ADO.NET objects have a different prefix:

1. Connection - SQLConnection for SQL Server, OracleConnection for Oracle, OleDbConnection and OdbcConnection for MS Access, etc.
2. Command - SQLCommand for SQL Server, OracleCommand for Oracle Server, OleDbCommand and OdbcCommand for MS Access, etc.
3. DataReader - SQLDataReader for SQL Server, OracleDataReader for Oracle, OleDbDataReader and OdbcDataReader for MS Access, etc.
4. DataAdapter - SQLDataAdapter for SQL Server, OracleDataAdapter for Oracle, OleDbDataAdapter and OdbcDataAdapter for MS Access, etc.

The DataSet object is not provider-specific. Once we connect to a database, execute the command, and retrieve data into the .NET application, the data will be stored in a DataSet and work independently of the database. We will discuss DataSet and DataAdapter in later articles.