Which is faster DataReader or DataAdapter?

How to FILL a Datagridview with a DATATABLE

OLE-DB (Object Linking Embedding Database) was the next big step forward for data providers and is still widely used today. With OLE-DB, Microsoft applied the knowledge gained from the development of ODBC to provide a better data access model. Microsoft with OLE-DB established a migration process for a COM-based API, which facilitated the modules consumable by most programming languages and the migration to a 32-bit operating system in conjunction with the release of Windows 95.

Developers using languages that support pointers, such as C, C++, VJ++, etc., can communicate directly with the ODBC and OLE-DB APIs. However, developers using a language such as Visual Basic need another layer. This is where data access consumers such as DAO, RDO, ADO and ADO.NET come into play.

With the release of Visual Basic 2.0, developers introduced a new method for accessing data, known as DAO (Data Access Objects). This was Microsoft’s first attempt to create a data consumer API.

Database Connection (VB.NET 2010 & 2012 Course)

DataSet is an in-memory data store, while the data reader is just a means to retrieve the data. On a lighter note, you can run Linq queries on DataSet, but not on a data reader. – Partha Choudhury

Actually, with a little extra code you can certainly run linq queries (or at least one query) on a data reader. Just use an iterator block to return the DataReader cast as an IDataRecord inside your while (reader.Read()) circle. – Joel Coehoorn

@RickNZ Don’t be too quick to rely on using statements to close things out for you. They call the object’s Dispose () method, not the Close () method, and I’ve run into at least one case where Dispose didn’t close the object for me. It’s always best to include an explicit call to the close method inside the using block. – Cdaragorn

@Cdaragorn, the MSDN docs are usually pretty clear about Close () vs Dispose (). In the case of SqlConnection, for example, the docs say that Close () and Dispose () are functionally equivalent. I have no objection to calling Close (), but calls to Dispose () should also be there for all IDisposables, and the cleanest way to do that is with a use statement. In cases where you know that Dispose () does not call Close (), then you should call Close () inside a final block if you can, not inside the using block (so it is still called if there is an exception). – RickNZ

Insert multiple rows(massive data)Transact-SQL, Commit

I know DataAdapters have performance issues, but is there a way around this that might be faster? At the moment, the DataAdapter.Fill method takes 5-6 seconds for 3000 records, which is too slow for my application. If I remove the Fill line and just execute SQL (using SQLCE), it takes 20 milliseconds, so I guess the query is not the problem. I tried adding BeginLoadData in the data table, but it doesn’t make any difference in performance.

I added the additional code and removed the BeginLoadData . I moved the database to my local machine to make sure it’s not a connection issue. @DJ Kraze, I thought the updates, deletes and inserts were mainly for DataAdapters and DataReaders for read-only stuff.

check out the link … I would also change that DataTable personally and go with a DataReader … also know that you can link data grids to List <> and it can be even faster … just an FYI …


It seems to me that there was some confusion here. Tristan is talking about a DataGridWinForms and you are talking about a DataGrid Web. Bad choice of names for the two controls. The thing is that in Windows applications you can’t link controls to DataReaders while in web you can. And it is because it is forward-only. In web the reader runs forward-only generating the HTML for each record. In windows the interaction with the application is different and for that reason you cannot have as data source something that you can only read once and only forward.–Eduardo A. Morcillo [MS MVP VB]http://www.mvps.org/emorcillo