In order to understand ADO.Net, analyze the figure shown below.
From the above figure, it is very clear that every application whether it be a web application like Asp.Net web Forms app and Asp.Net MVC app or a Desktop application like WPF app or Winforms app or a Web Service like Web API or WCF, needs to communicate to a data source like SQL Server, but SQL Server doesn't understand .Net languages Like c# or VB, then a big a question that arises in mind is how to communicate with data sources like SQL Server from any .Net Application, Here comes the Ado.Net, DO.NET is the core data access technology for .NET languages. With the help of Ado.Net, we can communicate to Data Sources like SQL Server using our favorite Language c# or VB.You can think of ADO.NET, as a set of classes (Framework), that can be used to interact with data sources like Databases and XML files. This data can then be consumed in any .NET application. ADO stands for Microsoft ActiveX Data Objects.
How does Sql Server understand C#?SQL Server doesn't understand any .Net language, it is .Net Data providers which convert the .Net language's query to corresponding transact SQL.
Databases only understand SQL. If a .NET application (Web, Desktop, Console etc.) has to retrieve data, then 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 enables us to accomplish all these task , for example if we want to communicate with Sql Server then we can use 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();
Notice that we are using SQLConnection, SQLCommand and SQLDataReader classes . All the objects are prefixed with the word SQL. All these classes are present in System.Data.SqlClient namespace.
So, we can say that the .NET data provider for SQL Server is System.Data.SqlClient.
Similarly follow below code to connect to 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();
Notice that we are using OracleConnection, OracleCommand and OracleDataReader classes . All the objects are prefixed with the word Oracle. All these classes are present in System.Data.OracleClient namespace.
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 following .NET Data Providers
Data Provider for SQL Server - System.Data.SqlClient
Data Provider for Oracle - System.Data.OracleClient
Data Provider for OLEDB - System.Data.OleDb
Data Provider for ODBC - System.Data.Odbc
Please note that, depending on the provider, the following ADO.NET objects have a different prefix
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
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 can then be stored in a DataSet and work independently of the database. we will discuss DataSet and DataAdapter in later articles.