Differences Between EF, Connected Model, And Disconnected Model

The topic of today's article is devoted to the differences between the approach models of ADO.NET and which case we should use in practice.

ADO.NET (ActiveX Data Objects for . NET) is a Microsoft technology designed to manage data in databases or other resources. Through ADO.NET, we can easily manipulate and handle various forms of Data (Excel, Outlook, Access, SQLServer, Exchange, OLE DB, XML, etc.).

As you know, ADO.NET currently has 3 model forms.

1. Connected Model
2. Disconnected Model
3. Entity Framework

Connected Model - works with the database in "online" mode. If you need to manipulate the data, the database must be open. If you need to get data, filter, sort, etc., you must be connected to the database. DbConnection, DbCommand, DbDataReader, DbDataAdapter, DbParameter, and DbTransaction are the primary parent classes that provide the architecture of the connected Model. Ado.NET technology has risen to an even more abstract level due to EF (Entity Framework), but the connected Model has not lost its importance and cannot lose it. Because they still use the connected Model in EF or disconnected model background. We can easily use this API in the following cases.

When to use the Connected Model?

1) For the learning purpose 

I think the best way to get acquainted with SQL and start working with SQL in .NET is the connected Model. Because starting directly from the disconnected Model or EF does not allow us to understand how the processes are executed in the background. You can view the Connected Model as the lowest layer. This will enable us to understand the principle of executing SQL queries. NET.

2) Accompaniment (codebase support)

Maybe the finished project you started working on is written on the connected Model. I have come across such projects several times. The point is that in some projects, the DAL layer is formed and written with the data access-connected model.

3) The need to write a new ORM (EF sucks sometimes)

Although EF is justified in many cases, it is not valid. Some companies even have to build their own DAL model, abandoning not only EF but all other ORMs for speed. In this case, you must work directly with the connected Model.

Disconnected Model

In this mode, ADO.NET manipulates the data in the container by loading it into RAM. Each time you connect to the database and pull data from it or insert data into it, it takes time and uses resources. We can use the necessary data from the database to minimize consumption by loading them into the RAM. It is true that Microsoft uses the pooling process to reduce the shortest time and resources in connecting to the database every time. However, in many situations, the disconnected Model is still very optimal. The disconnected Model models the database in RAM using the classes DataSet, DataTable, DataColumn, DataRow, DataRelation, etc. Since we are already fetching data from RAM and manipulating the data structure in RAM, the processes are executed very fast. A connection to the database is created only to keep the data permanent, and the data transfer process to the actual database takes place. We can describe the working process of the Disconnected Model in simple language as follows:

A query is made to the database, and all the necessary information in the database is stored in the RAM. The private classes provided by the Disconnected Model allow us to construct the Model of the data writer in RAM. After that, the connection to the base is lost. If we update or delete some data, those operations only happen in RAM. It does not affect the real base. After receiving the real data from the database, the Disconnected Model follows (tracks) those data throughout the lifecycle. If there is a change in the data, it generates the INSERT, UPDATE, and DELETE commands corresponding to that change. Finally, after finishing the work with the data in RAM, we transfer the data in RAMd to the database by giving the save command. In what cases is the Disconnected Model used?

When to use the Disconnected Model?

1) When the connected Model does not provide enough speed for any reason

If the connected Model cannot provide enough speed due to equipment, distance, data volume, or any other reason, we can easily use the disconnected Model. Suppose the project we are working on requires frequent access to the database and different operations on data (filter, sort, etc.). In that case, the disconnected Model can be considered for optimization purposes. It is more convenient to sort and filter data in RAM than to send soft and filter requests to the database every time. In many cases, the data is brought with the connected Model, and a sort filter is given with ajax, but that sort and filter are applied only to the obtained data. That is, you can fetch, for example, 100 lines of data from the database and sort or filter it on the UI side without a disconnected model. But this will only apply to those 100 data. If there are more rows in the database, those rows will not participate in this process.

2) When you continue an unfinished project at your company, and the disconnected Model is used

In several projects we worked on, the database was working with the disconnected Model. This can be seen as an "architectural decision" at least. In such a case, one part of the project could be written with the connected Model or one part with EF, but in my opinion, this leads to confusion. As much as possible in a project (not too big), you should aim to pick up only one style. But it is not necessary to look at it as a dogma. Because in massive projects, the concepts of active data (tables that are often manipulated) and passive data (passive tables) emerge. Therefore, both connected and other models can participate in one project.

Entity Framework (EF) is Microsoft's recommended database model for connecting to databases. EF is mainly used in the modern type of newly written applications. It is created as an abstraction over Ado. NET. In 99% of situations, ORM meets almost all our requirements.

In other words, it still uses the connected and disconnected Model in the background. On the face of it, it is an ORM that works in an OOP style. (For some reason, EF, which broke the back of OOP, is seen as an OOP style. EF has crossed many sound principles of writing programs in an OOP style and is still recommended because it is easy to use. ) In almost all types of projects starting from scratch EF is used. I guess it is the same for you. To understand how EF works in the background, I think it is imperative to understand the connected and disconnected Model. EF creates a conceptual description of the database in the form of classes to work with the database. On the front, we work with classes in OOP style as if we were working with a LIST, and in the background, SQL queries are generated according to the container we are working with. The goal is to further insulate programmers from SQL and ensure that we work with the database as we do with ordinary collections and data structures.