With the introduction of .Net Framework 3.5 there is a completely new way of accessing your database information that is just awesome: LINQ.
In fact, it is so intuitive, fast and easy, that I don't even bother showing people the 'old way' of ADO.Net.
Now keep in mind this is all personal opinion, but the most efficient way to access your SQL is through LINQ and there are some great resources out there for learning it.
Scott Guthrie has multiple awesome tutorials as well as videos teaching LINQ: weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx
I also recommend the book Linq in Action - it has been an awesome tool for me.
There is also the older ways which are still useful in their own right, but I steer away from them if I can anymore. I personally recommend using stored procedures and calling them via you code-behind as it is more centralized as well as more secure (speed really isn't an issue anymore). But if you do code you queries in your page, make sure you paramatrize them to prevent injection!
I hope that was somewhat clear, if you need any clarification or have any questions, please let me know!