There is no one single unambiguous answers to the question when and why. It depends on many factors in too many different scenarios. You don't need to observe all thinkable scenarios at once, right? Your reference to 1000 records tells us almost nothing.
The whole approach is wrong. You should first learn how things work and then try to link this knowledge with your problem and requirements trying to use logical reasoning.
I usually recommend this CodeProject article for starters:
Using ADO.NET for beginners[
^].
See also:
http://en.wikipedia.org/wiki/ADO.NET[
^],
a place to start:
http://msdn.microsoft.com/en-us/library/aa286484.aspx[
^].
—SA
Submit an article or tip