I'm casually skimming an ASP.NET book for review purposes and I came across mention of the connection factory classes in ADO.NET 2.0.
I forgot about these; I've always seen abstract, app-specific DAL base classes that get implemented with a SQL, Access, or other database-based implementation, but I've never seen anyone use DbProviderFactories.
The book claims that these factory classes provide database neutrality in instantiating a database connection, so that you can use SqlConnection but also OdbcConnection, et al, without changing or recompiling any of the codebase, "without affecting the application's performance!"
No performance hit? Is it not using reflection? I fired up Reflector to introspect these classes, namely System.Data.Common.DbProviderFactories, System.Data.Common.DbConnection, System.Data.Common.DbCommand, and System.Data.Common.DbDataReader. Reflection is used. It's fine, relflection is there for a reason, but when used in any loop it is also notoriously slow (at least 10x the invocation time of a strongly referenced invocation). I suppose if the application has a very lightweight load, it might not matter.
I wrote and ran a performance comparison test in a console app. First I just ran two near-identical methods seperately, each in a loop (1000x), one method using DbProviderFactories and one just using SqlConnection, and both using SELECT to return all rows in a single-row, 4-column table. Then I realized it would be good to measure the performance of the last run of each, because the first few runs and especially the very first run will be expectedly slower due to runtime caching and JITing.
Here's the end result:
Factory: 23739 ticks / 2ms (total @ 1000x: 2331ms)
SqlClient: 11233 ticks / 1ms (total @ 1000x: 1321ms)
Now the question becomes, does 1 millisecond difference per connection instance matter, considering how high that number's gonna go when it goes over the wire and both data load and business logic is going to increase things to anywhere from 10ms to 1000ms?
Perhaps not. There is a difference, but it is subtle. The debate is kind of like the debate about "" versus String.Empty.