Kassem,
It's never a good idea to retrieve the entire data at once, it's also not a great idea to issue unnecessary calls to the database every single time. There is a medium that only you, as a developer, can find.
Let's take an example the simplest form of Object Relational Mappers.
LINQ to SQL, is a very nice beginner ORM and it works just as well in large applications, for example
http://www.stackoverflow.com
In the world of database connectivity there's a concept called
Connection Pooling, which today, is already available to you hidden under the hood. Every time you are issuing a call to the database from the ORM, the pool is providing you with a connection for you to use and once you're done with it, it'll get shoved back into the pool for reuse later on. As much as you should NOT care about this, it's still good to know about. Another thing to note is that most ORMs apply a concept called
Lazy Loading in which the data will never be loaded until you access it. Here's a small example
KassemContext db = new KassemContext();
var achievements = from n in db.Achievements select n;
You'd presume that as soon as you invoked the linq query, the data has been loaded into the variable achievements. However! achievements is an
IQueryable<Achievement> which is lazy hence the data is not loaded yet! Now when if i for example decide to do the following:
var topten = achievements.Take(10);
then i've further reduced my access to ten records only, YET it's still lazy! Finally, once i try to access the data in topten, they'll be loaded:
foreach(var achievement in topten){
Console.WriteLine(achievement.Name);
}
Concerning your second question, I did not understand by what you meant by "no longer needed".