Jelle Druyts

Writing data access code will probably remain useful for quite some time, especially since ObjectSpaces is being rolled into Longhorn's WinFS data store. I sure do understand why, but I'm still a little sad to see it go (although it's not really gone of course).

Anyway, here's a quick dump of some things I picked up from a recent MSDN TV episode on ADO.NET 2.0. Especially cool is the "provider-agnostic data access code" so you're never coding against an actual provider (SQL Server, Oracle, ODBC, plain text, whatever, ...) but use the generic versions through a factory...

// Get the configured providers if you want to see them all.
DataTable providers = DbProviderFactories.GetFactoryClasses();
// The "InvariantName" column contains the invariant name to be passed to GetFactory.

// Use a specific provider.
DbProviderFactory factory = DbProviderFactories.GetFactory( invariantName );
using( DbConnection c = factory.CreateConnection() )
{
    // Use generic methods to create commands and other ADO.NET goodies.
    c.ConnectionString = "...";
    DbCommand cmd = c.CreateCommand();
    cmd.CommandText = "...";

    // Something else that's new: load a DataTABLE directly in stead of a DataSET.
    DbDataReader r = cmd.ExecuteReader();
    DataTable table = new DataTable();
    table.Load( r );
}

// And for perfomance, in stead of updating each row separately to the DB,
// batch them all at once to lower the number of connections to the DB.
// This will call sp_executesql(""); with a sql string that contains the batched statements.
DataAdapter da; // Initialize this...
da.UpdateBatchSize = 100;