While it is possible to use the TableAdapter method specified in the article I would not recommend it.  DataSets are a very heavyweight datastructure, and typed datasets are even more heavyweight.  If you look around at much of the early documentation on ASP.Net web development, you will see that Microsoft and the development community came to a concensus that the DataReader was more performant in the web environment and thus it became the prefered data access method for web development.

Still, many people do not care for the Data Access Layer that exists in DNN because of the large number of stored procedures, DAL classes/methods and BLL classes/methods that must be maintained.  This can significantly slow development.  To simplify this maintenance step, many people have resorted to using code generators to spit out the DAL and BLL code.  There is a learning curve to make code generation techniques work, and I find that I often have a need to tweak the resulting methods so I am still stuck creating custom sprocs and the associated DAL and BLL methods..  Depending on the size and complexity of you data model, this is certainly a viable option.

The third data access method that I think more people should investigate is the use of an O/RM tool to handle the heavy lifting.  In the last 2 or 3 years a lot of great options have emerged in the .Net space:  nHibernate, LLBLGenPro, EntitySpaces, XPO, Wilson ORM, nBatis, Genome and SubSonic just to name a few of the more popular options.  Each ORM is slightly different and can provide a lot of extra value over a standard generated or hand-coded DAL.  With any decent ORM the need for stored procedures almost completely goes away.  There are a few case where stored procedures are still desired, but these are the exception rather than the rule.

Instead most ORMs dynamically generate the appropriate queries and return a domain model that is pretty close to what you are used to working with.  Most good ORMs will optimize the queries and do some caching to further improve performance.

Right now, my prefered data access tool is SubSonic.  Because it is open-source, I can use it without any licensing costs.  It implements the ActiveRecord pattern which is conceptually similar to how DNN uses controllers and info classes to expose your domain model.  The biggest draw for me with SubSonic is that it uses a BuildProvider to dynamically generate my data layer at run-time.  This means that changes to my data model are almost instantly available to my module (ok... this is stretching it a little... but it is close enough without going into specifics).  This feature allows me to easily build my application and evolve the data model as I decide to add new features or rework the data model to better handle some use cases which I may not have thought about when first sitting down to code.  I prefer a much more fluid and iterative development style that is closer to agile methodologies than the classic waterfall approach.  Having a tool like SubSonic really simplifies things for me.  Unfortunately, buildproviders do not work in medium trust environments.  To handle this scenario SubSonic allows you to generate the dal that the buildprovider would normally dynamically generate.  This means I can do development using the dynamic approach and then deploy using the generated DAL.  Personally, even if Medium Trust was not an issue, I would still prefer the generated code in a production environment since I prefer for my code to be a little more "locked down" so I know exactly what code is being executed.

Anyway, I hope that helps.

Actually, the code generated DAL/BLL is much less overhead if coded to use DataReaders.  A DataSet includes a lot of extra code that is useful for cursor management and other advanced features that are not used in a web environment.  Most other DALs use arrays, arraylists or simple collections to hold each of the records.  There is no overhead associated with managing dirty records, or changed fields (remember that datasets will maintain both the original and new data).

You are misunderstanding the issue of query optimization.  When you create a stored procedure, as a developer you probably create a one size fits all stored procedure for things like inserts and updates.  What if most of the time you are only changing one or two columns of data in a record?  Most hand-coded stored-procs (as least those I have seen from most developers, which includes the ones in the core DNN install) will update every column, even if only one or two column values were changed.  This means that I had to transfer all of that data to the db and required locks on the entire row rather than locks on just one or two columns.  Many ORMs are capable of seeing if a column value has changed and can craft the update query to only affect the changed columns.  This is just one example of the types of optimizations that an ORM can do. 

As a developer, you wear many hats and have to think about many different aspects of your application.  You worry about the UI, the business logic, the data layer, packaging, deployment etc.  However, ORM vendors spend the bulk of their time just thinking about how to improve the performance and capability of the data access layer.  They spend thousands and thousands of hours on something that you would likely knock out in less than a week.  To me, coding my own data layer, or using one that was whipped together for an MSDN article, would be akin to developing my own db by hacking together a thin API over a bunch of XML files.  Sure I could get it to work, and it might even perform OK, but it will never match what people have spent their entire careers perfecting.

Does that mean that an ORM is perfect in every situation?  No.  There are many cases where embedding custom logic as close to the data as possible can yield significant performance advantages.  However, keep in mind that 95% of all DALs consist of simple CRUD statments or simple table joins.

As a final point I would point you to an Article by Scott Mitchell on his thoughts about DataSets versus DataReaders for ASP.Net applications (Scott is the author of the TableAdapter article).  Personally I think Scott did  a big disservice to the community by writing the TableAdapter article.  Too often .Net and ASP.Net in particular has gotten a bad rap as trying to cater to the "new" developer at the expense of performance and maintainability.  Point and Click/Drag and Drop is great for demos but is horrible for production applications.  With less than a couple of hours of playing around with SubSonic or another ORM, you can easily get to a point where getting a SubSonic implementation up and running requires less effort that the corresponding TableAdapter based DAL.  The SubSonic/ORM based solution will not require you to sacrifice performance and will serve you better in the long run.