Exam 70-487: Accessing Data - How to choose the Appropriate Data Access Technology - ADO.NET?

It is virtually impossible nowadays to find a software application that does not make use of some form of data Access.

There are many ways to build data-centric applications and many technologies available for use. Microsoft provides quite a few:

  1. ADO.NET, 
  2. Entity Framework, 
  3. WCF Data Services, 
  4. Azure Cosmos DB

The "Accessing Data" objective  covers 20-25% of the overall Exam 70-487, and it is important to understand the characteristics and distinctive of each of the above data access technologies. When to use one vs the other.


In the beginning was ADO, Microsoft ActiveX Data Objects (ADO)! It enables your client applications to access and manipulate data from a variety of sources through an OLE DB provider. Its primary benefits are ease of use, high speed, low memory overhead, and a small disk footprint. ADO supports key features for building client/server and Web-based applications. One of its major weakness is its strictly connected model.  

ADO evolved from an earlier Microsoft data interface, Remote Data Objects (RDO). RDO works with Microsoft's ODBC to access relational databases, but not nonrelational databases such as IBM's ISAM and VSAM.

Okay, don't expect questions on the exam about ADO, but for your own education, you can read more about ADO (ActiveX Data Objects) here.

ADO.NET is the successor of ADO (quite frankly, ADO.NET was designed from such a ground up fashion, that it should be considered original, other than the fact that it covers most features found in ADO). ADO.NET is the oldest of the technologies in the current stack,  but still warrants serious consideration because there is still a huge amount of ADO.NET code in production and it is being used to build new applications today (Note: It is strongly recommend that you use ADO.NET, not ADO, for accessing data in your new .NET applications.)

Again, the exam questions are related to ADO.NET.

 ADO.NET was build from the ground up to support large loads and to excel at security, scale-ability, flexibility and dependability; performance-oriented areas that are taken care of by the fact ADO.NET is heavily biased towards a disconnected model.

ADO.NET is a set of classes that expose data access services for .NET Framework programmers. It opens a connection to the data repository, execute CRUD commands and then quickly closes the connection: This is crucially important because connections are "expensive (processing and network resources)" and can hold locks on data causing concurrency issues.

Opening and closing connections as such a high rate can incur minor overhead. ADO.NET takes a step further in improving performance by adding the concept of connection pooling: the idea of allowing multiple simultaneous clients to use a common connection to the data store, and even limit the number of active connections (set to 100 for and ADO.NET 4.5 application accessing SQL server 2012).

ADO.NET is highly compatible: At its heart is the System.Data namespace, that contains many base classes that are used, irrespective of the database system. There are several vendor-specific libraries (System.Data.SqlClient, System.Data.OracleClient), and some generic ones for OleDb and Odbc-compliant systems (System.Data.OleDb, System.Data.Odbc).

Three major activities remain constant is most database interactions: 1. Connect  2. Execute CRUD 3. Hold the result.

ADO.NET handles the above 3 activities using two main components known as the .NET Framework data providers and the DataSet.

A .NET Framework data provider is used for connecting to a database, executing commands, and retrieving results. Those results are either processed directly, placed in a DataSet in order to be exposed to the user as needed, combined with data from multiple sources, or marshaled across tiers.

As Microsoft Docs states, The Connection object provides connectivity to a data source. 
The Command object enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information. 

The DataReader provides a high-performance stream of data from the data source. 

Finally, the DataAdapter provides the bridge between the DataSet object and the data source. 

It is worth noting that, underneath the abstractions, a DataAdapter uses a DataReader to populate the returned DataSet or DataTable. Hence, the Data Reader is faster that the dataAdapter in retrieving the same results.
The DataAdapter uses Command objects to execute SQL commands at the data source to both load the DataSet with data and reconcile changes that were made to the data in the DataSet back to the data source. 

The DataSet is explicitly designed for data access independent of any data source. It can therefore be used with multiple and differing data sources, used with XML data, or used to manage data local to the application.

Important Sayings:

  • Understand Query Parametization and do it to avoid being hacked by an injection attack. Even then, still validate your inputs at every level.
  • Minimize unnecessary connections to the database, so if you open it, close it. This can be done through try/catch/finally or try/finally structures. You can also use the Using Statement which ensures the Dispose method is called on all IDisposable objects (even then, get used to closing all database connections explicitly in the Finally block).
  • Use some of the Asynchronous methods of the SqlDataReader to provide rich feedback to your application.

So why choose ADO.NET as a data access technology?

The exam expects you to know what makes one technology more appropriate than the other in a given setting. You need to understand of course how each technology works.

Reason #1 to choose ADO.NET:  Consistency (Unless it is a new application or and old one that has been updated to use some newer technologies, chances are it is using ADO.NET already)

Reason #2 to choose ADO.NET: Stability both in terms of future update and the current state of the technology. You will know how to use it even with the multiple changes through the .NET framework. Most bugs and hinks have been fixed, since ADO.NET has been around for a long time.

Reason #3 to choose ADO.NET: Easy to learn and there are providers for almost every well-known database with more being created with any new data store systems.

Reason # 4 to choose ADO.NET: You can connect to SQL Azure databases even though ADO.NET precedes the cloud.

EXAM 70-487 tip for ADO.NET Objective:

ADO.NET is the simplest of the data access technologies available. The Exam primarily serves to verify that you are familiar with the technology and can implement solutions using it. Although the objective talks about "choosing a technology", you should focus on how you will accomplish any given task and what benefit each item brings to the table. Become comfortable with the relevant namespace, but better yet, make sure you understand and practice how to populate a multi-datatables DataSet with constraints and foreign keys using DataAdapter. 

Useful Exam preparation references:


Popular posts from this blog

Why can't Microsoft install IIS on Window Operating Systems by default?

Exam 70-487: Accessing Data - How to choose the Appropriate Data Access Technology - Entity Framework?

Exam 70-487: Accessing Data - How to choose the Appropriate Data Access Technology - Azure Cosmos DB?