What are the differences between a Datatable and a Datareader for Mysql queries in C#?

Question

I am creating a C# project with Mysql, my connector follows the standard Singleton and use Mysqldatareader to recover the table data and popular a List so far ok.

My problem started when I had to recover data from one table before I finished recovering data from another. Mysqldatareader gave the error "There is already an open Datareader Associated with this Connection which must be closed first". Reading on the net I saw that this error occurs in Mysqldatareader because it does not support multiple queries in the same connection, so I switched from Mysqldatareader to Datatable to popular List.

That is, because in all examples on the net it is recommended to use Datareader to popular a list, What are the problems in using Datatable?

Answer 1

Approaches in the use Datareader and Dataadapter (which is what you should be using to popular your Datatable) are the following:

Datareader:

• A connection to the database is obtained

• Sends the SQL command

• The control returns immediately to the code, as soon as there are records to process or as soon as the database informs that there is no record

• The code starts processing the records immediately - eventually the database has not even finished finding all the records yet.

• Records already processed are immediately available to be released from memory.

• Once the processing of all records is finished, the connection is released.

Dataadapter:

• A connection to the database is obtained

• Sends the SQL command

• When all records have been selected and delivered by the database, the control returns to your code.

• Connection to the bank is released.

• The records are all in memory, and now you can process them, disconnected from the database.

Completion

There is no problem in any of the approaches.

The first is best suited to processing large volume records as you gain time (start processing while the database is still selecting and delivering the records) and requires less memory because records do not need to be loaded all at the same time before they are processed.

The second is more suitable for processing smaller volume of records and for when there is the intention to edit the records and send the updates to the database.

If you’re filling one out list view, must be dealing with a relatively small volume of records so the approach of Dataadapter plus Datatable seems to be more appropriate as it is the simplest to implement and even this is the standard of ADO.Net: work disconnected.

Note: how large volume of data is processed using several Datareaders

There are database servers that allow multiple result sets on the same connection, so that you would not have this error.

In the Oracle this feature is enabled by default.

In the MS SQL Server, this resource is called MARS and can be enabled through the connection string. You would only have an exception if you tried to open a transaction while one of the resultsets is pending (you haven’t finished reading all the records) on the same connection.

When the database does not allow more than one resultset on the same connection, a common approach is to open a new connection for each resultset (in this case, for each Datareader being processed in parallel). This solution can have a higher cost because each connection to the database has a significant cost.