(1) the Fill() method encapsulates the opening and closing of the database. Explicitly Opening and Closing of the connection is NOT needed.
(2) the ExecuteNonQuery() method should probably be avoided; it doesn't facilitate n-tiered application development, and is often a security concern (SQL injection). Through the DataSet and ADO.NET we have a new model for data access.
(3) the Dispose() methods are unneeded and should generally NOT be called explicitly as it invokes garbage collection, thus creating additional overhead. Instead you should set your DataSet and DataAdapter to null. Calling the .Clear() on a DataSet is a good idea as it frees up unneeded resources, however the Garbage Collection would have cleaned up those resources when the DataSet went out of scope.
(4) Transactions coupled with stored procedures are good.
Will you pay a heavy price in resources/speed? That depends on what are you using now? I can't give a quantitative answer.
(5) Not too clear on the question, but Stored Procedures are very quick.
(6) SqlClient over OleDB, OleDB over ODBC. SqlClient has been optimized for SQL Server and ASP.NET, OleDB adds another layer on DB communication, and ODBC adds even more layers.
You should read up on ADO.NET, specifically DataSets, and DataAdpaters..
Through DataAdapters you can offer an alternative too: [string mySQLString = "SQL for INSERT/DELETE/UPDATE/SELECT";]
You may want to consider buying an ADO.NET reference book..
Links of interest: