web enable
Five years of steady growth We understand American and European customers.
Company is built on enduring principles
Process centric assures quality We do what we say and guarantee our work Track record of successful projects
  . Custom software solutions by Indian pros    
Home Services Portfolio Partner Alliances Contact Us About Us Site Map  
Collaborative Tools for the next millennium
Biz Talk Server
Overview of Microsoft ADO.NET
Java and Web Enabling
PHP and mySQL
Zope as a Content Management tool
Streaming Media

Enterprise Groupware Applications

industry collaboration strategies

industry collaboration strategies
Stylus Systems
India Web Developers
Thomas and Alex



Overview of ADO.NET(Cont..) Architecture of ADO.NET

ADO.NET can work either through Web protocols, using XML, or in a more traditional client/server architecture.

Architecture of ADO.NET
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

ADO.NET System Data

The ADO.NET object model is made up

  • Data View
  • Data Set
  • Data Provider


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.


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 are part of a Data Provider and the Connection object provides connectivity to a data source.

Connections can be opened in two ways:

  1. Explicitly by calling the Open method on the connection;
  2. Implicitly when using a DataAdapter.

The following examples demonstrate how to create and open connections to SQL Server™ (SqlClient) and OLE DB (OleDb) databases.


SqlConnection myConn = new SqlConnection

("Data Source=localhost;Integrated Security=SSPI;"+"Initial Catalog=northwind");


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.


OleDbConnection myConn = new OleDbConnection

   ("Provider=SQLOLEDB;Data Source=localhost;" +   "Integrated Security=SSPI;Initial Catalog=northwind");


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.


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.

SqlDataReader dr = cmd.ExecuteReader();
// process the resultset(s) here

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.

nRecsAffected = cmd.ExecuteNonQuery();
// 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.

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.


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.


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.


Other Related Articles from

Business Zone
People: Heart of the Organization
Communicating in the workplace- shifting from linking applications to linking people.

Industry Zone
Collaboration and Enterprise
and other collaborative concepts

Home | Services | Partner Alliances | Contact Us | About Us | Site Map | Fun Zone | Partner with Us | Links
Copyright web-enable.com 2002. All Rights Reserved