In this tutorial, you will learn about ADO.NET, its architecture along with its usage. You will also learn how to use ADO.NET with SQL Data Provider.
Introduction
ADO stands for ActiveX Data Objects. It is a Microsoft technology. Microsoft ActiveX provides a programming interface to access data from the database. ADO.NET is designed to efficiently access data, efficiently with the help of object-oriented libraries. It is automatically installed with Microsoft IIS Server. ADO.NET provides support for XML and disconnected recordsets.
A recordset is a data source which is disconnected from the database to assign the connection to some other recordset.
The architecture of ADO.NET
ADO.NET act as an adapter between data source. i.e. database and the Forms which displays and process the data. The below image can explain the architecture of ADO.NET, more appropriately.

ADO.NET Data Providers
ADO.NET has three commonly used data providers.
- OleDb Data Provider
- SQL Data Provider
- ODBC Data Provider
Other Data providers include Oracle Data Provider and Borland Data Provider.
1. OleDb Data Provider Description
Include: Using System.data.OleDb;
API Prefix: OleDb
Description: To communicate with the Data Sources that expose an OleDb interface, i.e. Access or Excel.
Data Adapter: OleDbDataAdapter
Command Builder: OleDbCommandBuilder
Command: OleDbCommand
Reader: OleDbDataReader
Connection: OleDbConnection

2. SQL Data Provider Description
Include: Using System.data.SqlClient;
API Prefix: Sql
Description: To interact with Microsoft SQL Server.
Data Adapter: SqlDataAdapter
Command Builder: SqlCommandBuilder
Command: SqlCommand
Reader: SqlDataReader
Connection: SqlConnection

3. ODBC Data Provider Description
Include: Using System.data.Odbc;
API Prefix: Odbc
Description: To interact with Data Sources which have an ODBC interface. Normally older data bases.
Data Adapter: OdbcDataAdapter
Command Builder: OdbcCommandBuilder
Command: OdbcCommand
Reader: OdbcDataReader
Connection: OdbcConnection

4. Oracle Data Provider Description
API Prefix: Oracle
Description: To interact with Oracle Databases.
5. Borland Data Provider Description
API Prefix: Bdp
Description: It provides Generic access to many databases such as Interbase, SQL Server, IBM DB2, and Oracle.
SQL Data Provider
The SQL Data Provider is widely used with ADO.NET, it includes the following objects.
-
SQL Connection Object
The SQL Connection Object is used to connect to the database with the help of a connection string. SqlConnection constructor takes the connection String as parameters.
Syntax
1SqlConnection conn = new SqlConnection ("Data Source =(local); Initial Catalog= Northwind; Integrated Security= SSPI ");Connection String
Connection string provides exact attributes of the database.
Components of Connection String
Connection String Include Data Source, Initial Catalog, Integrated Security, User ID and Password.
Authentication
The Connection String of SQL Connection Object has two types of authentication.
– Trusted Authentication
To use Trusted Authentication you must use
Integrated Security = SSPI.1Data Source = <ServerName>; Initial Catalog= <DatabaseName>; Integrated Security=SSPI– SQL Server Security Authentication
To use Integrated security you need to specify the User ID and the Password.
1Data Source = <ServerName>; Initial Catalog = <DatabaseName>; User Id= UserName; Password= UserPassword -
SQL Command Object
The SQL Command Object sends the command i.e. SQL Query to the database. SqlCommand takes SQL query and SqlConnection object as parameters.
Syntax
1SqlCommand cmd = new SqlCommand("select ProductName from Products",conn);You can also create the Command object as
123SqlCommand cmd = new SqlCommand();cmd.CommandText = "select ProductName from Products";cmd.Connection = conn; -
SQL DataReader Object
When the command is executed then SqlDataReader is used to read the record with the help of ExecuteReader() method.
12SqlCommand cmd = new SqlCommand("select ProductName from Products",conn);SqlDataReader reader=cmd.ExecuteReader();//The reader now contains list of product name from Products table.
CRUD SQL Example
Learn more about Database Connectivity.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
Public class DbConnection { //Try catch block is not mentioned here but it is required when you implement the program private SqlConnection conn; public void createConnection() { //creating connection object conn = new SqlConnection ("Data Source=(local);Initial Catalog=Northwind; Integrated Security=SSPI"); } //end createConnection method public void InsertProductRecord() { createConnection(); conn.Open(); String sqlQuery="Insert into Products(ProductName) values('Vanial')"; SqlCommand cmd = new SqlCommand(sqlQuery, conn); cmd.ExecuteNonQuery(); } //end InsertProductRecord public void selectProductRecord() { createConnection(); conn.Open(); SqlCommand cmd = new SqlCommand("Select ProductName from Products", conn); SqlDataReader rdr = cmd.ExecuteReader(); while(rdr.Read()) { Console.WriteLine(rdr[0]); //from selected row of reader it selects first column } } //end selectProductRecord public void updateProductRecord() { createConnection(); conn.Open(); String sqlQuery="update Products set ProductName='Vanail chlorid' where ProductID=78"; SqlCommand cmd = new SqlCommand(sqlQuery); cmd.Connection = conn; //passing connection as separtate variable cmd.ExecuteNonQuery(); } //end updateProductRecord public void deleteProductRecord() { createConnection(); conn.Open(); String sqlQuery="Delete from Products where ProductID=78"; SqlCommand cmd = new SqlCommand(); cmd.CommandText = sqlQuery; cmd.Connection = conn; cmd.ExecuteNonQuery(); } //end deleteProductRecord public void numberOfRecords() { int count = -1; createConnection(); conn.Open(); SqlCommand cmd = new SqlCommand("select count(*) from Products", conn); count = (int)cmd.ExecuteScalar(); } //end numberOfRecords method } //end DbConnection |
Hope that you like this tutorial, Stay tuned for more upcoming tutorials. Stay Blessed!