none
Can I determine the database backend type using OleDB? RRS feed

  • Question

  • I have an application that the user provides the connection string. I'm using OleDB connections. Can I tell which backend database, Sql Server, Oracle, Access, for instance, is begin by making some method call or looking at some property?

    Without that, I will scan the providername and look for Sql, or Oracle, or ACE.  Is there a better way?


    • Edited by Mark Tiede Monday, October 17, 2016 7:26 PM
    Monday, October 17, 2016 7:25 PM

All replies

  • Hi Mark Tiede,

    >> I will scan the providername and look for Sql, or Oracle, or ACE.  Is there a better way?

    From my experience. if we use SQL Server, we could use .Net Framework Data Provider for SQL Server, if we use Oracle, we could use ODP.Net, If we use Excel, Access Database, we could use OLEDB(ACE).

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, October 18, 2016 2:34 AM
    Moderator
  • Please read my question.

    Tuesday, October 18, 2016 12:05 PM
  • If I understand your question, you want to determine the database from an OLEDB connection string. If you create an OleDbConnection and assign the connection string you can get the provider name from the Provider property. This won't tell you want type of database it is for so you would still need to define that somewhere, perhaps an XML file mapping the providers to a database system. The site connectionstrings.com would be a good place to start if you go this route.

            Dim OleDbConnection As New System.Data.OleDb.OleDbConnection("Provider=MSDAORA;" & _
                                                "Data Source=DataSourceName;" & _
                                                "USER ID=Scott;PASSWORD=Tiger;")
    
            Console.WriteLine(OleDbConnection.Provider)


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, October 18, 2016 12:44 PM
  • So it sounds like there is no better way than what I proposed which was to scan the provider. Is does point out that it might not have the word "oracle" in the name and I should just look for "ora" to determine if it is Oracle.

    I was hoping there was some obscure property that the driver could be queried for to find the real backend database instead of depending on what name the provider has been given.

    I don't want to have some xml thing that the customer has to configure, that is why I was trying to automate it by looking at the provider or some other property somewhere.

    Thanks for the reply.

    Tuesday, October 18, 2016 2:28 PM
  • So it sounds like there is no better way than what I proposed which was to scan the provider. Is does point out that it might not have the word "oracle" in the name and I should just look for "ora" to determine if it is Oracle.

    I was hoping there was some obscure property that the driver could be queried for to find the real backend database instead of depending on what name the provider has been given.

    I don't want to have some xml thing that the customer has to configure, that is why I was trying to automate it by looking at the provider or some other property somewhere.

    Thanks for the reply.


    Unfortunately not. There would have to be something in the connection string that clearly identifies the database and that simply has never been the case. You would either need to hard-code such a feature or include it within a configuration file.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, October 18, 2016 4:31 PM