Good question!
As far as databinding goes, you should look at it as only one component of your overall data access strategy. My strategy has three components (I get to your DataBinding in component 2):
First, I always create (or reuse, mostly) a Data Access Layer (DAL) to simplify data access. This is not because I may someday swap out your database for another - the chances of that are slim enough that it doesn't warrant all the work that'd be required (YAGNI). You do this so that you can A) remove all of the clutter from normal database code (e.g. getting the connection string, setting up and closing connections, etc.) and B) simplify common operations with dedicated functions.
Second, you absolutely should implement ObjectDataSources that encapsulate the DataBinding for your UI controls. If you've built a good DAL, this becomes pretty trivial. For example, here is a ObjectDataSource that uses my DAL:
[DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
public List<EnrollListMemberData> GetNameList(int classID, DateTime classDate)
{
using (BSDIQuery qry = new BSDIQuery())
{
return
qry.Command(
"Select a.ClassDate, a.ClientID, b.FirstName, b.LastName, b.ID From ClassEnroll a inner join Folder b on (a.ClientID = b.ClientID) Where (a.ClassID=@ClassID) AND ")
.ParamVal(classID)
.Append("(DateDiff(d, a.ClassDate, @ClassDate) = 0) Order By LastName;")
.ParamVal(classDate)
.ReturnList<EnrollListMemberData>();
}
}
A few things to note: the "DataObjectMethodAttribute" attribute will make this method visible to the design-time environment so that you'll see it in the pull-down list of data sources when you go to link up your Grid (or whatever). You'll also need the [DataObjectAttribute] attribute on the class that provides this method (this class if part of my Business Layer). Finally, this is a pretty simple example and doesn't have some common constructs such as startRowIndex and maximumRows parameters for returning paged results.
Note that the particular call here is from my DAL - it is not LinqToSQL even though it has a surface similarity. I like SQL and I don't want C# idioms that just have an arbitrary mapping back to SQL anyway. Note that if I attempted to implement all of this in straight ADO calls, this function would be three times as long and have LOTS of code that really wasn't germane to expressing my goals.
Third, I always place multistep database operations in stored procedures to minimize the number of calls over the wire to the database. For example, I provide a "Check In" feature in one product that takes a check in request, checks it against a membership table, retrieves previous check in history (how many visits in the last month?), awards incentive points if appropriate, etc. etc. Running multiple queries and changes to the database in C# code would be horribly complex and quite expensive. In keeping with our DAL philosophy, I also encapsulate the call to the stored procedure in my DAL so that the actual call from code is just:
int status = dal.CheckIn(userID, ref checkInHistory);
As you can see, using a stored procedure and encapsulating it in a C# class method also makes the code far easier to read. My code just says what it does (as above) rather than having 100+ lines of code setting up queries, etc.
I hope that this helps!