CODEWITHSUNDEEP
.netsql-server
Jeff Stock
Jeff Stock

Reputation: 3856

Understanding DataTable with SQL Server DataAdapter for best speed

I have been wondering this and can't really find a straight answer. In .NET when you fill a DataTable with rows from the database using the SQL Server data adapter does it truly load into memory right then and there or does it still read data from the database as you are accessing it in some regard?

Upvotes: 1

Views: 632

Answers (3)

Cerebrus
Cerebrus

Reputation: 25775

The .NET DataSet (and by transference, a DataTable) is "a disconnected in-memory representation of data stored in the database", if I remember my textbook definition exactly.

That definition is self-explanatory but to clarify... it does really load that data into memory as soon as you call Fill() on the DataAdapter. It does not need to hit the database again until you explicitly call another method such as Update(), Delete() or Insert().

The DataAdapter also implicitly manages the connection itself. It opens the connection before hitting the database if it was closed and automatically closes it after the job is done.

Upvotes: 1

Adam Robinson
Adam Robinson

Reputation: 185663

Yes, when an adapter fills a DataTable, all of the data is read from the database into memory and stored in the DataTable. No persistent connection is required. Obviously you'll need a connection if you want to refresh the data in the DataTable or save changes to the database, but you do not need to keep a connection open in order to use the data in your DataTable.

Upvotes: 1

Otávio Décio
Otávio Décio

Reputation: 74290

You can close the connection after filling the DataTable. It does not need to access the database anymore after filling. The DataReader requires an open connection as it is being used.

Upvotes: 1

Related Questions