Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Why so many connection providers for database ? Expand / Collapse
Posted Wednesday, January 22, 2014 10:41 AM


Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:51 PM
Points: 183, Visits: 480
I see several connection types for databases - OLEDB, ADO, ADO.NET etc. AFAIK, ADO.NET has a convenient and easy to use API in scripts. Also, if you want to retainSameConnection inside a .NET script, its easier to use a ADO.NET result set rather than OLEDB as mentioned here -

Then why do we have so many types of DB connection managers ? Why not choose the most superior one and have only that ? One reason could be that .NET might not be installed on target machines. If yes, then are there any valid reasons for not installing .NET on target machines ?
Post #1533759
Posted Wednesday, January 22, 2014 1:51 PM



Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 15,510, Visits: 13,170
It depends on how you want to use it I guess. It's about having a choice.
Each type of adaptor has its own strenghts and weaknesses. I've done tests where OLE DB was faster than ADO.NET.
Should I throw away performance because someone else wants to use the connection manager in an API? No, I want to have a choice.

Some components only work with OLE DB (lookup), some work only with ADO.NET (CDC). Some providers are only available in ODBC.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1533827
Posted Thursday, January 23, 2014 8:41 AM


Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, Visits: 487
If I am MS, I would also like to appeal to as many customers as possible

The heart and soul of an ETL tool is the ability to connect to as many sources as possible and load a large amount of data to one target.
Post #1534142
Posted Thursday, January 23, 2014 8:15 PM


Group: General Forum Members
Last Login: Friday, November 4, 2016 1:47 AM
Points: 20, Visits: 173
The connection protocol is often decided by those that wrote the client software
and what they decided to develop their software in:

Roughly, but not exclusive or correct in all cases.

OLEDB - Probably a C or a C++ program
ADO - Visual Basic, MS Access
ADO.NET - Microsoft .NET programming language
ODBC - Some non-Microsoft development package

As was said. MS is just catering for the largest audience possible.
Post #1534322
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse