Introduction
When we decide to develop any enterprise-level application using .NET Framework, during the design and planning time, we always need to focus on the data operation techniques used in the application. Data operation techniques sometimes depend on the database type we will use in the backend. But despite that, we always need to focus on this process. Data access techniques provide some methods through which we can execute our query to fetch or access data. In general, the data access query engine always offers two types of material – the database objects containing the data and the executable instructions or operations through which we can retrieve or transform data.
Microsoft has often made many changes in the database access technology in the .NET Framework. But after all, the new modifications always confirm to the .NET developers that the final data access technology appeared as a solution for a long-term period.
ADO.NET – An Overview
ADO.NET stands for ActiveX Data Objects. ADO.Net is considered a data access technique in the .NET Framework. In ADO.NET, the .NET Framework programmers can access the set of classes through which they can access the data access service. ADO.NET provides an extensive collection of methods for different operations related to the data access techniques. Therefore, we can use the ADO.NET and .NET Framework as an integrated parts. With this technique’s help, we can access the application data. With the help of ADO.NET, developers can perform different development work like creating front-end database clients and middle-tier data-driven business objects that can be used in the applications. ADO.NET provides other multiple classes and methods through which we can perform different operations like the established connection using Connection Objects, Command, and can retrieve data using DataAdapter and DataReader objects.
ADO.NET is one of the most used data access technologies provided by Microsoft in the .NET Framework. It helps us communicate between different types of databases using a standard set of components (classes & methods) like relational and nonrelational databases. ADO.Net consistently differentiates the data access layer from the data manipulation layer as a discrete component to be used independently or in tandem. Developers can use ADO.Net with the help of .NET Framework data providers to establish a connection with a database, execute different commands and fetch the records as results. These records or data can be processed directly with the help of ADO.Net objects or combined with the data from various sources. With the help of ADO.Net, developers can establish Connections with different databases like Microsoft SQL Server, Oracle, Microsoft Access, XML, etc. Then, they can execute the commands to manage the disconnected data. ADO.NET provides data in two modes: connected mode and disconnected mode. In the Connected way, we need to fetch data from the live data or server, and in the case of disconnected mode, information is provided from the data store.
From the architectural point of view, ADO.NET always uses a multi-layered architecture structure. This structure always contains some key concepts like –
- Connection Objects
- Command Objects
- Dataset Objects
The architectural diagram below image demonstrates the ADO.NET Architecture concept.
Overview of Data Adapter in ADO.NET
DataAdapter is an essential part of the ADO.NET data access provider. With the help of DataAdapter, we can establish the relationship to fetch data from the database into DataSet objects. DataAdapter always sits within the data source (database), and a dataset objects and transfers the data from the data source to the dataset or vice-versa. We can also use the DataAdapter along with the Dataset Objects. This combination can be done by using the Fill Method, which mainly changes the data in the dataset object so that data can be matched with the data source, and the Update method, which changes the data in the data source to check the data with the dataset. So, with the help of these two objects, we can perform data access and manipulation-related operations.
With the help of the DataAdapter object, we can perform all types of database-related operations like Select, Insert, Update, and Delete in the data source. In the case of Insert, Update, and Delete-related database operations, we usually use the continuation of the SelectCommand of the DataAdapter objects. This SelectCommand component represents a command object type mainly responsible for fetching data from the database. For different types of data operations, we can use other command object methods like InsertCommand, DeleteCommand, UpdateCommand under the DataAdapter objects.
With the help of DataAdapter, we can connect to a dataset and define the related SQL Strings to fetch the data from the data source or write the data to a data source. The examples below show how we can bring data using the DataAdapter objects in ADO.NET. The below code block demonstrates how we can fetch data from a customer table in SQL Database.
| string ConnectionString = “Integrated Security = SSPI;” + “Initial catalog = Northwind; ” + ” Data Source = localhost; “;
string SQL = “SELECT CustomerID, CompanyName FROM Customers”; SqlConenction strCon = new SqlConnection(ConnectionString);
// open the database connection strCon.Open();
// Create an object for SqlDataAdapter to fetch data SqlDataAdapter adapter = new SqlDataAdapter(SQL, strCon);
|
Overview of DataSet in ADO.NET
Dataset acts as a container that may contain one or multiple data table objects, and these tables fetch data from the database. Dataset always provides the disconnected result brought from the data source, and this data is separated from the database. Dataset is mainly used for the data manipulation of the data source. We can fetch the data into the dataset from the data source, and once this data fetching is completed, the connection with the data source is disconnected. After the data manipulation-related work is completed, the data source connection is again re-established, and the changes are updated to the leading data source. We can also populate the data tables in dataset objects with the help of Data adapter objects.
Each Dataset object is a DataTable Collection container containing DataRow and DataColumn Objects collections. If required, we can also define constraints like Primary key, unique condition, and default values as a constraint collection within the Dataset objects. So, the dataset represents the data as a form of memory-resident representation that can establish the relation programming model independent of the database. Dataset can be used with different types of databases, with XML data, or manage the local data within the application. With the help of the dataset, we can demonstrate the entire collection data along with its related table, constraints, and relationships within the tables. From the diagram below, we can clearly understand the object model structure of DataSet.
DataSet can also represent the data as an XML format and its schema as an XML schema-based definition languages (called XSD) schema. With the help of the DataRelation object, we can establish a relationship between one row of a data table with other rows in another. First, DataRelation tries to identify the matching column names within the two tables of the DataSet. After that, it can enable navigation from one table to another table within the DataSet. We can also fill DataSet objects by using an XML document or stream. The data provided by the XML document or stream can combine the data or schema in the dataset objects.
Overview of DataReader in ADO.NET
In ADO.NET, DataReader reads the data from the primary data source. Using DataReader, developers can quickly read the data from a database because it always returns from a stream. The DataReader is mainly used to perform the reading data or forward streaming using ADO.NET. For this reason, DataReader is often called a firehose cursor or a forward read-only cursor as it can move forward with the data. While fetching data from the data source with the help of DataReader, it returned as an unbuffered stream of data through which we can implement procedural logic to process the data sequentially. It is always a good option to fetch a large volume of data from the data source as it is not cached in the memory.
Like other ADO.NET objects, each data provider provides a data reader class objects for fetching the data like OleDbDataReader, ODBCDataReader, and SqlDataReader. Once the data reader class objects are initialized, we can use various methods to bring our data from the data source. We can mostly use the Read method, which can be called sequentially and continuously read each row of the data into the DataReader objects. DataReader objects also provide a simple indexer which helps us find each column’s data from the data row. Also, we need to use the Close method when we finish our work with the DataReader Objects.
| public void FetCategoryData(SqlConnection connection)
{ using (connection) { SqlCommand command = new SqlCommand(“SELECT CategoryID, CategoryName FROM Categories;”, connection); connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows) { while (reader.Read()) { Console.WriteLine(“{0}\t{1}”, reader.GetInt32(0), reader.GetString(1)); } } else { Console.WriteLine(“No rows found.”); } reader.Close(); } } |
Comparison between Data Adapter, Dataset, and DataReader
The above section discussed the DataAdapter, DataSet, and DataReader in the ADO.NET. But before using them for development, we need to understand their primary differences. So that we can clearly understand the requirements and decide when which objects needs to be used during the development time. Therefore, the below table structure, demonstrate some critical difference between them.
| DataAdapter | DataSet | DataReadeer |
| Support Disconnected Architecture | Support Disconnected Architecture. | Support Connection-Oriented Architecture. |
| It acts as a bridge or communication between the DataSet and Data source | Dataset depends on the DataAdapter to fetch data from the data source. | DataReader is responsible for reading the data from the Data Source. It supports only read and forward only based on Connection-oriented architecture. |
| DataAdapter objects are mainly used to fetch the data from the Data Source and Bind the data into DataSet Objects | Data is assigned or stored into DataSet objects with the help of DataAdapter Objects. | DataReader performs iteration through result return from the data source and reads one by one at a time. It consumes less memory. And able to fetch large volumes of data faster than the DataSet. |
| Developers can manipulate and modify the data compared to the data source in DataSet objects. | Developers can manipulate and modify the data compared to the data source in DataSet objects. | We can’t modify the data with the help of DataReader. It can only read the data. |
