Getting Started with ADO.NET – Tutorial For Absolute Beginners

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.

Architecture of ADO.NET
Architecture of ADO.NET

ADO.NET Data Providers

ADO.NET has three commonly used data providers.

  1. OleDb Data Provider
  2. SQL Data Provider
  3. 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

ADO.NET OleDb Data Provider
ADO.NET OleDb Data Provider

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

ADO.NET SQL Data Provider
ADO.NET SQL Data Provider

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

ADO.NET ODBC Data Provider
ADO.NET ODBC Data Provider

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.

  1. 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

    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.

    Components of Connection String

    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. 

    – SQL Server Security Authentication

    To use Integrated security you need to specify the User ID and the Password.

  2. 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

    You can also create the Command object as

  3. SQL DataReader Object

    When the command is executed then SqlDataReader is used to read the record with the help of ExecuteReader() method.

     

CRUD SQL Example

Learn more about Database Connectivity.

Hope that you like this tutorial, Stay tuned for more upcoming tutorials. Stay Blessed!

Leave a Reply

Your email address will not be published. Required fields are marked *