I disagree with a lot of posts in this thread that blindly pointed the OP to LINQ to SQL. I don't mean to offend anyone, but have any of you actually stepped through the debugger to look at the SQL that is generated behind the scenes for some of the complex LINQ queries. Have you looked at the metadata-related queries that ADO.NET sends to SQL Server at runtime using the SQL Server Profiler before each DB call is made?
Yes, I have. I'm quite aware of the performance penalty (in reads anyway). Bottom line, though, is that while LINQ to SQL or LINQ to Entities (which is what I use) are not the fastest solution, they are fast enough in the majority of cases. Most of us work with websites that get moderate usage. In these cases, there's simply not a compelling reason not to use an ORM imo. The flexibility and maintainability they provide simply outweighs the ~15% hit you take on queries. Developer time is valuable too.
And if it means anything, I used to have a similar attitude. I used to wonder why I would use LINQ when I could just write the queries myself because, dammit, I know T-SQL. Now I'm pretty much the sole developer with a database that has 80 tables and growing. The ease with which I can create and alter queries with LINQ made it very compelling to me to do so. I've been using LINQ to Entities for awhile now and would never go back. Performance-wise, the users can't tell the difference. Frankly, I could only tell the difference if I really tried to find it.
Good luck querying tables with more than a million rows, populating data warehouses, or hosting database-heavy Websites. LINQ to SQL does make things a lot easier, but those things are extremely unoptimized. In general, any decent programmer must learn the basics of optimally querying a database and understand concepts like database indexes. You can put a "where" LINQ query which searches for 10 different things... if the query is not covered by an index, you'll be royally screwed in production.
My site might only get moderate traffic, but I would consider it to be "database-heavy." I haven't noticed any issue with LINQ to Entities. Indexing is something that you'd have to know using ADO.NET or LINQ. And I would imagine that most large organizations that get thousands of queries per second probably have database administrators that handle this sort of thing for the developers. I'm not saying you shouldn't be aware of the concept, but if your site really needs every last ounce of performance, database performance tweaks will probably be abstracted away from you and you'll probably have database experts writing the SQL code and handing you a set of stored procedures to use.
Next week I start a job writing Python using Google's app engine. They have a data store that's pretty different than what I've been using and from my end I will basically be doing functional filtering (.filter("filter"), etc). I know the datastore is fast and reliable. How exactly does it do what it does? I have some vague idea but that's it. And frankly, that's all I care to know. I trust the Google engineers are good at what they do and will make sure my data is safe and delivered to me quickly.
To the OP: please take the time to learn the basics of ADO.NET: DataTable/DataView, DataSet, disposing connection objects, utilizing "using" blocks, etc. And stay away from SqlDataReader objects . In general, unless you're dealing with Web Services, DataTable is usually the fastest since all the data is "binary." DataSet is OK for multiple tables, but it is extremely verbose because it is XML-based.
Once you have your data in memory, you can do some wonderful things via LINQ... for now, LINQ to SQL has a lot to improve.
ORMs have some room for performance improvement but they are here to stay. I think in three years the OP will have been much better served in both experience and professional opportunities if he learns EF as opposed to sticking with ADO.NET. Of course, I'd also encourage him to ditch webforms and move to MVC .