oledbconnection v sqlconnection

[JRD]

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

 

[pvdg42]

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.

 

[William \(Bill\) Vaughn]

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.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)