Introduction to ADO.Net - Naukri Code 360

Introduction

.Net is a framework that Microsoft creates for software development, and ADO.NET is a module for accessing the data which is used by the .Net framework.

This article serves as an ADO NET tutorial, and you will be learning everything from a simple introduction to ADO NET to everything in detail. We will be learning what ADO NET is and also about its architecture.

We will also be learning about the features of ado.net and what is meant by the object model in ADO.NET.We will also be discussing the different applications of ADO NET and the advantages and disadvantages of ADO NET.

So, without further delays, let's dive deeper into the topic and explore ADO NET.

What is ADO.NET?

ADO.NET is a technology developed by Microsoft as part of the .NET framework. It serves as a bridge between the framework and databases, simplifying the task of accessing and manipulating data. ADO.NET consists of libraries with predefined functions that aid programmers in working with databases. ADO.NET offers consistent access to various data sources, including SQL Server, XML, and those exposed through OLE DB and ODBC. It enables data-sharing consumer applications to connect to these sources, retrieve data, perform operations, and update it. ADO.NET distinguishes data access from Data manipulation, providing separate components that can be used independently or together.

It includes .NET Framework data providers facilitating database connectivity, executing commands, and retrieving results. These results can be processed directly, stored in an ADO.NET DataSet object for flexible presentation to users, combined with data from multiple sources, or transferred between application tiers. The DataSet object can also be utilized without a .NET Framework data provider to manage application-local data or handle XML-based data.

ADO.NET Architecture

ADO.NET architecture consists of six parts which are-

Connection
Command
DataReader
DataAdapter
DataSet
DataView

ADO.NET uses a multilayer architecture with a few concepts, for instance, Connection, Reader, Command, Adapter, and Dataset objects. ADO.NET introduced data providers, a set of special classes to access a specific database, execute SQL commands, and retrieve data. The Data providers are extensible. Developers can create their providers for a proprietary data source. Some examples of data providers include SQL Server providers, OLE DB, and Oracle providers.

We will be discussing all the components of the ADO.NET architecture in great detail later on in this article.

Features of ADO.NET

ADO.NET has many features associated with it. Some of them are-

The applications that are built using ADO.NET can be modeled in separate layers. And this makes the code and application maintainable.
The data architecture is scalable as it uses only disconnected data on the server, and since everything is handled on the client side, performance is enhanced. And this also increases scalability.
Data is transferred from one data source to another, represented internally in XML format. And this makes the interoperability of data possible.
ADO.NET supports access to many database systems, such as SQL and Oracle. And this helps users to work with different databases consistently.
ADO.NET also allows users to work with database transactions and ensures data integrity and consistency. ADO.NET supports both local and distributed transactions.
The programming style of ADO.NET is straightforward. In ADO.NET programs, user-defined words are used to construct statements or to evaluate expressions.

ADO.NET Object Model

ADO.NET provides an object-oriented view of the database, encapsulating many of the database properties and relationships within ADO.NET objects.

The ADO.NET objects also hide the details related to database access and provide encapsulation; they can interact with ADO.NET objects, and the developers need to worry about how that interaction is taking place and how data is being fetched from the database.

There are two main components of the object model in ADO.NET-

Connected Model
Disconnected Model

Connected Model:
In the ADO.NET connected model, your application establishes an instantaneous and non-stop connection to the database all through its operation. It's like a telephone call wherein you stay on the line for complete communication. When you operate the connected model, you fetch data from the database, work with it, and immediately return any changes. While this guarantees actual-time data, it can use extra sources and won't be appropriate for all techniques.

Disconnected Model:
In the disconnected model, your application connects to the database, retrieves the information, and then disconnects. Think of it like analyzing a book; you take it home to read, making notes or highlights. When you're finished, you come back to the book. Similarly, within the disconnected model, you work with data offline, making modifications as needed, after which you reconnect to the database to apply those adjustments. This model is efficient for handling data independently and reducing continuous connection overhead.

Connected vs. Disconnected Model

The connected and disconnected Data models have the following differences.

Connected Data	Disconnected Data
This type of Connection maintains a continuous connection to the database.	A discontinuous connection is maintained, and data is retrieved from a data structure such as a dataset.
It is a simpler implementation compared to disconnected data and increases the performance of smaller applications.	It is quite complex in comparison to the Connected data.
It reduces the application's scalability because it requires continuous Connection to the database.	It increases the application's scalability because it does not require continuous Connection to the database.
It is generally used for smaller applications where complex data analysis is not required.	It is used for multi-layer applications where complex data analysis is required.

Connection Object

The Connection object is a component of ADO.NET. The connection object initiates a connection to the data source.

All configurable aspects of a database connection are represented in the Connection object, including ConnectionString and ConnectionTimeout.Connection object helps the developers to access and manipulate a database. A database transaction is also dependent upon the Connection object.

In ADO.NET, the database we are working with defines the type of Connection.

The following are the commonly used connections in the ADO.NET

SqlConnection
OleDbConnection
OdbcConnection

Command Object

The Command Object is an important component of ADO.NET, allowing us to create and execute SQL queries over our Connection. It handles standard database operations like Select, Insert, Delete, and Update.

The data is retrieved as a RecordSet object, which provides various ways to manipulate it through its properties, collections, methods, and events. An important feature of the Command object is its ability to work with stored queries and procedures with parameters.

DataReader Object

The DataReader object works in a connected model, and it is built to retrieve and examine the rows which are returned in response to our queries.

No DataSet creation occurs; at a time, only a single row of information from the data source is in memory. This makes the DataReader quite efficient at returning large amounts of data.

The DataReader always returns read-only data, meaning you can't modify the data returned. A DataReader is designed to be a lightweight and efficient way to quickly go through data (this was called a firehose cursor in ADO).

DataAdapter Object

The DataAdapter is responsible for transferring the result of a database query from a Command object into a dataset using the DataAdapter.Fill() method. It can also update any changes made in the DataSet back to the original data source using the DataAdapter.Update() method.

The DataAdapter operates in a connected model and follows these steps:

Establish or open the connection to the database.
Retrieve the data based on the specified command.
Convert the data into an XML file.
Populate the DataSet with the retrieved data.
Close the database connection.

DataSet Object

The DataSet is a crucial component in ADO.NET for managing disconnected and distributed data scenarios. It acts as a memory-based data representation, offering a consistent programming model for relational operations regardless of the data source.

You can use it with various data sources, including XML, to handle local application data effectively. The DataSet encompasses complete data sets, including related tables, constraints, and table relationships, aligning with the relational database model.

Furthermore, the DataSet provides the ability to save its data as XML and its schema as XML schema definition language (XSD) schema, allowing for the persistence and reloading of its contents.

DataView Object

It enables us to change the appearance of the data stored in the database. We can also modify the order in which data is sorted in a table. We can also filter out data depending on the row state or a filter expression using a DataView.

LINQ and ADO.NET

LINQ stands for Language Integrated Query, a component of the .NET framework used to add querying functionality to the .NET languages and helps us use the LINQ queries on the ADO.NET objects. We can write LINQ queries to perform querying on various data sources such as SQL databases, DataSet Objects, and XML Documents. When we transfer data from SQL tables into objects in memory, the chances of occurring of an error are often high, and it is also a tedious task.

The LINQ to ADO.Net helps us write the LINQ Queries on Enumerable objects in ADO.NET.The programmer always views the data as an IEnumerable collection when they query and update the data. Full IntelliSense support is also provided to the programmers for writing queries against those collections.

LINQ allows programmers to write queries using programming languages like C# or VB.NET. It makes working with data in a strongly typed and compile-time-checked manner easier.

There are three different ADO.NET Language-Integrated Query (LINQ) technologies:

LINQ to DataSet, which provides an optimized querying over the DataSet,

LINQ to SQL enables us to directly execute queries over SQL Server Database schemas and LINQ to Entities, which allows us to query an Entity Data Model.

Let's learn about them in detail.

LINQ to DataSet

LINQ to DataSet means to perform the LINQ queries on the DataSet object, which is one of the most important components of the DataSet object which belongs to the disconnected object model. The LINQ query is performed on the DataSet since it has few querying capabilities.

LINQ also makes it easier and faster for programmers to query over data cached in a dataset object. The data can be cached locally or stored in disconnected environments, and progr.5s can query and manipulate the cached data, which helps in faster operations on the data.

The syntax for writing LINQ queries on ADO.NET dataset object

var result = from dt in temp.Tables[0].AsEnumerable()  
where (dt.Field<string>("EmpName").EndsWith("R"))  
select new  
{  
   Name = dt.Field<string>("EmpName"),  
   Surname = dt.Field<string>("Surname")  
};

Explanation

From the above syntax, we have written the LINQ Queries on ADO.NET Dataset "temp" object to get the data.

LINQ to SQL

An Entity Data Model (EDM) encompasses a collection of ideas that define the structure of data, irrespective of its storage format. This model employs three primary concepts to represent data structure: entity type, association type, and property. The EDM facilitates using primitive data types to define properties within a conceptual model.

LINQ to Entities provides Language-Integrated Query (LINQ) support, enabling developers to write queries against the Entity Framework conceptual model using Visual Basic or Visual C#. Queries against the Entity Framework are represented by command tree queries, which execute against the object context.

LINQ to Entities

ADO.NET Data Provider

The data provider is the component of ADO.NET which is used to connect to the database and to execute the query to retrieve the data based on that query. It also enhances the performance of our application by allowing the programmers to place the data into the Dataset so that the data can be used in our application in the future.

Multiple data providers are provided by the .NET framework that we can use in our application.

Some of the data providers are-

Data Provider for SQL Server

The .NET framework data provider for the SQL server is located in the System.Data.SqlClient namespace. This namespace can be used in our application with the help of the following command

using System.Data.SqlClient;

This data provider enhances the performance by providing direct access to the SQL Server without any middle connectivity layer.

Data Provider for Oracle

The data provider for Orcale is used to connect the application to the Orcale database with the help of the Oracle Client. This data provider also supports both local and distributed transactions. Oracle Data Provider classes are located in System.Data.OracleClient namespace. We must use both Systems.Data.OracleClient and System. data to connect our application with the Oracle database. This namespace can be used in our application with the help of the following command

using System.Data;    
using System.Data.OracleClient;

Data Provider for OLEDB

This data provider connects our application to data sources that use OLEDB (Object Linking and Embedding Database). The .NET framework data provider for the SQL server is located in the System.Data.OleDb namespace.

ADO.NET Applications

ADO.NET has multiple applications. Some of them are-

Desktop Applications

ADO.NET can also be used for developing desktop applications that interact with the database. It allows users to enter data, save it to the database, and display it on desktop applications.

Mobile Applications

Technologies such as Xamarin can be used with ADO.NET for mobile application development.ADO.NET is used to access databases and update the data in mobile applications.

Data import/export tools

ADO.NET is also used to make applications that are used for exporting data to the database and importing the data from Excel or CSV files or other data sou.

Data Analysis Applications

Multiple data analysis applications also use ADO.NET since it can be used to perform tasks like data analysis and data mining. It has many libraries to perform statistical analysis and calculations on given data.

Web Applications

ADO.NET has a wide range of applications in web development and web applications. In web development, ADO.NET is used to access the database and run the query to retrieve data and display the data on web pages.

Data Type in ADO.NET

Many data types are provided in ADO.NET to handle and manipulate different types of data; mapping of data is done between .NET and the database. Some of the datatypes are-

String: It is used to represent any sequence of characters. It's used for the representation of data in the form of texts, such as name, place, etc.
Integer: It is used to represent whole numbers with no fractional part. The numbers represented by Integer data are 32-bit.
Boolean: It's used to store logical values which can be either true or false.
Double: It represents whole numbers that can have a fractional part.
Double-precision floating-point numbers belong to this datatype.
Float: It is also used to represent whole numbers that can have a fractional part. Single-precision floating-point numbers belong to this datatype.
Binary: It represents a large binary object (BLOB) to store binary data, such as files or images, in the database.
GUID: It is used to represent a globally unique value. Unique identifiers that can be used as a primary key in the database belong to GUID datatype.
Byte: It is also used to represent whole numbers with no fractional part, but the numbers represented by Byte are relatively smaller. The numbers represented by Byte datatype are 8-bit.
"
DateTime: It is used to represent date and time value. Date and time manipulation is also done with dateTime datatype.

Advantages of using ADO.NET

ADO.NET has the following advantages-

Easy Data Access: Data can be easily accessed with the help of ADO.NET from different data sources like XML documents and databases. The developers can also easily manipulate data with the help of a set of classes provided by ADO.NET.
High Performance: ADO.NET is used to disconnect data architecture which is easy to scale as it reduces the load on the database. The performance is increased since everything is handled on the client side.
Scalable: ADO.NET uses disconnected data access; therefore, the application does not retain the connections to the database for a longer period. This makes it feasible for multiple users to access the data and hence makes it scalable.
Maintainability: The data logic and the user interface is separated in ADO.NET. This allows us to create our application in independent layers. Hence it can be easily maintained.
Active Community: The community support for ADO.NET is huge, and many developers are actively working using ADO.NET, making it easier for someone who is a beginner and wants to start learning how to use ADO.NET lot of documentation is also available.

Disadvantages of using ADO.NET

ADO.NET has the following disadvantages-

Cross-Platform Support: ADO.NET has very less cross-platform support because initially, it was developed only for Windows and the .NET framework. It is difficult to use it on Linux or macOS.
Resource Management: The management of resources such as database resources, transactions, and connections must be done manually by ADO.NET.
Code Complexity: The code that ADO.NET uses for data access and management is quite complex. This makes it difficult for us to understand and maintain the code.
Learning Curve: ADO.NET is a new technology and is quite difficult to understand, especially for someone new to programming. Many concepts, such as data providers, data sets, etc., must be understood to learn ADO.NET.
Limited Scalability: The disconnected data architecture provided by ADO.NET is not beneficial in all cases regarding scalability. It becomes difficult for the developers to maintain large amounts of data.

Frequently Asked Questions

What is ADO.NET used for?

ADO.NET is a module used for accessing the data sources by the applications which use the .NET framework.ADO.NET has multiple libraries used for various purposes such as accessing the database and fetching data based upon the query and manipulating the data.

Is ADO.NET still used?

Yes, ADO.NET is still used for database access in .NET applications, especially for fine-tuned, high-performance, or legacy systems requiring direct SQL interaction.

Is ADO.NET an ORM?

No, ADO.NET is not an ORM; it provides low-level data access, while ORMs like Entity Framework abstract and manage object-relational mapping.

Conclusion

We learned what ADO.NET is, and we also learned about some basic functionalities it provides. We got to know that ADO.NET is a very useful module that is used by the .NET framework. It plays an essential role in accessing data and is used for creating web, mobile, desktop, and other types of applications and has many other applications. We also learned about all the advantages and disadvantages associated with it.