SqlClient vs oledb question

[spebola]

I am writing an application and would like it to work with different data sources assuming the tables are defined the same (SQL Server 2000, ORACLE, etc.). I have elected to use the System.Data.OLEDB namespace. I understand that the namespace System.Data.SqlClient offers increased performance when using SqlServer 2000. Is this performance increase significant? Is my assumption that using OLEDB only requires changing the connection string to attach to different data sources correct?

 

Any comments would be appreciated.

[Robby]

"I understand that the namespace System.Data.SqlClient offers increased performance when using SqlServer 2000"

 

Yes it does. How significant? Apperantly enough.

If you use System.Data.OLEDB, you would only need to change the connection string for SQL Server.

 

You could easily make two seperate classes, one OLEDB and one SQLClient, (this gets you the best of both worlds.)

[quwiltw]

I think the performance benefits are enough to justify the trouble you'll have to go through. I've explained my solution to this somewhere else but it's essentially to do all Interface-based programming in your data access layer and create a DBFactory that creates the right instances for you depending on the data source. You'll have to give up some of the "extras" offered in SQLClient but you'll at least get the performance boost out of it. Just do all your coding against the interfaces that they all implement (IDbDataAdapter, IDbCommand, IDbConnection, etc.) It seems to work for me so far.