Why so many connection providers for database ?

  • 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 - http://stackoverflow.com/questions/11867639/connect-to-sql-database-inside-script-task-in-ssis

    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 ?

  • 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.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 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.

  • 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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply