asked on

What's the difference between accessing SQL Server thru OleDB and SQLClient Providers?

That's exactly the question:
What's the difference between accessing SQL Server thru OleDB and SQLClient Providers?

I believe it's most a performance issue but, does anyone have a benchmark?
Does the difference really shows on a practical scenario?

Is there any other drawbacks I'm not aware of?

Thanks,
Alex

ASKER CERTIFIED SOLUTION

Bob Learned

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alexandre Simões

ASKER

You've being participating on my recent questions...
I'm strugling to find the best generic data model solution.

This link http://www.devx.com/vb2themax/Article/19887/1954?pf=true lets us see the differences.
There we can read that SQLClient provider is 10x faster than OLEDb, and that the use of DataReaders can boost up to 56x a DataSet scenario.

The thing is that I want to make a unique DataModel that can interact with SQL Server 2000, MS Access and Oracle DB Engines... and OLEDB can give me that.

Alex :p

Bob Learned

Ok, let's take a cross-section look, and we will notice that there are a lot of people trying to do generic data access:

======================================================

Writing a Generic Data Access Component
www.c-sharpcorner.com/Code/2002/July/GenericDataProvider.asp

A Generic Data Access Component using Factory Pattern
http://www.c-sharpcorner.com/Code/2002/July/GenericDataAccessCompActivator.asp

Generic Data Access Objects
http://www.hibernate.org/328.html

The Code Project - Generic Data Access Layers in .NET - VB.NET
http://www.codeproject.com/Purgatory/udal.asp

The Code Project - A Generic Data Access Component using Factory ...
http://www.codeproject.com/Purgatory/A_Generic_Data_Access.asp

A Generic Data Access Component
http://www.vbdotnetheaven.com/Code/Jun2003/2044.asp

Writing Generic Data Access Code in ASP.NET 2.0 and ADO.NET 2.0 ...
http://msdn.microsoft.com/library/en-us/dnadonet/html/genericdacode.asp

Data Access and Storage Developer Center: Learning About ADO.NET 2.0
http://msdn.microsoft.com/data/dataaccess/whidbey/default.aspx

Toward a Generic Data Access WebService
http://www.eggheadcafe.com/articles/20031106.asp

[DOC] Generic Data Access in Microsoft
http://campus.lakeforest.edu/~hummel/talks/oopsla2004/Generic-Data-Access.doc

A Generic Data Access Component using Factory Pattern - DotNet ...
http://www.dnzone.com/go?276

Generic data access with ADO.NET and the Data Access Application Block
http://weblogs.asp.net/fmarguerie/archive/2003/05/19/7197.aspx

15 Seconds : Creating a Data Access Layer in .NET - Part 1
http://www.15seconds.com/issue/030317.htm

White Paper: Generic OPC client for OPC Data Access
http://www.wipro.com/insights/generic_opc.htm

CodeGuru: Create Provider-Independent Data Access Code with ADO ...
http://www.codeguru.com/Csharp/.NET/net_data/sortinganditerating/article.php/c10487/

Academia.NET
http://pluralsight.com/blogs/drjoe/default.aspx

Generic Data Link Control Environment Overview
http://publib.boulder.ibm.com/infocenter/pseries/topic/com.ibm.aix.doc/aixprggd/progcomc/dlc_ovw.htm

Generic ADO.net Data Access
http://www.wwwcoder.com/main/parentid/226/site/3175/68/default.aspx

Amazon.com: Data Access Patterns: Database Interactions in Object ...
http://www.amazon.com/exec/obidos/tg/detail/-/0131401572?v=glance

Connected Operations in ADO.NET
http://www.informit.com/articles/article.asp?p=26568&seqNum=8

======================================================

Do you get the feeling that you are trying to reinvent the wheel?

Bob

Alexandre Simões

ASKER

Hi...

Many thanks for all those links but I'm still stuck.
The only real solution lies on ADO.net 2.0 nowhere else.

I'm still stuck on the Commands parameters for example, where each provider have their own DBTypes.
If I need a specific SQL Stament (not auto generated by a CommandBuilder) I need to create the a set of parameters for each supported DB provider... Am I wrong?

I'll keep searching... tho OleDB is on the top of the list for my solution. I think that some speed loss is the price to pay for generic access in ADO.net 1.1

Shortly I'll be back... :)

Thanks!

Alex :p

Bob Learned

I like the idea of a factory pattern to create a generic data layer, since it uses interfaces, and the underlying structures are still database-specific.

Bob

MaheshZ

I also think that it is the way to go.......use
The speed you gain could be very important as your application grows. Anyways if there is a problem later on you could replce your existing data layer with a new one that uses oledb.

Alexandre Simões

ASKER

Hi guys...

Thanks for your help here.
I'll stick with my DevExpress XPO data model.

Alex :p