ActiveX Data Object for the .NET Framework (ADO.NET) is the latest database access technology from Microsoft. ADO.NET addresses issues with previous database access technologies and provides future scalability. Although ADO.NET Stands for Active Data Objects .NET, it's perhaps misnamed because ADO.NET is not an ActiveX / Component Object Model (COM) technology. As the business world is moving onto the Internet, one of the main goals of ADO.NET is to write high- performance, reliable and scalable database applications over the Internet. ADO.NET uses .NET Common Language Runtime (CLR) to cache the data and exchange data among applications over the Internet as well as intranets.
This article will provide an overview of ADO.NET In it, I'll provide the basics of ADO.NET describe its advantages over current data access technologies and briefly introduce ADO.NET classes and namespaces as well as show how to use them to write simple database applications using Visual Studio .NET. Microsoft Visual studio .NET provides tremendous Support for writing database applications in no time, using its wizards and utilities. In this article, you'll also take a brief look at ADO.NET components and how they fit in model and work together.
Note: The term .NET base class library is synonymous with .NET runtime library and .NET framework class library.
What Is ADO.NET?
ADO.NET is a new database access technology created by Microsoft; its .NET framework can access any kind of data source. It's a set of object-oriented classes that provides a rich set of data components to create high-performance, reliable and scalable data base applications for client- server applications as well as distributed environments over the Internet and intranets.
In the ADO.NET model, unlike ADO (in connected state) and previous data access technologies applications connect to the data sources when they are reading or updating the data. After that the connection closes. This is important because in client- server or distributed applications, having connection resources open all the time is one of the most resource- consuming parts. You don't have to connect to a data source all the time; the only time you need to connect to a data source is when you are reading and writing final changes to a data source.
The old approach of having connections open all the time is problematic when considering the number of clients that access the same data source simultaneously. In larger systems, developers often used MTS/COM+ to pool ADO connections to address some of this resource consumption; however, ADO.NET provides a more optimal paradigm for larger systems without needing to utilize MTS/COM+ enterprise services-unless of course you need to take advantage of such advanced techniques as a three- phase commit.
ADO .NET uses SQL queries and stored procedures to read write update and delete data from a data source. You use SQL queries through ADO.NET Command object, which returns data in the form of DataReader or DataSet objects. After that connection closes, you use DataSet objects to work with the data and connect to the data source again when you need to update the data source.
NOTE: SQL queries are also called SQL statements see the Appendix for SQL query examples.
A dataset is a collection of DataTable objects and relationships among them. It works as a container that stores returned data from a database in cached from. You can fill a dataset with the data retrieved from multiple tables of a database. Once you have a dataset (which is disconnected data, stored on your local machine), you treat the dataset changes final to the actual database. You call the Update method to make dataset changes final to the actual database. You can even read and update a dataset from different data sources.
You access a data source and fill a dataset via data providers. The .NET framework provides three different types of data providers: Sql, OleDb and ODBC. Microsoft is also working on providing a data provider for Oracle database and other Database Management System (DBMS) suppliers may produce their own data providers. You use a DataAdapter object of a data provider and call its Fill method to fill a dataset.
XML plays a major role in ADO.NET. The ADO.NET model utilizes XML to store the data in cache and transfer the data among applications. Datasets use XML schemas to store and transfer data among applications. You can even use this XML file from other applications without interacting with the actual dataset. You can use data among all kinds of applications and components because XML is an industry standard; you can transfer data via many protocols, such as HTTP, because of XML's text- based nature.
Why ADO .NET was Designed
To understand why ADO.NET was designed, you have to take a look at the present database access technologies. As you saw in the “Background: Microsoft Data Access Technologies” section of the Introduction, each previous data access technologies has its own pros and cons. Some of them were designed specifically for a particular type of data source, some of them are not easy to program for developers and some of them don't have good enough performance for example, ODBC API is a set of C functions. There is no object-oriented support. So developing applications using ODBC is time- consuming process for developers. Another Problem with ODBC is its relational nature. ODBC API is limiting to relational database, and its difficult to use ODBC with nonrelational data sources such as network directories, email stores, ISAM data object data, and so on. DAO is an object model but was specifically designed to work with the Microsoft Jet database engine. The MFC ODBC and DAO classes are one more wrapper for ODBC and DAO API. Because of this extra wrapper, these classes are not up to the mark performance-wise and still have some of the same limitations as ODBC and DAO.
However, OLE-DB is a solution that works with any kind of data sources using OLE-DB providers and consumers, but writing OLE-DB applications is time consuming. It's also hard for developers to program. Moreover, OLE-DB is a flat model. That means you could directly connect to a table without going through hierarchical objects of connection and database. Because of the lower level programming model of OLE API, OLE-DB API isn't easy to program for non – OLE programmers.
To overcome to this problem, Microsoft introduced ADO. ADO is a high-level programming model and it was best data access technology at that point (of course, this was before ADO.NET). It's a nice COM wrapper for OLE-DB API, and it suits most developers. The first major drawback of ADO is COM model, which isn't easy to learn and program for non-COM developers. The second drawback is the limited support for XML. You can read XML documents through ADO, but documents must have a certain format. The document must contain a root node named Xml and two children. The first child of the root node describes the schema, which describes the type of each row and column, and the second node represents the serialized OLE DB rowset.
In brief, ADO.NET meets today's programming needs, including disconnected data, tight integration with XML, and a common model to work with all kinds of data sources.
To keep all these problems in mind, Microsoft wrote a set of new object- oriented classes, called ADO.NET. ADO.NET inherits its programming model from its predecessor ADO, so ADO programmers will see something common between these two APIs. It'll also take less time to jump from ADO to ADO.NET. ADO.NET classes are based on the ADO model and have some objects in common, such as Connection and Command. However, datasets replace record sets in ADO.NET. These classes are easy to use and have solutions for all the problems discussed here.
Welcome to the ADO .NET world
Advantages of ADO .NET
ADO.NET offers several advantages over previous Microsoft data access technologies, including ADO. The following section will outline these advantages.
Single Object- Oriented API
As discussed in the “Background: Microsoft Data Access Technologies” section of the Introduction and previous section of this article, there were many ways to access various data sources. Each of these technologies has its pros and cons.
Sometimes developers are confused about which technology to use. For example to work with Microsoft Access database, you can use ODBC, DAO, OLE -DE, ADO, DAO and ODBC MFC classes. Which one would you choose? Again it all depends on your requirements. The deployment of these technologies on client machines was one of the biggest problems because of the DLL hell. Believe me, because of these technology limitations, I used ODBC, DAO and ODBC in one of my applications to get the desired result.
The ADO.NET provides a single object-oriented set of classes. There are different data providers to work with different data sources, but the programming model for all these data providers to work in the same way. So if you know how to work with one data provider, you can easily work with others. It's just a matter of changing class names and connection strings.
NOTE: You can use more than one data provider to access a single data source. For example, you can use ODBC or OleDb data providers to access Microsoft access databases.
The ADO.NET classes are easy to use and to understand because of their object- oriented nature.
Managed Code
The ADO .NET classes are managed classes. They take all the advantages of .NET CLR, such as language independency and automatic resource management. All .NET languages access the same API. So if you know how to use these classes in C#, you'll have no problem using them in VB.NET. Another big advantage is you don't have to worry about memory allocation and freeing it. The CLR will take care of it for you.
Deployment
In real life, writing database application using ODBC, DAO, and other previous technologies and deploying on client machines was a big problem was somewhat taken care in ADO except that three are different versions of MDAC. Now you don't have to worry about that. Installing distributable .NET components will take care of it.
XML Support
Today, XML is an industry standard and the most widely used method of sharing data among applications over the Internet. As discussed earlier in ADO .NET data is cached and transferred in XML format. All components and applications can share this data and you can transfer data via different protocols such as HTTP.
Visual Data Components
Visual Studio .NET offers ADO .NET components and data– bound controls to work in visual form. That means you can use these components as you use any windows controls. You drag and drop these components on windows and web forms set their properties and write events. It helps programmers to write less code and develop applications in no time. VS .NET also offers the data form wizard, which you can use to write full-fledged database applications without writing a single line of code. Using these components you can directly bind these components with data-bound controls by setting these control's properties at design-time.
Performance and Scalability
Performance and scalability are two major factors when developing web-based applications and services. Transferring data one source to another is a costly affair over the Internet because of connection bandwidth limitations and rapidly increasing traffic. Using disconnected cached data in XML takes care of both of these problems.
Comparing ADO .NET and ADO
Among present Microsoft data access technologies, ADO is the most popular and powerful technology. To understand the power of ADO .NET, it's not a bad idea to compare ADO.NET with ADO.
The ADO .NET model is inherited from its predecessor ADO, but there are some key differences between ADO and ADO .NET. The following sections will describe these differences.
Connections and Disconnected data
With ADO .NET you use as few connections as possible and have more disconnected data.
Both the ADO and ADO .NET models support disconnected data but ADO'S record set object wasn't actually designed to work with disconnected data. So there are performance problems with that. However, ADO.NET's dataset is specifically designed to work with disconnected data and you can treat a dataset as a local copy of a database. In ADO.NET, you store data in a dataset and close the make final changes to the data source. The ADO model is not flexible enough for XML users; ADO uses OLE-DB persistence provider to support XML.
Recordset versus DataSet
In ADO, the in-memory representation of data is the recordset. A recordset is a set of records that can be an entire database table, a section of a table, or a join of more than one table. SQL statements are used to return the set of data. You use a record set's MoveNext, MovePrevious, MoveFirst and MoveLast methods to navigate through the data in a record set.
In ADO.NET the record set is gone. The new objects introduced in ADO .NET that serve the purpose of the record set are dataset and data reader. A Dataset object represents a dataset, and a DataReader object represents a data reader in ADO.NET. A dataset is a collection of one or more than one tables. A dataset can also contain metadata of database tables, such as their relationships, constraints, and primary and foreign keys. A DataTable object represents a table of a dataset. With the help of the DataView object, you can have multiple views of the same dataset.
A dataset doesn't have any move methods to navigate through dataset records like a record set. In ADO .NET, a DataTable is a collection of DataRows. You use a DataRow object to navigate through the records. ADO.NET also provides data relation objects to navigate through master and detail records using primary and foreign keys. The DataReader object provides fast-cached, forward-only data to read data and navigate through its records.
XML Support
The ADO .NET model uses XML to store and transfer data among applications, which is not only an industry standard but also provides fast access of data for desktop and distributed applications. ADO uses COM marshaling to transfer data among applications, which is not only hard to program but also not as efficient or reliable as XML because of its limitations data types and their conversion. And the nature of COM. Using XML, not only is it easy to program XML files you can also transfer data through firewalls using HTTP, which is not possible using COM marshaling.