When developers start working on any application (for web or desktop versions), they spend a lot of time worrying about the backend database, its tables and their relationships, stored procedures, views, etc. Along with that, they also need to consider the data schema that will be returned from the back-end to the application. For this type of operations we can use several available frameworks such as DAO, RDO, ADO, ADO.NET, Entity Framework, etc. Among these different frameworks, the most widely used are ADO.NET and Entity Framework. So after reading this article, you will have a clear understanding of the following topics supported by clear examples:
- What is ADO.NET?
- Benefits of ADO.NET.
- Overview of the ADO.NET architecture.
- What is Entity Framework?
- Differences between ADO.NET and Entity Framework
- Why would we use ADO.Net?
- Why would we use the Entity Framework.
What is ADO.NET?
ADO.NET was invented by Microsoft as part of the .NET Framework component. With the help of this technology, we can access any type of data source through our applications and get the data in our C# and VB.NET. ADO.NET is a collection of object-based classes that provide a rich set of data components, and with the help of these components, we can create high-performance, reliable, and scalable database-driven applications. In ADO.NET models, it connects to the data source only when required by the system to read or update the data. It is one of the biggest impacts in application development. Because, in a client-server or distributed application, always having a connection resource open at all times is one of the most resource-intensive parts. Actually, we don't need to be connected to a data source all the time. We only need to connect to a data source when we are reading and writing data from a data source.
With ADO.NET, we can use SQL queries and stored procedures to perform read, write, update, and delete operations on a data source. We can use SQL syntax with the help of ADO.NET command objects and it always returns data in the form of DataReader or DataSet objects. So that after the connection is closed, we can use the DataSet objects to work on the data and after the work is completed on our computer, we can connect the data source again when we need to update the data source. A dataset is a container of multiple DataTable objects, and each data table can have a relationship between them. We can access the data source and populate the data set with the help of data providers. The .NET Framework provides us with three different types of data providers: ADO.NET, OLEDB, and ODBC.
XML plays an important role in ADO.NET. The ADO.NET model uses XML to cache data and pass it between applications. Datasets use XML schemas to store and transfer data between applications. We can even use this XML file from other applications without interacting with the actual dataset. We can use data in all kinds of applications and components because XML is an industry standard; we can transfer data over many protocols, such as HTTP, due to the text-based nature of XML.
Why we use ADO.NET
ADO.NET offers many advantages over older Microsoft-based data access technologies such as ADO. Some of the main and important advantages are the following:
- Single Object Oriented API– ADO.NET always presents a single collection of object-oriented classes. ADO.NET also provides different data providers to work with different types of data sources, but the programming model for all data providers works in the same way. So if we implement ADO.NET for a data provider, after that, if we need to change the data provider or use another data provider, we don't need to change the whole process, we just need to change the class names and connection strings. . .
- managed code– ADO.NET classes are managed classes. They take full advantage of the .NET CLR, such as language independence and automatic resource management. All .NET languages access the same API. Therefore, if we know how to use these classes in C#, we should have no problem using them in VB.NET. Another big advantage is that we don't have to worry about allocating memory and freeing it. The CLR will take care of that for us.
- Implantation– In real life, writing database applications using ODBC, DAO, and other older technologies and deploying them on client machines was a big problem that was partly solved in ADO, except that there were different versions of MDAC. Now you don't have to think about it. Installing distributable .NET components will take care of that.
- XML support –Today, XML is an industry standard format and the most widely used method for sharing data between applications over the Internet. In ADO.NET, data is always cached and passed in XML format. So this data can be shared with the component application and we can transfer data through different protocols like HTTP for different types of operations.
- Performance and scalability:When we develop any web-based application, we always focus on two main concerns, i.e., performance and scalability. Transferring data from one source to another is always an expensive process on the Internet due to limited connection bandwidth and rapidly increasing traffic. Using disconnected cached data in XML solves both problems.
- Data Reader vs. Data Set:The ADO.NET DataReader is used to retrieve data in read-only mode (cannot update data to a data source) and forward-only mode (cannot read backward/random data) from a database. data. We create a DataReader by calling Command.ExecuteReader after creating an instance of the Command object.
- LINQ to dataset -The LINQ to DataSet API provides query functions against a cached DataSet object using LINQ queries. LINQ queries are written in C#.
- LINQ a SQL -The LINQ to SQL API provides queries against relational databases without using a middle-level database library.
ADO.NET architecture concept
Microsoft has designed ADO.NET so that we can perform different types of data source operations in the same way. For the sake of simplicity, we can classify ADO.NET components into three categories: disconnected, common or shared data providers, and .NET. Disconnected components are built on top of the ADO.NET architecture. We can use these classes with or without data providers. For example, we can use a DataTable object with or without providers, and common or shared components are the base classes for all types of data providers. The following ADO.NET architecture diagram demonstrates the ADO.NET component model and how they work together.
A data provider is a set of factors, similar to Connection, Command, DataAdapter, and DataReader. EITHERConnectionis the first element that talks to a data source. With the help ofconnection object, we can establish a connection between the application and the data source. These connection objects function as reference objects in the Command and DataAdapter objects. TOcommand objectexecutes an SQL query and stored procedures to read, add, update, and delete data from a data source through a DataAdapter. TOdata adapteris grounded between a data set and the connection. We can use the command object to execute any type of SQL queries and stored procedures to get data from the database.
All data providers share common ADO.NET components. These components like DataSet, DataView, and DataViewManager always represent data on behalf of ADO.NET. DataSet component objects typically use XML schemas to capture and return data between applications and data providers. A DataSet is a subset of DataTable objects. A DataTable represents a database table. We can represent single or multiple views of a data set with the help of DataView and DataViewManager objects. In our applications, if necessary, we can directly use a DataView or DataViewManager component with data-bound controls such as DataGrid or DataList.
What is Entity Framework?
Let's talk about the Entity Framework. Entity Framework is an open source ORM Framework for applications based on the .NET framework supported by Microsoft. According to Microsoft documentation, Entity Framework has been provided to automate all kinds of database related activities for our application. With the help of this framework, developers can access the required database and start development work with data with the help of domain-specific class objects. Also, they don't have to focus on the underlying database tables or columns where the actual data will be stored. By using and understanding the Entity Framework and its framework, developers can work at a higher level of abstraction when it comes to data and can build and maintain data-driven applications with less code compared to traditional applications.
The following image illustrates where the Entity Framework fits into our application:
As the image above shows, the Entity Framework fits between business entities (domain classes) and the database. It saves data stored in business entity properties and also retrieves data from the database and automatically converts it to business entity objects.
Why we use Entity Framework
Now, let's see how the Entity Framework works. The Entity Framework offers many advantages over older Microsoft-based data access technologies such as ADO.NET. Some of the main and important advantages are the following:
- Modeling: Entity Framework creates an EDM (Entity Data Model) based on POCO (Plain Old CLR Object) entities with properties to get/set different types of data. The Entity Framework uses this EDM-based model class when needed to get data from or save data to the underlying database.
- Consultant: With the help of the Entity Framework, we can use LINQ (C#/VB.NET) queries to get data from the underlying database. During processing, the database provider will translate LINQ queries into the database-specific query language (for example, SQL for a relational database). Also, we can execute raw SQL queries directly with the help of Entity Framework for any kind of database based operations.
- change tracking: EF keeps track of the changes that have occurred in the instances of its entities (property values) that need to be sent to the database.
- saving: We can execute INSERT, UPDATE and DELETE commands on the database based on the changes with the help of Entity Framework. These changes will take place in our database only when we call the SaveChanges() method. The Entity Framework also provides the SaveChangesAsync() asynchronous method to perform the database operation asynchronously.
- Simultaneity: The Entity Framework typically follows the Optimistic Concurrency process model by default. So you can protect the overwrite changes made by another user during the same time interval while the data was fetched from the database.
- Minutes: With the help of Entity Framework, we can perform automatic transaction management while querying or saving data in all applications. Also, we can customize transaction management with the help of Entity Framework.
- Cache: By default, the Entity Framework always provides the first level of getting data during get operations. So even though the application sends the query call over and over again to the same data source, after the first time it will return data from the cache instance instead of accessing the database.
- integrated agreements: The Entity Framework always follows the standard conventions on the configuration programming pattern and we can also include a set of standard rules that automatically configure the EF model.
- settings- With Entity Framework, we can configure EF models with the help of annotation attributes or fluent API to override standard conventions related to data rule or validation.
- migrations: Entity Provides also provides a set of migration commands that we can run in the NuGet Package Manager Console or command line interface to perform different basic database schema operations such as create a table, update a structure from table or remove any column etc.
Arquitetura Entity Framework
Let's give a brief overview of the different components available in the Entity Framework architecture:
- EDM (entity data model):The Entity Data Model or EDM is divided into three main parts: Conceptual Model, Mapping Model, and Storage.
- Conceptual model:In the Conceptual Model, we normally define the model classes and establish the relationships between them. This class-based relationship is independent of the database's point of view.
- storage model:The storage model mainly contains all the database related design details such as tables, views, stored procedures and the relationships between different table objects and their relationship keys. Please read abouttable by type vs table by hierarchy, if you want to know more about it.
- Cartography:The mapping components mainly contain information related to the mapping between the conceptual model and the storage model.
- LINQ to entities:LINQ-to-Entities (L2E) is a query language. This query language is used to write object model queries in the Entity Framework layer. These queries return entities as output where the entities are used primarily from the conceptual model.
- SQL entity:Entity SQL is another query language, available only for Entity Framework v1 – v6. This language tool is very similar to LINQ to Entities. However, from an operational point of view, it is a bit more complex compared to L2E.
- Object service:In the Entity Framework architecture layer, the object service is the main entry point for getting data from the database layer. The service layer of the object is also responsible for the materialization of the data.
- Customer data provider entity:The main purpose of the Entity Client Data Provider layer is to convert LINQ-to-Entities or Entity SQL queries into an SQL query that is normally understood by any type of database. It always communicates with the ADO.Net data provider to return data from the database.
- ADO.Net data provider:This layer communicates with the database using the standard ADO.NET engine.
The following figure shows the general architecture of the Entity Framework.
To expand your possibilities, you can use this powerfulEntity Framework Designerwhich automates the process.
What is the difference between ADO.NET and the Entity Framework?
In this chapter, we will explore the difference between traditional ADO.NET and the Entity Framework. While both ADO.Net and the Entity Framework are defined in the basic pattern of ActiveX Data Objects for connecting relational and non-relational database systems. But despite this, these two have many differences from each other. Some of the main differences between ADO.NET and the Entity Framework are as follows:
- performance: ADO.NET is much faster compared to Entity Framework. Because ADO.NET always connects directly to the database. That's why it offers much better performance compared to Entity Framework. It's because, in the Entity Framework, the entity first translates LINQ queries to SQL and then processes the query to perform database operations. Additionally, the Entity Framework consists of a wrapper class for ADO.Net. Because of this wrapper, the developer can code much faster on the Entity Framework.
- Flexibility: In the case of executing SQL queries and stored procedures, ADO.NET always gives us much more flexibility and control compared to the Entity Framework. ADO.NET always provides full control over the database that we use in applications. LINQ queries always result in an efficient SQL query because raw SQL queries are always useful. We also need to log raw SQL queries whenever we can't express the query using LINQ.
- development speed: With the help of ADO.NET, we can maintain full control over the data layer of our applications. We can create from scratch the classes and methods that will help us to establish the communication between the database and the application. It always takes more time and effort to develop a data access layer compared to the Entity Framework. In the case of the Entity Framework, creating the data access layer is much easier. Because Entity Framework generates model classes and database context classes automatically. In this way, it automatically handles the operation of the database.
- Code maintainability: Code can be better maintained in the Entity Framework. Because while trying to debug the data access layer in ADO.NET, we couldn't find the proper relationship between the model classes. But in the Entity Framework it has been maintained and we have also found a clear dependency on the storage model with the help of the mapping process.
Both ADO.NET and the Entity Frameworks have similar and quite different features. To make the comparison process easier and to answer many questions about them (eg, "Does the Entity Framework use ADO.NET?" etc.), we provide a clear comparison table.
Comparison table of ADO.NET and Entity Framework
Therefore, in this article, we have discussed ADO.NET and Entity Framework along with the main differences between them. Therefore, whenever we are going to start developing any application, we must make the right decision to choose one of them. If we want to have more control over SQL commands and operations with the help of raw SQL queries, ADO.NET will be a great place to start working. While if we want to develop the application much faster and with easy code maintenance, Entity Framework will be the best option. But we can also use both approaches in a single application like Entity Framework for CRUD related operations and ADO.NET for getting database records for reporting purposes and Big Data related SQL operations.