Features
of ADO.Net
·
Disconnected Data
Architecture
With the advent of n-tier architecture and
distributed web application concept , Disconnected data architecture is
required for better performance and to server more no. of users.
In traditional application connection is
established and maintained throughout the running of the application. Also, connected
architecture uses intensive System resources and also restricts scalability.
This architecture is not suitable for Web Applications.
Disconnected data architecture means that
the connection between the Web Server and browser is disconnected after the
server processes the request from the browser. In this architecture
applications connect to the database only when they need to access or update
the data.
·
Data in Datasets
A Dataset is a virtual database that is
stored locally and allows to work with the data in the same way as working with
the original database.
·
Built in support
for XML.
ADO.Net uses XML automatically as the
format for transferring data from the database to the dataset and from the
dataset to other components.
Comparing ADO and ADO.Net
·
In Memory Data
Representation
ADO uses the Recordset object for in-memory
data representation while ADO.Net uses
the DataSet object.
A Recordset is like ADO.Net single table.
To retrieve records from multiple tables, we need to use join query.
Dataset is “virtual database” that contains
one or more tables, which are called data
tables. When Dataset contains data
from multiple tables, Dataset will be
having multiple DataTable objects. Dataset is a virtual database so it also
contain relationship between these Datatables. The relationship between
DataTable objects is represented using DataRelation object in the Dataset.
·
Data Navigation
ADO allows sequential access to the rows of
ADO.Net recordset via the MoveNext method.
ADO.Net allows Sequential as well as
non-sequential access to the rows of the tables in Dataset.
·
Use of Cursors
A cursor refers to a database element. It
is primarily used for managing the navigation of records and for updating data.
ADO supports Server side and Client side
cursors. ADO.Net doesn’t provide support for the cursors, because ADO.Net uses disconnected
architecture but it provides similar functionality through DataReader object,
which offers functionality of Forward only and Read only cursors.
·
Disconnected Data Access
ADO communicates with the database through
the calls to the OLE DB provider, but ADO.Net communicates with the database by
using a data adapter, which in turn communicates with the OLE DB provider or
SQL server.
·
Sharing data across
Applications
ADO uses COM marshalling for transferring a
disconnected Recordset, whereas ADO.Net uses XML for transferring data in the
form of a DataSet.
COM marshalling provides support for only
those data types that are defined by the COM standard. It is difficult to transfer through firewalls
because they can prevent COM marshalling requests.
Transferring data as XML, there is no
restriction on data types and transfer of data through firewall is possible
because firewalls allow passing of XML.
Benefits of ADO.Net
·
Interoperability
ADO.Net uses XML as the format for the
exchange of data so that any component that can understand XML can receive and
process the data.
ADO.Net is interoperable that is it can
easily operate with the applications that support XML.
·
Maintainability
Adding tier to a deployed application can
create problems, such as disturbance in data exchange or data transport capabilities between the tiers.
A solution is to use ADO.Net datasets to
implement the original application, because the tiers added to deployed ADO.Net
applicatons can easily exchange data through the use of datasets, which are
formatted in XML.
·
Programmability
·
Performance
ADO.NET
Components
ADO.Net provides two main components :
Dataset and the .NET data provider.
The following diagram illustrates the components of ADO.NET architecture.
ADO.NET
architecture
DataSet
The DataSet object is central to
supporting disconnected, distributed data scenarios with ADO.NET. The DataSet
is a memory-resident representation of data that provides a consistent
relational programming model regardless of the data source. It can be used with
multiple and differing data sources, used with XML data, or used to manage data
local to the application. The DataSet represents a complete set of data
including related tables, constraints, and relationships among the tables.
A DataSet refers to ADO.Net collection of
one or more tables or records from ADO.Net data source and information about
the relationship that exists between them. It contains tables, rows, columns,
constraints (such as primary key and foreign key constraints) and relationship
that exist between the tables. DataSet is used for temporary storage of records
that the application retrieves fro the database as ADO.Net cache in the memory.
The application can then work with the records in the dataset without having to
connect to the database again and again.
A DataSet is a virtual and local relational
database that contains desired data from the database.
DataSet class represents the ADO.Net
dataset and is stored in the System.Data. namespace.
Objects
in DataSet
|
Object
|
Description
|
|
DataSet
|
Represents a virtual, local relational
database in the memory that is used for temporary storage of data.
|
|
DataTable
|
Represents one table of in-memory data in
a dataset. ADO.Net DataTable object contains data in the form of rows and
columns.
|
|
DataRow
|
Represents a row containing data in a
DataTable object.
|
|
DataColumn
|
Represents ADO.Net column schema in a DataTable
object.
|
|
DataRelation
|
Represents the relationship that exists
between two DataTable objects.
|
|
Constraint
|
Represents a constraint for one or more
DataColumn objects.
|
THE .Net Data
Provider
.Net Data provider acts as a bridge between
the application and the data source; it is used to establish ADO.Net connection
with the data source, to execute commands, and to retrieve results.
Core
components
·
Connection Object
To
transfer data between an application and the database, we must connect to the
database. ADO.Net provides the Connection object, which enables us to establish
and manage a connection with the database.
·
Command Object
After
establishing connection with the database, we can use the Command object for
processing requests (in the form of command) and returning the results of those
requests from the database. The Command object also enables us to perform other
tasks that necessitate a connection with the database, such as updating the
records of the database.
·
DataReader Object
DataReader
object is helpful to read data in a sequential manner. This object, is similar
to read-only, forward-only recordset and is used for retrieving a read-only,
forward-only data stream from the database. The DataReader object allows only
one row of a data to be stored in the memory at any point of time.
·
DataAdapter Object
DataAdapter
object facilitates communication between the data source and the dataset; it is
used to transfer data from the data source to the dataset and vice versa. It is
used mainly to work with the data in the dataset and then transferring changed
data back to the data source.
Types of .NET Data Provider
The
OLE DB .NET Data Provider
The OLE DB .NET data provider enables data accesses
through the use of COM (Component Object Model) inter - operability.
|
Class
|
Description
|
|
OleDbConnection
|
Represents an open connection with the
data source.
|
|
OleDbCommand
|
Represents a SQL statement or stored
procedure for execution against the data source.
|
|
OleDbDataReader
|
Provides a means to read data rows from
the data source in a forward – only mode. Is similar to the read-only,
forward-only recordset of ADO.
|
|
OleDbDataAdapter
|
Represents the data commands and database
connections used for transfer of data from the data source to the dataset and
vice versa.
|
|
OleDbError
|
Compiles information pertaining to errors
or warnings that the data source returns.
|
|
OleDbException
|
Represents the exception that results
when the OLE DB data source returns an error or warning.
|
|
OleDbPermission
|
Enables the OLE DB .Net data provider to
verify whether a user has enough security permissions to acquire access to the OLE DB data source.
|
|
OleDbTransaction
|
Represents a SQL transaction for the data
source.
|
The
SQL SERVER.NET Data Provider
The SQL SERVER.NET data provider enables
data access through the use of COM (Component Object Model) inter - operability.
|
Class
|
Description
|
|
SqlConnection
|
Represents an open connection with the
data source.
|
|
SqlCommand
|
Represents a SQL statement or stored
procedure for execution against the data source.
|
|
SqlDataReader
|
Provides a means to read data rows from
the data source in a forward – only mode. Is similar to the read-only,
forward-only recordset of ADO.
|
|
SqlDataAdapter
|
Represents the data commands and database
connections used for transfer of data from the data source to the dataset and
vice versa.
|
|
SqlError
|
Compiles information pertaining to errors
or warnings that the data source returns.
|
|
SqlException
|
Represents the exception that results
when the SQL SERVERdata source returns an error or warning.
|
|
SqlPermission
|
Enables the SQL SERVER.Net data provider
to verify whether a user has enough security permissions to acquire access to the SQL SERVERdata source.
|
|
SqlTransaction
|
Represents a SQL transaction for the data
source.
|
ADO .Net Connection Design objects – An
overview.
OleDbConnection
Object
The OleDbConnection object uses OLE DB to
connect to a different types of data sources, such as text files, spreadsheets
and databases.
Properties
·
ConnectionString
Property
Consists of information that is required
for establishing a connection.
Clauses for ConnectionString Property.
|
Clause
|
Description of the value
|
|
Provider
|
Represents the name of the data provider
used to establish a connection with the data source.
|
|
Data Source / Server / Address
|
Represents the name of the server or the
network address of the data source to which you need to connect.
|
|
Initial Catalog / Database
|
Represents the \name of the database or
the data source.
|
|
User ID / UID
|
Represents the username that enables you
t o log on to the database server.
|
|
Password / Pwd
|
Represents the password for logging on to
the server.
|
|
Connect Timeout / Connection Timeout
|
Represents the time (in seconds) after
which the attempt to connect is terminated and an error is generated.
|
|
Persist Security Info
|
Indicates whether the security
information will be returned as a part of the connection. When the value is
set to false, which is the default value , the property does not return any
security information. However, if it is set to true, the property can return
the security information such as the password.
|
Each clause and its value is considered as
a pair. Separate each pair from the next pair with a semicolon (;). Specifying
same clause multiple times in a connection string, the Connection object uses
the value associated with the last occurrence of the clause.
·
Provider property
Represents the name
of the OLE DB data provider used to establish a connection with the data
source. It is a Read – only property which returns the value specified in the
Provider clause in ConnectionString property.
·
DataSource Property
Represents the
location and name of the OLE DB data source. It is a Read – only property which
returns the value specified in the DataSource clause in ConnectionString
property.
·
DataBase Property
Represents the name of the database that we
want to use after establishing and opening the connection. It is a Read – only
property which returns the value specified in the DataBase clause in
ConnectionString property.
·
ConnectionTimeout
Property
Represents the time
(in seconds) after which the attempt to connect is terminated and an error is
generated. By default it is set to 15 seconds. It is a Read – only property
which returns the value specified in the ConnectionTimeout clause in
ConnectionString property.
Methods
·
Open()
To open a connection to a data source, we
need to use the Open() method. This enable us to establish an open connection
with the data source. It uses connection information provided in the
ConnectionString property.
·
Close()
Open Connection utilizes system resources.
Therefore, to close this connection after performing the required operations
Close() method is used.
·
Dispose()
This method is used to release the
resources being used by the OleDbConnection object. Use this method to close
database connection.
This method will automatically calls the
OleDbConection.Close() mthod to close the connection.
Data
Adapters
Exchange of data between a dataset and a
data source includes reading data from a data source to store it in the dataset
and later updating the data source with the changes made in the dataset.
Exchange of data between the dataset and the data source is enabled through the
use of data adapter. Data Adapter is used for communicating between a dataset
and a data source.
The OleDbDataAdapter object is suitable for use with any data source that can be
accessed through the OLE DB .NET data provider. To enable the OleDbDataAdapter
object to work efficiently, use it with the corresponding OleDbConnection and
OleDbCommand objects.
Properties
Ø
SelectCommand
The SelectCommand property of the OleDbDataAdapter class is used to
refer to a SQL statement or a stored procedure
that allows you to select and retrieve records from the database.
This property is an instance of the
OleDbCommand class so the value of the SelectCommand property is an
OleDbCommand object that is used to select records from the database to store
them in the dataset. The OleDbDataAdapter object uses this property when it
calls the Fill() method to fill the dataset with data.
Ø
InsertCommand
The InsertCommand property of the OleDbDataAdapter class is
used to refer to a SQL statement or a stored procedure that enables to insert
data in the database.
This property is an instance of the
OleDbCommand class so the value is an OleDbCommand object that is used to
insert records in the database to match them with the new rows added in the
dataset. The OleDbDataAdapter object uses this property when it calls the
Update() method to update the database with changes made in the dataset.
Ø
UpdateCommand
The UpdateCommand property of the OleDbDataAdapter class is
used to refer to a SQL statement or a stored procedure that enables to update
data in the database.
This property is an instance of the
OleDbCommand class so the value is an OleDbCommand object that is used to
update records in the database to match them with the rows that are modified in
the dataset. The OleDbDataAdapter object uses this property when it calls the
Update() method to update the database with changes made in the dataset.
Ø
DeleteCommand
The DeleteCommand property of the OleDbDataAdapter class is
used to refer to a SQL statement or a stored procedure that enables to delete
data in the database.
This property is an instance of the
OleDbCommand class so the value is an OleDbCommand object that is used to
delete records in the database to match them with the rows that are deleted in
the dataset. The OleDbDataAdapter object uses this property when it calls the
Update() method to update the database with changes made in the dataset.
Ø
TableMappings
The TableMappings property of
OleDbDataAdapter gets a collection providing the mapping between a source table
in the database and DataTable in the dataset. The value of this property is a
collection that provides this mapping.
To update the changes, the OleDbDataAdapter
links the names of the columns in the database with the names of the columns in
the dataset by using the DataTableMappingCollection .
Methods
Ø
Fill()
Data Adapter is used to communicate between
a dataset and a database. This communication includes storing the data
retrieved from the database in a dataset. For this, Fill() method is used.
This method is used to fill the dataset
with data from the database. When we call this method by using the
OleDbDataAdapter object, this method adds or refreshes rows in the dataset.
This method uses the SelectCommand property to select the records with which we
want to fill the dataset. It connects to the database by using the connection
objectrelated to the SelectCommand property. So the OleDbConnection object
needs to be valid, although it is not necessary it to open. If the connection is not already open, the
Fill() method automatically opens the connection, retrieves the data from the
database, and then closes the connection. However, if the connection is already
open prior to calling the Fill() method, it remains open even after the Fill()
method is executed.
e.g.
MyOleDbDataAdapter.Fill (MyDstObj,
“ProdTable”);
Ø
FillSchema()
The FillSchema() method of the
OleDbDataAdapter class is used to add a DataTable object in a dataset and then
to configure its schema to correspond to the schema of the corresponding table
in the database. Schema refers to the definition of the structure of a
database.
This method enables the OleDbDataAdapter
class to create the schema of the dataset prior to filling it with data. When
the FillSchema() method is called, no rows are returned and Fill() method needs
to be used to add rows to the DataTable object in the dataset.
Ø
Dispose()
The
Dispose() method is used to release the resources that the OleDbDataAdapter
class uses.
Ø
Update()
The Update() method is used to update the
database with the changes made in the dataset. When we call this method, the
OleDbDataAdapter object uses the InsertCommand, UpdateCommand, and
DeleteCommand properties for the rows that are inserted, updated and deleted
respectively.
0 comments:
Post a Comment