There are a number of ways to query for data that is stored in a SQL Database from ASP.NET. Generally the best way is to use stored procedures. They can be compiled to be faster, and they are also much safer as they protect you from SQL injection problems, as discussed here. For an example of using stored procedures, check out: HOW TO: Call SQL Server Stored Procedures in ASP.NET by Using Visual C# .NET.
This is all good information to use, but it doesn’t address the question of what to do about a large table. For example, you can easily end up with a table in a database with over a million rows in it. If you allow a query to run that basically does SELECT * FROM Table, you could end up running out of memory. If you capture a dump and look at !dumheap -stat, it will show something like:
Granted this is a x64 dump, but you get the idea. We have a bunch of System.Data.DataRow object (1,430,264) and if we look at the System.Data.Datatables (not shown) we see we only have two. Looking at them, we see:
The solution here is to limit the amount of data that the stored procedure can return. One way is to discussed here. A good way to tell that this is the problem you are hitting is if your site works completely fine and then suddenly memory jumps by a large amount in a short period of time. Getting a dump and looking at the output of these commands will tell you for sure.
Book: “Real World ASP.NET Best Practices” by Matt Milner and Farhan Muhammad