After reading Rob Howard's post, his MSDN article, and the reply from Nick Berardi, I decided to make a few comments.
The framework that I have started testing and using is from Deyan Petrov's Data Access & Transaction Handling Framework that he posted on Code Project.

With his framework, you define your Providers in an xml file (very similar to the way Nick does):

<dataProvider name="OleDb_1.0" 
    connectionType="System.Data.OleDb.OleDbConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
    commandType="System.Data.OleDb.OleDbCommand, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
    parameterType="System.Data.OleDb.OleDbParameter, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
    parameterDbType="System.Data.OleDb.OleDbType, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
    parameterDbTypeProperty="OleDbType"
    dataAdapterType="System.Data.OleDb.OleDbDataAdapter, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
    commandBuilderType="System.Data.OleDb.OleDbCommandBuilder, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
    parameterNamePrefix="@" />

You would do the same for SqlClient, Oracle, etc.
You then define your DataSources:

<dataSources>
    <dataSource name="test.oledb.access.pubs" default="True"    
        provider="OleDb_1.1"
        connectionString="provider=Microsoft.JET.OLEDB.4.0;data source=c:\test\pubs.mdb"
        dataOperationsDir="commands"
        dataOperationsFileMask="pubs.oledb.access.*.xml" 
        parameterNamePrefix="@" />
</dataSources>

From your code, you no longer have to worry about what type of provider you are talking to.
Fore example, in your Data Access Layer, you would have the following code:

private IDataSource _ds = null;
_ds = DataSourceFactory.GetDataSource();   // this gets the default Data Source.
// or this gets a specific named Data Source
// _ds = DataSourceFactory.GetDataSource("test.oledb.access.pubs");
IDataCommand cmd = _ds.GetCommand("InsertAuthor");
// ...
// Do stuff to the Command object here...
// ...
cmd.ExecuteNonQuery();

In another layer, you would wrap this with the Transaction model:

public int InsertAuthor(AuthorData value)
{
    int result = 0;
    TransactionContext ctx = TransactionContextFactory.GetContext(TransactionAffinity.Required);
    try
    {
        ctx.Enter();
        AuthorService service = new AuthorService();
        result = service.InsertAuthor(value);
        ctx.VoteCommit();
    }
    catch( Exception ex )
    {
        ctx.VoteRollback();
        Console.WriteLine(ex.ToString());
    }
    finally
    {
        ctx.Exit();
    }
}

It is very simple and easy to use as you can see, but we have gone a step further...
We have made the class that would normally hold the this transactional code, and made it inherit from ContextBoundObject.
Now we can use the following code:

[Transaction(TransactionOption.Required)]
public int InsertAuthor(AuthorData value)
{
    int result = 0;

    AuthorService service = new AuthorService();
    result = service.InsertAuthor(value);
}

Under the covers, right now we are just calling Deyan's Transaction code to start, stop, commit & rollback a transaction.

One of the real beauties of all this is that you can make a transaction go around calls to different Providers! 
We are going to be customizing his work a little more by defining some of the common providers (SqlClient, OleDb, etc) in code instead of the xml, just to reduce the chance of error and to make the xml file a little smaller.  You will still be able to define other providers (MySql & others) in the xml and it will add it to the list of providers. 

If anyone else has been working with this framework, let me know as I will have to test this inside and out before it can go into production.

I also want to go through Rob Howards Provider model and see if there is anything I could use to make this framework better.  Feel free to give me a comments or suggestions!

[Rob Howard's Blog]

A couple months back I mentioned publishing our provider specification we're using for Whidbey. It's now available on MSDN in the resurrected Nothin' but ASP.NET column. This first article outlines the pattern, and in the following column we'll walk through using this pattern for ASP.NET 1.1. There are in fact two ASP.NET 1.1 applications following it today: the ASP.NET Forums (http://forums.asp.net/) and DotNetNuke (http://www.dotnetnuke.com).

Thanks to Kent our MSDN ASP.NET community superstar for helping get this pushed through --- and convincing the MSDN staff to start the column back up again! Hey, it was only a short-break (2 years <g>).