Overview of ADO.NET(Cont..) 
ADO.NET can work either through Web protocols, using XML,
or in a more traditional client/server architecture. 
Click for larger diagram
The ADO.NET interface is readily accessible using any of the .NET- compliant
languages, including C#, Visual Basic .Net, and JScript.Net.
ADO.NET exposes an object model that conforms to the standards prescribed
by the World Wide Web Consortium
(W3C). It is designed to support the developer in accessing and writing
to a wide variety of data sources. This can be anything from a product
list in a database used on an e-commerce web site to a collection of sales
figures collected in Excel and saved to an XML file and used as the basis
for a table being displayed on an internal Intranet site. The ADO.NET Object Model 
The ADO.NET object model is made up
- Data View
- Data Set
- Data Provider
DataView
The DataView speaks to the DataSet and is a special class designed for
UI objects to bind to and can provide customized views of the DataSet.
It provides methods and properties that enable UI objects such as a DataGrid
to bind to a DataSet and contains properties such as AllowEdit and Count
that allow the UI object to work with the data in meaningful ways. A DataView
is only used in conjunction with a DataSet and never with a DataReader.
DataSet The DataSet is the core component of the disconnected architecture
of ADO.NET that caches data locally on the client. The DataSet
is explicitly designed for data access independent of any data source.
As a result it can be used with multiple and differing data sources, XML
data, or used to manage data local to the application. The DataSet
contains a collection of one or more DataTable objects made up
of rows and columns of data, as well as primary key, foreign key, constraint
and relation information about the data in the DataTable objects. Another feature of the DataSet is that it tracks changes that are made
to the data it holds before updating the source data. DataSet are also
fully XML-featured. They contain methods such as GetXml and WriteXml that
respectively produce and consume XML data easily. In an XML scenario where
there is no database, these methods enable use of ADO.NET without the
Data Provider being involved. The DataSet object provides a consistent programming model that works
with all current models of data storage: flat, relational, and hierarchical.
It represents the data that it holds as collections and data types. The
data within a DataSet is manipulated via the set of standard APIs exposed
through the DataSet and its child objects regardless of its data source.
The DataSet is similar to a Recordset with CursorLocation
= adUseClient, CursorType = adOpenStatic, and LockType
= adLockOptimistic. However, the DataSet has extended capabilities
over the Recordset for managing application data. Data Provider The DataProvider at the bottom of the diagram speaks to the database.
The DataProvider connects to the database on behalf of ADO.NET.
It encapsulates all connections to a database. The blue section at the
bottom of the diagram describes a DataProvider with several key
objects in it:
- Connection
- Command
- DataAdaptor
- DataReader
.NET Beta2 provides two distinct Data Providers and only one is
used at a time, based on the type of database being connected to. The first Data Provider, SQL Server .NET Data Provider uses a special
protocol called TDS (Tabular Data Stream) to communicate directly with
SQL Server without adding the overhead of OLE DB or ODBC. This Data Provider
is represented in the .NET namespace as System.Data.SqlClient. The other Data Provider is the OLE DB .NET Provider. Using .NET’s COM
interoperability features provides native OLE DB to enable data access.
According to the .NET documentation, one important difference between
the two is that the OLE DB .NET Provider supports both manual and automatic
transactions. For automatic transactions, the OLE DB .NET Data Provider
automatically enlists in a transaction and obtains transaction details
from Windows 2000 Component Services. Component Services will be covered
in a future article. This Data Provider is represented in the .NET namespace
as System.Data.OleDb. Connections Connections are part of a Data Provider and the Connection object
provides connectivity to a data source. Connections can be opened in two ways:
- Explicitly by calling the Open method on the connection;
- Implicitly when using a DataAdapter.
The following examples demonstrate how to create and open connections
to SQL Server™ (SqlClient) and OLE DB (OleDb) databases. SqlClient
SqlConnection myConn = new SqlConnection
("Data Source=localhost;Integrated Security=SSPI;"+"Initial
Catalog=northwind");
myConn.Open();
Connection String Format – SqlConnection The SQL Server™ .NET Data Provider supports a connection string format
that is similar to the OLE DB (ADO) connection string format. For valid
string format names and values, see the SQLConnection.ConnectionString
Property. OleDb
OleDbConnection myConn = new OleDbConnection
("Provider=SQLOLEDB;Data Source=localhost;" + "Integrated
Security=SSPI;Initial Catalog=northwind");
myConn.Open();
Closing the Connection You must always close the Connection when you are finished using
it. This can be done using either the Close or Dispose methods
of the Connection object. Connections are not implicitly released
when the Connection object falls out of scope or is reclaimed by
garbage collection. Commands The Command object enables access to database commands to return
data, modify data, run stored procedures, and send or retrieve parameter
information. Commands contain information that is submitted to a database
as a query, and, like connections, are represented by the provider-specific
classes SqlCommand and OleDbCommand. Functionally, once the Connections
are established and the Commands are executed the results are in the form
of streams. These resultant streams can be accessed either by DataReader
object, or passed into a DataSet object via a DataAdapter. The SqlCommand class provides four different methods to execute a command.
They are: ExecuteReader, ExecuteNonQuery, ExecuteScalar
and, newest but not least, ExecuteXmlReader. Essentially, such
methods differentiate only for the type of input they expect, and consequently
for the result they return. In general, once you know the operation to
accomplish, determining the right method to use is rather straightforward. Incidentally, an OleDbCommand object does not support ExecuteXmlReader. ExecuteReader expects to run a query command or a stored procedure
that selects records. It expects to have one or more resultsets to return.
cmd.Connection.Open();
SqlDataReader dr = cmd.ExecuteReader();
// process the resultset(s) here
cmd.Connection.Close();
You access the selected records using the SqlDataReader object
and use the method Read to loop through them. You move to the next
resultset using the NextResults method. ExecuteNonQuery expects to run a command, or a stored procedure,
that affects the state of the specified table. This means anything but
a query command. You normally use this method to issue an INSERT, UPDATE,
DELETE, CREATE, and SET statement. ExecuteNonQuery returns only the number of rows affected by the command
execution, or –1 should this information be unavailable. It doesn't give
you a chance to access any resultset generated by the statement or the
stored procedure. Actually, there's really nothing to prevent you from
using this method for a query command, but in this case you get neither
the resultset nor the number of the affected rows.
cmd.Connection.Open();
nRecsAffected = cmd.ExecuteNonQuery();
cmd.Connection.Close();
// check the record(s) affected here
The number of affected rows is also made available through the RecordsAffected
property of the SqlCommand object. This property equals –1 in case
of errors or if a query command is executed. ExecuteScalar expects to run a query command, or more likely a
stored procedure, that returns data. However, this method is different
from ExecuteReader in that it just makes available, as a scalar
value, the first column on the first row of the selected resultset.
cmd.Connection.Open();
Object o = cmd.ExecuteScalar(); cmd.Connection.Close();
// work on the scalar here
The method returns the value as a boxed object. It's then up to you to
unbox or cast that value to the proper, expected type. ExecuteScalar turns out to be particularly useful when you have statistical
or aggregate operations to accomplish on a certain amount of data. In
these and similar circumstances, there is just one value that you might
want to return back to the caller. Because of its use cases, you normally
use this method on more or less complex stored procedures rather than
on single SQL statements. ExecuteXmlReader builds up and returns an XmlReader object
after a SELECT command that exploits XML features in SQL Server
2000 has been issued. DataAdapters The DataAdapter provides a set of methods and properties to retrieve
and save data between a DataSet and its source data store. It does the
actual work of putting returned data from a database into a DataSet. It
also manages reconciling how data should be updated against a database.
Connections and Commands whose properties are set early on in code are
often passed into DataAdapters for use when their action methods are invoked.
The DataAdapter object encapsulates a set of data commands and a database
connection, which are used to fill the DataSet and update the data source.
The Fill method of the DataAdapter calls the SELECT command while Update
method calls INSERT, UPDATE or DELETE command for each changed row. Simple
and straightforward, huh? One of the great features about the DataAdapter object is that these
commands can be set explicitly in order to control the statements used
at runtime to resolve changes, including the use of stored procedures.
The .NET documentation notes that the CommandBuilder object can
be used to generate these commands at run-time based upon a select statement
for "ad-hoc queries". However, this requires an extra round-trip
to the server in order to gather required metadata, so explicitly providing
the INSERT, UPDATE, and DELETE commands at design time will always result
in better run-time performance. The DataAdapter is the object that connects to the database to fill the
DataSet. It also connects to the database in order to update the data,
and this is based on the operations that take place while the DataSet
holds the data. However, there are cases where the DataAdapter and DataSet objects are
bypassed. In these cases, the DataReader object is used. DataReaders When dealing with large read only data, e.g. reading 5,000 rows of data,
the .NET framework includes the DataReader object, which is a read-only,
forward-only stream returned from the database. Only one record at a time
is ever present in memory. The DataReader is similar to a Recordset with CursorType
= adOpenForwardOnly and LockType
= adLockReadOnly. 
| Inquire Here |
| Looking for a partner to build your
application using ADO.NET? Contact us to
get started. |
|
Services
