Most 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. VB .NET uses ADO .NET (Active X Data
Object) as it’s data access and manipulation protocol which also enables us to work
with data on the Internet. Let’s take a look why ADO .NET came into picture replacing
ADO.
Evolution of ADO.NET
The first data access model, DAO (data access model) was
created for local databases with the built-in Jet engine which had performance and
functionality issues. Next came RDO (Remote Data Object)
and ADO (Active Data Object) which were designed for Client
Server architectures but soon ADO took over RDO. ADO was a good architecture but as
the language changes so is the technology. With ADO, all the data is contained in
a recordset object which had problems when implemented on
the network and penetrating firewalls. ADO was a connected data
access, which means that when a connection to the database is established the connection
remains open until the application is closed. Leaving the connection open for the
lifetime of the application raises concerns about database security and network
traffic. Also, as databases are becoming increasingly important and as they are
serving more people, a connected data access model makes us think about its productivity.
For example, an application with connected data access may do well when connected
to two clients, the same may do poorly when connected to 10 and might be unusable
when connected to 100 or more. Also, open database connections use system resources
to a maximum extent making the system performance less effective.
Why ADO.NET?
To cope up 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 Microsoft SQL server
database, 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 SQL Data Provider which
is designed only to work with Microsoft’s SQL Server 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. Microsoft Visual Studio
.NET provides two types of Connection classes: the SqlConnection object,
which is designed specifically to connect to Microsoft SQL Server 7.0 or later, and
the OleDbConnection object, which can provide connections
to a wide range of database types like Microsoft Access and Oracle. The Connection
object 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.
