There are many solutions from down and dirty to elegantly structured. I have found that establishing good practices has paid huge dividends as projects grow. Also a lot of the grunt work is done for you by the compiler.
I highly recommend reading Tutorial 1 for your language of choice at http://msdn.microsoft.com/en-us/library/aa581769
] and building from there. The whole documentation around creating a robust n-teir architecture is very well put together.
Although it is somewhat targetted at major applications it actually works really well for quick and simple sites too once you understand the principles. To add a declarative dataset for a simple table in VWD (or VS) takes seconds and you get full CRUD methods that you can use to access your table. Figuring it out the first time takes a little longer but no pain, no gain as they say.;-)
My habit now is:
All write operations are done through DataSets built declaratively in the builder UI. This mnakes things much easier for both development and maintenance of the inital TableAdapters (literally drag and drop) plus enhanced queries on to those tables.
Nearly all programmatic read operations are the same. But I mainly build a BLL (business logic layer) of classes that wrap database access so page development is just a matter of instantiating the relevant objects - the data connectivity is nicely isolated to the BLL. I use the DataSource controls fairly extensvely for page building but only for READ ONLY access. Having the user do a row selection and adding more meaningful controls to handle inserting or updaing from there via the BLL.
It's a reliable design pattern and means I always know where to look for my data access code. Also any changes are automatically propogated to ALL the pages using the BLL classes rather than having to do a lot of refactoring of multiple pages just to accommodate small changes (e.g. new columns and so on).