Alexandre Simões
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
==========================
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
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
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
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
Bob
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.
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.
ASKER
Hi guys...
Thanks for your help here.
I'll stick with my DevExpress XPO data model.
Alex :p
Thanks for your help here.
I'll stick with my DevExpress XPO data model.
Alex :p
ASKER
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