What Is ADO.NET ?

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.

What Is ADO.NET?

ADO.NET stands for ActiveX Data Object is a database access technology created by Microsoft as part of its .NET framework that 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 database 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.

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.

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.

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

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.

The ADO.NET classes are easy to use and to understand because of their object- oriented nature.

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.

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.

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.

DataReader versus DataSet

The ADO.NET DataReader is used to retrieve read-only (cannot update data back to a datasource) and forward-only (cannot read backward/random) data from a database. You create a DataReader by calling Command.ExecuteReader after creating an instance of the Command object.

Learn more about DataReader vs DataSet.

LINQ to DataSet

LINQ to DataSet API provides queries capabilities on a cached DataSet object using LINQ queries. The LINQ queries are written in C#. Learn more here: LINQ to DataSet

LINQ to SQL

LINQ to SQL API provides queries against relational databases without using a middle layer database library. Learn more here: LINQ to SQL in C#

ADO.NET Entity Framework

The ADO.NET Entity Framework is designed to enable developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema. The goal is to decrease the amount of code and maintenance required for data-oriented applications. For more information, see ADO.NET Entity Framework.

ADO.NET Entity Framework and ADO.NET

ADO.NET Components

The ADO.NET is designed to work with multiple kinds of data sources in same fashion. You can categorize ADO.NET components in three categories: disconnected, common or shared and the .NET data providers. The disconnected components build the basic ADO.NET architecture. You can use these components (or classes) with or without data providers. For example, you can use a DataTable object with or without providers and shared or common components are the base classes for data providers. Shared or common components are the base classes for data providers and shared by all data providers. The data provider components are specifically designed to work with different kinds of data sources. For example, ODBC data providers work with ODBC data sources and OleDb data providers work with OLE-DB data sources.

The following figure represents the ADO.NET components model and how they work together:

A data provider is a set of components, such as Connection, Command, DataAdapter and DataReader. The Connection is the first component that talks to a data source. The Connection object establishes a connection to a data source and works as a connection reference in Command and DataAdapter objects. A Command object executes a SQL query and stored procedures to read, add, update, and delete data of a data source via a DataAdapter. A DataAdapter is a bridge between a dataset and the connection. It uses Command Object to execute SQL queries and stored procedures.

All data providers share the ADO.NET common components. These components represent the data. Some of the common components are DataSet, DataView, and DataViewManager. The DataSet uses XML to store and transfer data between the applications and the data provider. A DataSet is a set of DataTable objects. A DataTable represents a database table. The DataView and DataViewManager objects provide single or multiple views of a dataset. You can attach a DataView or a DataViewManager directly to data–bound controls such as a DataGrid or DataList. Other common components are DataTable, DataRow, DataColumn and so on. Now, I’ll break down the ADO.NET model to show how it works.

Connection Object

The Connection object is the first component of ADO.NET that you should be looking at. A connection sets a link between a data source and ADO.NET. A Connection object sits between a data source and a DataAdapter (via Command). You need to define a data provider and a data source when you create a connection. With these two, you can also specify the user ID and password depending on the type of data source.

Connection can also be connected to a Command object to execute SQL queries, which can be used to retrieve, add, update and delete data to a data source.

The Connection object also plays a useful role in creating a transaction. Transactions are stored in transactions objects, and transaction classes have all those nice features for dealing with transactions such as commit and rollback.

Learn more about SqlConnection here: SqlConnection In ADO.NET

Command object

The Command object can execute SQL queries and stored procedures. You can execute SQL queries to return data in a DataSet or a DataReader object. To retrieve add, update and delete data you use SELECT, INSERT, UPDATE, and DELETE SQL queries. A DataAdapter generated using the VS .NET Integrated development Environment (IDE) has these queries.

Learn more about SqlCommand here: SqlCommand In ADO.NET

The Command Builder

The SQL SELECT command is a fairly easy one to construct. Even if you don’t know how to construct a SQL SELECT command, the Query builder in visual studio helps you. But notice there are three other commands in figure 3-6 to construct: InsertCommand, UpdateCommand, and DeleteCommand. These commands can get quite complicated in .NET because they require complex parameter objects and often involve large lists of columns ADO.NET provides a nice utility know as the CommandBuilder that automatically builds these commands for you.

The DataAdapter Object

The DataAdapter object serves as a conduit between the data source and the Dataset. The DataAdapter knows about the DataSet and it knows how to populate it. The Adapter also knows about the connection to the data source.

Learn more about DataAdapter here: DataAdapter in C#

DataSet Object

A DataSet object falls in disconnected components series. You can use it with or without data providers. The DataSet consists of a collection of tables, rows, columns and relationships. Figure 3-10 illustrates these relationships specifically that the DataSet contains a collection of DataTables and the DataTable contains a collection of DataRows, DataRelations, and DataColumns. A DataTable maps to a table in the database. The previous DataSet contains a DataTable that maps to the Orders table because you filled it with a SELECT query executed on the Order table.

Well, now you see how you can look at your tables, but you still really haven’t seen any hard data yet. The data in a DataSet is contained in the DataRow. A DataTable in the DataSet consists of a collection of DataRow. Each DataRow can be accessed via an index or the column name. As you can see from figure 3-10, a data set has a one-to-many relationship with DataTable. That means a DataSet can have one or more than one DataTable objects. Similarly, a DataTable can have one or more than one DataRelation, DataRow, and DataColumn objects.

Learn more about DataSets here: DataSet in C#

DataSets in DataViews

Another thing you can do with the contents of your DataSet is sort and filter them using DataViews. You can have multiple Views of a dataset. A DataView is a view of your data created according to certain criteria. Each DataView has a one-to-one mapping to a DataTable in a DataSet. For example, say you have three tables in a dataset: table 1, table2, and table3. Using three different data tables and data views, you can represent this dataset in three different views. Using sort and filters, you can even sort and filter the data based on some criteria.

A dataview can directly attach to data-bound controls such as a DataGrid, DataList, or a Combo box.

Learn more about DataView here: DataView in C#