ADO.NET Architecture

The ADO.NET Architecture consists of two major components:

1.      ADO.NET providers

2.      Datasets

ADO.NET Providers

·         The ADO.NET providers are responsible for transferring data between a database and a Visual Basic.NET application.

·         The Data Provider is responsible for providing and maintaining the connection to the database.

·         It is a set of classes that can be used for communicating with database, and holding/manipulating data.

·         The data provider connects to the data source on behalf of ADO.NET.

·         The data provider components are specific to data source.

·         There are two types of providers available in the ADO.NET model:

ü  SQL provider: Used for data transfer with SQL Server. The SQL server data-provider classes are present in the System.Data.SqlClient namespace.

ü  OLEDB provider:Used for data transfer with different kinds of database such as Oracle and MS Access. The OLEDB Data-provider classes are present in the System.Data.Oledb namespace. We can use OLE DB to connect Microsoft Access or Microsoft Excel frequently.

ü  ODBC: Used for data transfer with ODBC.  The ODBC data-provider classes are present in System.Data.OracleClient namespace.

The ADO.NET provider consists of the following components:

·         Connections: Used for establishing the connection to the data source.

·         Commands: Used for executing the commands on the data source.

·         Data Adapter: Used for populating the data from the data store into the dataset. (In short it is a bridge between Data source and Dataset object for transferring data.)

Connection Object

The connection object is used in Visual Basic .NET application to connect to the database. There are three types of connection objects available in VB.NET: SqlConnection, OledbConnection and ODBCConnection.

Commonly used properties of ADO.NET connection object

·         Connection String: The string that contains the text when the connection is established with any database.

·         Initial Catalog: The name of the database that will be used when the connection is established with any database.

·         DataSource:Used for defining the instance name of the database to which the connection is to be made.

Commonly used methods of connection object

·         Open: Used for opening the connection to any database. It does not take any parameter. The example for this method is: conn1.Open()

·         Close: Used for closing an existing connection of any database. It does not take any parameter. E.g., conn1.Close()

Command Object

It is used to store the command that needs to be executed on the database to which the connection is being established. It stores and executes the command for performing database manipulations.

Commonly used properties

·         CommandText: Stores the SQL command or the stored procedure name that is executed by the command object to manipulate or extract data from an SQL database.

·         CommandType:Stores the information about how the command text needs to be executed. For example, the common type property determines whether to execute the text in the command property as a table or as a stored procdedure.

·         Connection: Contains the name of the connection object used to connect to the database. E.g., SqlConnection, OledbConnection.

Commonly used methods of command object

·         ExecuteNonQuery: Used to execute the query specified in the command. It does not take any parameter. The example for this method is conn1.ExecuteNonQuery().

·         ExecuteReader:Used to execute the command specified when a datareader object is used or when a stored procedure needs to be executed using the command object.

Data Adapter

The DataAdapter should be used with the Dataset class for transferring data between the Visual Basic.Net application and the database. The Data adapter class is used for connecting and executing commands. The dataset object uses the methods and properties of the DataAdapter to transfer data between the dataset and the database.

Commonly properties

·         SelectCommand: Used to execute a select query on the database.

·         InsertCommand: Used to execute an insert query on the database.

·         UpdateCommand: Used to execute update query on the database.

·         DeleteCommand: Used to execute a delete query on the database.

·         TableMapping: Provides a mapping between the table and the dataset for representing the data as rows and columns in the dataset.

Common Methods

·         Fill: This method adds to or refreshes the data that matches with the data in the database. It requires the dataset name, which the adapter will refresh. E.g., DataAdapter1.Fill(dataset1)

·         Update: As the connection to the database is disconnected and needs to be updated when there are changes, this method needs to be explicitly invoked (call) to update the changes into the database after execution of the respective insert. E.g., DataAdapter1.Update(Dataset).

Note:

The second essential part of an architecture "Data sets" will be explained later. I request you not to skip this topic any how otherwise you won't be able to comprehend later part.

Edited By: Chaudhary Amit V.