Benefits of using sqlclient data provider with sql server 2005 over oledb data provider

Question

hi all,

Recently I think of shifting from oledb data provider to sqlclient as I am using sql server 2005 express edition as my app's backend which contain millions of records..

Can anybody give me concrete reasons for the same

please reply sooon

Answer 1

Dupe? http://stackoverflow.com/questions/473932/what-are-the-pros-and-cons-of-oledb-versus-sqlclient

Answer 2

As your using sql server 2005 as your app backend i would expect performance to be better with sqlclient as there will be no sort of transalation layer , due to it communicating via the Tabular Data Stream (TDS) which you wouldn't get with oledb due to it being a generic provider.

It mentions it in the MSDN link below...

System.Data.SqlClient includes a tabular data stream (TDS) parser to communicate directly with SQL Server.

MSDN Link

Another link which mentions the TDS

ADO.NET and SQL Server Performance Tips