Greetings,

I wrote a wrapper for the ado.net using oledb to make connection to the
database. At the time the purpose of this class was so that I could use it
against any kind of database type. However, I was told recently that
SqlConnection is better to use against sql server.

So I was wondering which is the best to use oledb or sqlconnection when
going against a sql server database, or is the performance minimal and does
not matter which of the two you use.

John

I don't have any measurements to prove that SqlConnection, SqlDataAdapter,
SqlCommand, etc. are superior to their OleDb... equivalents, but I recommend
that you use them if you're going to use SQLServer. The Sql... classes are
optimized for and provide access to specific features of SQLServer databases
that generic technologies don't.

The SqlClient namespace (and provider) is designed to extrude TDS directly.
The OleDb provider is a one-size-fits-all data access interface that uses
several layers to generate TDS. My tests show a significant loss of
performance and functionality.

Creating a single program that can be used with more than one backend is
problematic. No two database engines have the same SQL so you'll have to
compromise all along the design path to be sure that every targeted DBMS has
the feature or function you're trying to use. Most developers choose to
build these applications using a layered approach that dereferences the
actual low-level data access layer and exposes a common object layer that
does not care how the objects are constructed. This way you can create an
engine-specific DAL that can (to a greater extent) leverage the features and
power of each target DBMS.

Show quoteHide quote

Newbie Missing Something with Access DB Update Using VB .NET
Problems call DataAdapter.Update and Concurrency
What's My Password?
next vs?
What are OleDbParameter names with 'Original_' prefix?