ADO .NET is a data access and manipulation protocol. Many applications need data access at one point of time making it a crucial component when working with applications. Data access is making the application interact with a database, where all the data is stored. Different applications have different requirements for database access.

Visual Basic .NET uses ADO .NET (Active X Data Object). Let's see why ADO .NET is superior to ADO.


How and where do you declare the ADO.Net objects? What are the functionalities which are available after the ADO.Net object is declared? To know all this, read erp .net books.

Evolution of ADO.NET

DAO (data access model) was created for local databases with the built-in Jet engine. Next came RDO (Remote Data Object) and ADO (Active Data Object) which were designed for Client Server architectures and ADO was superior of the two.

In ADO, all the data is contained in a recordset object which had problems when implemented on the network and penetrating firewalls. ADO is a connected data access, which means that when a connection to the database is established the connection remains open till the application is closed. Keeping the connection open for the lifetime of the application raises concerns about database security and network traffic. Also, open database connections use system resources to a maximum extent making the system performance less effective.

Why ADO.NET?

To overcome with some of the problems mentioned above, ADO .NET came into existence. ADO .NET addresses the above mentioned problems by maintaining a disconnected database access model which means, when an application interacts with the database, the connection is opened to serve the request of the application and is closed as soon as the request is completed. Likewise, if a database is Updated, the connection is opened long enough to complete the Update operation and is closed. By keeping connections open for only a minimum period of time, ADO .NET conserves system resources and provides maximum security for databases and also has less impact on system performance. Also, ADO .NET when interacting with the database uses XML and converts all the data into XML format for database related operations making them more efficient.

The ADO.NET Data Architecture

Data Access in ADO.NET relies on two components: DataSet and Data Provider.

DataSet

The dataset is a disconnected, in-memory representation of data. It can be considered as a local copy of the relevant portions of the database. The DataSet is persisted in memory and the data in it can be manipulated and updated independent of the database. When the use of this DataSet is finished, changes can be made back to the central database for updating. The data in DataSet can be loaded from any valid data source like an Oracle database or from a Microsoft Access database.

Data Provider

The Data Provider is responsible for providing and maintaining the connection to the database. A DataProvider is a set of related components that work together to provide data in an efficient and performance driven manner. The .NET Framework currently comes with two DataProviders: the SQLData Provider which is designed only to work with Microsoft's SQLServer 7.0 or later and the OleDb DataProvider which allows us to connect to other types of databases like Access and Oracle. Each DataProvider consists of the following component classes:

The Connection object which provides a connection to the database
The Command object which is used to execute a command
The DataReader object which provides a forward-only, read only, connected recordset
The DataAdapter object which populates a disconnected DataSet with data and performs update

Data access with ADO.NET can be summarized as follows:

A connection object establishes the connection for the application with the database. The command object provides direct execution of the command to the database. If the command returns more than a single value, the command object returns a DataReader to provide the data. Alternatively, the DataAdapter can be used to fill the Dataset object. The database can be updated using the command object or the DataAdapter.

Component classes that make up the Data Providers

The Connection Object

The Connection object creates the connection to the database. Visual Studio .NET provides two types of Connection classes: the SqlConnection object, which is designed specifically to connect to SQLServer 7.0 or later, and the OleDbConnection object, which can provide connections to a wide range of database types like Access and Oracle. The Connection string contains all of the information required to open a connection to the database.

The Command Object

The Command object is represented by two corresponding classes: SqlCommand and OleDbCommand. Command objects are used to execute commands to a database across a data connection. The Command objects can be used to execute stored procedures on the database, SQL commands, or return complete tables directly. Command objects provide three methods that are used to execute commands on the database:

ExecuteNonQuery: Executes commands that have no return values such as INSERT, UPDATE or DELETE
ExecuteScalar: Returns a single value from a database query
ExecuteReader: Returns a result set by way of a DataReader object

The DataReader Object

The DataReader object provides a forward-only, read-only, connected stream recordset from a database. Unlike other components of the Data Provider, DataReader objects cannot be directly instantiated. Rather, the DataReader is returned as the result of the Command object's ExecuteReader method. The SqlCommand.ExecuteReader method returns a SqlDataReader object, and the OleDbCommand.ExecuteReader method returns an OleDbDataReader object. The DataReader can provide rows of data directly to application logic when you do not need to keep the data cached in memory. Because only one row is in memory at a time, the DataReader provides the lowest overhead in terms of system performance but requires the exclusive use of an open Connection object for the lifetime of the DataReader.

The DataAdapter Object

The DataAdapter is the class at the core of ADO .NET's disconnected data access. It is essentially the middleman facilitating all communication between the database and a DataSet. The DataAdapter is used either to fill a DataTable or DataSet with data from the database with it's Fill method. After the memory-resident data has been manipulated, the DataAdapter can commit the changes to the database by calling the Update method. The DataAdapter provides four properties that represent database commands:

SelectCommand
InsertCommand
DeleteCommand
UpdateCommand

When the Update method is called, changes in the DataSet are copied back to the database and the appropriate InsertCommand, DeleteCommand, or UpdateCommand is executed.

namespaces that are included in ADO.Net

System.Data.SQLClient - Contains classes for connecting to Microsoft SQL Server.

System.Data.OLEDB - Contains classes for connecting to a data source that has an OLEDB provider.

System.Data.Odbc - Contains classes for connecting to a data source that has an ODBC driver.

System.Data.OracleClient - Contains classes for connecting to an Oracle database server.

ADO.Net classes

System.Data.SQLClient namespace contains the following three classes.

SqlConnection
SqlCommand
SqlDataReader


System.Data.OLEDB namespace contains the following three classes.

OleDbConnection
OleDbCommand
OleDbDataReader

We can use the following ado.net classes when working with datasets.

Dataadapter
Dataset
Datatable
Datarelation
Dataview

Other Classes in the Ado.net model.

Transaction class
Exception class
Error class

Performance with ADO.net

With ADO.Net, performance hits are no more an issue. ADO.Net passes extensible markup language from layer to layer seamlessly and has reduced processing overheads and increasing performce by more than two times.

Scalabilty with ADO.Net

ADO.Net handles disconnected data by focussing on the three tasks of get the data, process the data and get the data out. This focussed processing frees up valuable resources on the servers and allows more requests to be handled.

Interoperability

The transportation protocol used in ADO.net is XML and so any component that can handle XML can handle ADO.Net.

Summary

ADO.Net is a superior technology compared to ADO. Common tasks we can do using ado.net are retrieving records from a database table, inserting new records, updating records and deleting records. ADO.Net is used in any database application today. It is preferred over other technologies because of its performnce, scalability and interoperability.