Although the ADO(dot)NET library has a not too complicated architecture, it contains many classes. Each class has many ways of instantiating using each other’s objects. This makes many newcomers to ADO.NET feel confused and confused. Actually, if you understand the architecture of the ADO.NET library, everything will be much simpler and easier to understand.

This lesson will show you in detail the ADO.NET architecture, the main components of the ADO.NET library, the data sources that ADO.NET can access, the concepts and data providers for ADO.NET Architecture.

By the end of the lesson you’ll have a systematic look at ADO.NET and its key classes, as well as how the classes relate to each other. This will be of great help as you dive into each specific component of ADO.NET.

Role and Position of ADO.NET Architecture

A program’s data is the most important component and can be stored (and retrieved) in a variety of ways. There are two main directions: (1) self-managing data; (2) use a specialized program or architecture to manage data, commonly referred to as a database. Word or Notepad use the first trend, while specialized management applications (Line-of-Business, LOB) often choose the second direction.

For the direction of using the database, there are also two options: (1) using a database in client-server architecture, (2) using a file-based database. Excel is an application that uses option 2. While option 1 is common in LOB applications. They are also collectively referred to as data sources.

Databases store information in their own form. The most common is tabular, but can also be XML or Json. Meanwhile, the program stores information in the form of a hierarchical chain of objects.

As such, there is a big difference between the way databases process information and programming languages. This is where ADO.NET plays its role: the middleman between the database and the program.

ADO.NET is part of the .NET framework. It serves as a toolkit and an intermediary layer that helps the program interact with the data source.

Architecture of ADO.NET In General

The ADO.NET architecture can be divided into two components: the connected component and the local component (disconnected). This division is related to the roles and responsibilities of objects in ADO.NET. These two components interact with each other through a special component: the Data Adapter.

Connected Components

The connected component includes objects responsible for directly interacting with the data source: connection, command, parameter.

Notice, here we are only talking about objects and not specific classes because ADO.NET can work with many types of data sources. For each type of data source will have to use a separate class group. However, these classes must all implement a common interface.

Connection is responsible for connecting to the data source. The main jobs the connection takes on is to open / close the connection, check the connection status. All the rest of the Connected group members operate on connection.

Command is the component responsible for executing queries such as read, write, update, delete data (collectively called CRUD – Create – Retrieve – Update – Delete command group). Command can also be responsible for working with the structure of the data source, for example changing the table structure. Commands operate on a specific connection.

Parameter is responsible for dynamically and securely passing parameters to queries. Parameter works on Commands and helps to pass parameters to Commands.

This component depends on the type of data source. For example, to work with SQL server must create a dedicated object for this data source type (from the SqlConnection class); For Oracle, the object will be created from the OracleConnection class.

Disconnected Component

This component is responsible for creating a copy of (part of) the database in program memory. This copy allows the program to work with the data without maintaining a constant connection to the data source.

To create a “copy” of the database, the Disconnected component contains classes that simulate the structure of the database, including:

• DataSet (simulate the whole database),
• DataTable (simulate data table),
• DataRow (data stream),
• DataColumn (column of data),
• DataView (similar to Sql Server view),
• DataRelation (relationship between tables).

The disconnected component does not depend on a particular data source. Data from any data source can be dumped into DataSet, DataTable.

The Disconnected component cannot work directly with the data source on its own. Instead, it must interact with the connected component through the Data Adapter to perform CRUD operations.

Data Adapter

The Data Adapter is a special intermediary that helps the Disconnected component work with the Connected component. The Data Adapter supports automatically performing CRUD operations with the data source as needed.

The Data Adapter can be visualized as a bridge between the local data (of the Disconnected component) and the Connected component. For example, when the Disconnected component needs data, the Data Adapter automatically calls the Connected unit to connect and query the data. When Disconnected needs to save data back, the Data Adapter calls Connected to execute the corresponding query.

As on the ADO.NET architecture diagram we can notice, although looking at the overall program that interacts with ADO.NET. However, we have a few specific options:

• (1) only works with Connected components;
• (2) only works with Disconnected component;
• (3) combination.

Data Provider

ADO.NET divides the Connected component by type of data source. For example, to work with SQL Server will need a separate class group (located in the System.Data.SqlClient namespace). Similarly, to work Oracle also needs such a group of classes (in the System.Data.OracleClient namespace). A group of classes for the Connected component to work with such a specific type of data source is called a Data Provider.

To work with each type of data source will have to build and use a corresponding data provider. The data provider model makes ADO.NET more flexible and flexible when it comes to accessing new types of data sources. Any database developer can create their own data provider for ADO.NET to support their database.

In the framework of this course we only work with providers for SQL Server. Once you know how to work with a provider, you can completely learn to work with other types of providers yourself.

Providers like SqlClient or OracleClient are called specialized providers. Whereas ODBC or OleDb are known as generic providers. Dedicated Providers have high performance and exploit the unique features of the data source. Generic Providers are limited in performance but allow working with many different types of data sources.

Understanding Data Source in ADO.NET

ADO.NET provides a wide range of data sources that programmers can use to connect to different databases. In this section, we will delve into data sources in ADO.NET, the different types of data sources available, and how to connect to them.

A. Definition of Data Source

A data source refers to the database that provides the data that an application uses. A data source can be any database system that stores data such as Oracle, SQL Server, MySQL, XML file, or even a CSV file. ADO.NET provides various classes to interact with these data sources, enabling programmers to access and manipulate data from the database.

Types of Data Sources in ADO.NET

ADO.NET provides various types of data sources that programmers can use when working with different databases. Here are the most common data sources in ADO.NET:

1. SQL Server Database

SQL Server is a relational database management system (RDBMS) that is widely used in the industry. ADO.NET provides a SqlClient class that programmers can use to connect to SQL Server databases.

2. Oracle Database

Oracle is another RDBMS that is commonly used in the industry. ADO.NET provides an OracleClient class that programmers can use to connect to Oracle databases.

3. OLE DB

OLE DB stands for Object Linking and Embedding, Database. It is a Microsoft technology that provides a common interface for accessing various types of data sources, including databases, spreadsheets, and other types of data sources. ADO.NET provides an OleDb class that programmers can use to connect to OLE DB data sources.

4. XML File

An XML file is a text file that contains data in a structured format. ADO.NET provides an XmlReader class that programmers can use to read data from an XML file.

How to connect to a Data Source

To connect to a data source, programmers need to create a connection string that specifies the data source and the credentials required to access it. A connection string typically contains the server name, the database name, and the credentials needed to authenticate the user.

1. Connection String

A connection string is a string that contains the information required to connect to a data source. It typically consists of a series of key-value pairs separated by semicolons. Here is an example of a connection string to connect to a SQL Server database:

Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

2. Connection Object

Once a connection string has been created, programmers can create a connection object that uses the connection string to connect to the data source. The connection object provides various methods to interact with the data source, such as opening and closing connections, executing commands, and retrieving data.

Understanding Data Provider in ADO.NET

Data providers in ADO.NET play a crucial role in providing access to different types of databases. They enable programmers to interact with databases by providing classes and methods that allow them to connect to a database, execute queries, and retrieve data. In this section, we will delve into data providers in ADO.NET, the different types of data providers available, and how they compare to each other.

A data provider is a set of classes that enable programmers to interact with a specific type of database. Data providers provide a common interface for interacting with databases, regardless of the underlying database system. Data providers are specific to a particular type of database system, such as SQL Server, Oracle, or MySQL.

Types of Data Providers in ADO.NET

ADO.NET provides different types of data providers that programmers can use when working with different types of databases. Here are the most common data providers in ADO.NET:

1. SQL Data Provider

The SQL data provider is the most commonly used data provider in ADO.NET. It provides classes that allow programmers to connect to and interact with SQL Server databases.

2. OLE DB Data Provider

The OLE DB data provider enables programmers to connect to OLE DB data sources such as Microsoft Access, Excel spreadsheets, and other data sources that support OLE DB.

3. Oracle Data Provider

The Oracle data provider enables programmers to connect to Oracle databases and interact with them. It provides classes that allow programmers to execute queries, retrieve data, and update the database.

4. ODBC Data Provider

The ODBC data provider is a general-purpose data provider that allows programmers to connect to different types of databases using the Open Database Connectivity (ODBC) standard. It provides a common interface for connecting to different types of databases, regardless of the underlying database system.

Comparison of Data Providers

When choosing a data provider in ADO.NET, programmers need to consider various factors, such as performance, features, and compatibility. Here are some of the factors that programmers should consider when choosing a data provider:

1. Performance

The performance of a data provider is critical when working with large databases. Some data providers, such as the SQL data provider, are optimized for specific database systems and provide better performance than others.

2. Features

Different data providers provide different features that programmers can use when working with databases. For example, the Oracle data provider provides advanced features for working with Oracle databases, such as support for Oracle-specific data types.

3. Compatibility

Programmers need to ensure that the data provider they choose is compatible with the database system they are working with. Some data providers are only compatible with specific database systems, while others provide support for multiple database systems.

Explanation of ADO.NET Objects

ADO.NET provides a set of objects that enable programmers to interact with databases efficiently. In this section, we will discuss each of the ADO.NET objects in detail.

1. Connection Object

The Connection Object represents a connection to a data source. It provides various methods and properties to control the connection to the database. The Connection Object can be used to establish a connection to a database, open and close the connection, and manage transactions.

When using the Connection Object, programmers need to specify the connection string that contains information about the database server and credentials to authenticate the user. Once the connection is established, programmers can use the Connection Object to execute queries against the database.

2. Command Object

The Command Object represents a query or stored procedure that is executed against a database. It provides various methods and properties to execute queries and retrieve data from the database.

The Command Object is used in conjunction with the Connection Object to execute queries against the database. Programmers can specify the SQL statement or stored procedure to execute, along with any parameters that need to be passed to the query.

The Command Object can also be used to execute non-query commands, such as inserting, updating, or deleting data from the database.

3. DataReader Object

The DataReader Object provides a fast, read-only, forward-only stream of data from a database. It provides various methods and properties to read data from the database.

The DataReader Object is used in conjunction with the Command Object to retrieve data from the database. Once a query is executed using the Command Object, the DataReader Object is used to read the data from the database.

The DataReader Object is useful when retrieving large amounts of data from the database, as it provides a fast and efficient way to read data from the database.

4. DataAdapter Object

The DataAdapter Object provides a bridge between a DataSet Object and a data source. It provides various methods and properties to populate the DataSet Object with data from a database.

The DataAdapter Object is used to retrieve data from the database and populate a DataSet Object with the data. Once the DataSet Object is populated with data, it can be used to manipulate the data in memory.

The DataAdapter Object provides various methods to fill the DataSet Object with data, such as Fill(), FillSchema(), and Update(). Programmers can also specify parameters to filter the data retrieved from the database.

5. DataSet Object

The DataSet Object is an in-memory representation of data that can be used to manipulate data in disconnected mode. It provides various methods and properties to manipulate data in memory.

The DataSet Object can be populated with data using a DataAdapter Object. Once the DataSet Object is populated with data, it can be used to manipulate the data in memory without having to interact with the database.

The DataSet Object provides various methods to manipulate data, such as Add(), Remove(), and Modify(). It also provides methods to query the data, such as Select(), Find(), and Filter().

Interaction between ADO.NET Objects

ADO.NET objects interact with each other to perform various operations such as connecting to a database, executing queries, and retrieving data. In this section, we will discuss how ADO.NET objects interact with each other.

1. Connection and Command Object

The Connection Object provides the connection string to the Command Object, which is used to execute queries against the database. The Command Object uses the Connection Object to establish a connection to the database and execute the query.

Programmers can specify the connection string when creating the Connection Object or set it using the Connection Object’s ConnectionString property. Once the connection is established, programmers can use the Command Object’s ExecuteReader() method to execute the query and retrieve data from the database.

2. Command and DataReader Object

The Command Object executes the query against the database and retrieves the data. The DataReader Object is then used to read the data from the database.

Programmers can create a DataReader Object by calling the Command Object’s ExecuteReader() method. The DataReader Object provides various methods to read the data, such as Read(), which reads the next row from the data source, and GetInt32(), which retrieves an integer value from the data source.

The DataReader Object is optimized for reading large amounts of data from the database efficiently.

3. Command and DataAdapter Object

The Command Object provides the query to the DataAdapter Object, which then populates the DataSet Object with data from the database.

Programmers can create a DataAdapter Object and specify the SQL statement or stored procedure to execute using the Command Object’s CommandText property. The DataAdapter Object’s Fill() method is then used to populate the DataSet Object with data from the database.

The DataAdapter Object provides various methods to update the database with changes made to the DataSet Object, such as Update() and UpdateBatchSize().

4. DataAdapter and DataSet Object

The DataAdapter Object populates the DataSet Object with data from the database. The DataSet Object is then used to manipulate the data in memory.

Programmers can create a DataSet Object and pass it to the DataAdapter Object’s Fill() method to populate it with data from the database. Once the DataSet Object is populated with data, it can be manipulated using various methods such as Select(), which retrieves a subset of the data, and Sort(), which sorts the data.

Programmers can also make changes to the data in the DataSet Object, such as adding, modifying, or deleting rows. The changes can then be saved back to the database using the DataAdapter Object’s Update() method.

Conclusion

In this lesson we looked at the role and place of ADO.NET in .NET framework application development. We also looked at the basic architecture of ADO.NET in detail.

Here we determine to focus deeply on the Connected component of ADO.NET as it will continue to be used in Entity Framework. The Disconnected component is outdated and is no longer recommended. We will not go into depth about this component. Instead we will turn to the content about Entity Framework.

The content of the following articles will in turn go into the details of each part of this architecture.

If you feel the site useful, before you leave please help the site with a small action so that it can grow and serve you better.

If you find this article useful, please help share it with everyone.

If you have any concerns or need to comment further, please write in the discussion section at the bottom of the page. Thank you for reading.