I'm writing a database helper component for a web application for use within
our company. This component will interface with a SQL Server 7.0 database
for now, but that might change to another database - possibly Oracle - in
the future (unlikely, but possible). Right now I have two options:
1. Write the helper class for use with the native SQL Server data provider,
and make sure the class consumers never have to use any SqlClient-specific
types. This means I will have to provide a custom type (struct) for passing
parameters, and convert these parameters to SqlParameter within the helper
class (BTW, if anybody of you has another suggestion to solve this problem
I'd appreciate it). If we switch to another database in the future, I would
have to change the types used by this class (SqlCommand to OleDbCommand,
etc.), but the code of the class consumers wouldn't need any changes.
2. Write the helper class for use with OleDb (i.e. use the SQLOLEDB
provider). If we choose this option, I wouldn't have to create any custom
types, and the application would continue to work if we would switch to
Obviously, option 2 is easier, requires less work and is less error-prone.
Option 1 is faster, however (as long as we keep using SQL Server, which is
very likely). So the big question here is:
"How big is the performance difference between using the native SQL Server
provider and using the SQLOLEDB provider ?"
Are there any bechmarks, test results, analyses, etc... online ? I would
really like to know how much performance is sacrificed if we decide to
choose option 2.
Thank you in advance.