The approaches in using DataReader and DataAdapter (which is what you should be using to popular your DataTable ) are as follows:
DataReader:
-
You get a connection to the database
-
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 you that there is no record
-
The code begins to process the records immediately - eventually the database has not even finished finding all the records yet.
-
Already processed records are immediately available to be released from memory.
-
Once all records have been processed, the connection is released.
DataAdapter:
-
You get a connection to the database
-
Sends the SQL command
-
When all records have been selected and delivered by the database, the control returns to your code.
-
The connection to the bank is released.
-
The records are all in memory, and now you can process them, disconnected from the database.
Conclusion
There is no problem with either approach.
The first one is best suited for large volume processing because you save time (it starts processing while the database is still selecting and delivering the records), and it requires less memory because the records do not have to be loaded all at once. same time before being processed.
The second one is best suited for smaller volume processing and for when it is intended to edit the records and send the updates to the database.
If you are filling a list view , you should be dealing with a relatively small volume of records so the DataAdapter approach seems DataTable be more appropriate because it is the simplest to implement and even this is the standard of ADO.Net: work disconnected.
Note: How large data is processed using multiple DataReaders
There are database servers that allow multiple resultsets on the same connection, so you would not have this error.
In Oracle this feature is enabled by default.
In MS SQL Server , this feature 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 have not finished reading all the records yet) 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 may have a higher cost because each connection to the database has a significant cost.
