Enumerating OleDb Providers

PIEBALDconsult

Rate me:

3.67/5 (5 votes)

22 Feb 2018CPOL

9.8K

A simple way to enumerate the OleDb Providers installed on the system

Introduction

This tip presents a simple wrapper around the System.Data.OleDb.OleDbEnumerator.GetRootEnumerator method.

https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbenumerator.getrootenumerator(v=vs.110).aspx

Background

I do a lot of database work with ADO.NET and I access many types of databases, some of which require OleDb, including Access and Excel. Listing the OleDb providers on a system is something I need to do from time to time, particularly when I get a new system. This small class makes accessing the enumeration just a little easier.

The Code

Your application needs only iterate the EnumerateProviders method.

public sealed class OleDbProvider
{   
  /*\   
  |*| https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbenumerator.getrootenumerator
  |*| (v=vs.110).aspx
  |*| 0 SOURCES_NAME
  |*| 1 SOURCES_PARSENAME
  |*| 2 SOURCES_DESCRIPTION
  |*| 3 SOURCES_TYPE  One of the following enumeration members:
  |*|      Binder (0)
  |*|    , DataSource_MDP (1)
  |*|    , DataSource_TDP (2)
  |*|    , Enumerator (3).
  |*|      These correspond to the values returned in the SOURCES_TYPE column 
  |*|      of the native OLE DB sources rowset.
  |*| 4 SOURCES_ISPARENT
  |*| 5 SOURCES_CLSID
  \*/
          
  public enum ProviderType
  { Binder         = 0
  , DataSource_MDP = 1
  , DataSource_TDP = 2
  , Enumerator     = 3
  } ;

  public string       Name        { get ; private set ; }
  public System.Guid  ParseName   { get ; private set ; }
  public string       Description { get ; private set ; }
  public ProviderType Type        { get ; private set ; }
  public bool         IsParent    { get ; private set ; }
  public System.Guid  ClsID       { get ; private set ; }

  private OleDbProvider   
  (
    System.Data.IDataRecord Record
  )
  {         
    this.Name        =                     (string)       Record [ "SOURCES_NAME"        ] ;
    this.ParseName   = System.Guid.Parse ( (string)       Record [ "SOURCES_PARSENAME"   ] ) ;
    this.Type        =                     (ProviderType) Record [ "SOURCES_TYPE"        ] ;
    this.Description =                     (string)       Record [ "SOURCES_DESCRIPTION" ] ;
    this.IsParent    =                     (bool)         Record [ "SOURCES_ISPARENT"    ] ;
    this.ClsID       = System.Guid.Parse ( (string)       Record [ "SOURCES_CLSID"       ] ) ;
    
    return ;
  }
  
  public static System.Collections.Generic.IEnumerable<OleDbProvider>
  EnumerateProviders
  (
  )
  {
    using
    (
      System.Data.IDataReader dr
    =
      System.Data.OleDb.OleDbEnumerator.GetRootEnumerator()
    )
    {
      while ( dr.Read() )
      {
        yield return ( new OleDbProvider ( dr ) ) ;
      }
    }

    yield break ;
  }
}

History

2018-02-22 First version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

PIEBALDconsult

Software Developer (Senior)

United States

BSCS 1992 Wentworth Institute of Technology

Originally from the Boston (MA) area. Lived in SoCal for a while. Now in the Phoenix (AZ) area.

OpenVMS enthusiast, ISO 8601 evangelist, photographer, opinionated SOB, acknowledged pedant and contrarian

---------------

"I would be looking for better tekkies, too. Yours are broken." -- Paul Pedant

"Using fewer technologies is better than using more." -- Rico Mariani

"Good code is its own best documentation. As you’re about to add a comment, ask yourself, ‘How can I improve the code so that this comment isn’t needed?’" -- Steve McConnell

"Every time you write a comment, you should grimace and feel the failure of your ability of expression." -- Unknown

"If you need help knowing what to think, let me know and I'll tell you." -- Jeffrey Snover [MSFT]

"Typing is no substitute for thinking." -- R.W. Hamming

"I find it appalling that you can become a programmer with less training than it takes to become a plumber." -- Bjarne Stroustrup

ZagNut’s Law: Arrogance is inversely proportional to ability.

"Well blow me sideways with a plastic marionette. I've just learned something new - and if I could award you a 100 for that post I would. Way to go you keyboard lovegod you." -- Pete O'Hanlon

"linq'ish" sounds like "inept" in German -- Andreas Gieriet

"Things would be different if I ran the zoo." -- Dr. Seuss

"Wrong is evil, and it must be defeated." –- Jeff Ello

"A good designer must rely on experience, on precise, logical thinking, and on pedantic exactness." -- Nigel Shaw

“It’s always easier to do it the hard way.” -- Blackhart

“If Unix wasn’t so bad that you can’t give it away, Bill Gates would never have succeeded in selling Windows.” -- Blackhart

"Use vertical and horizontal whitespace generously. Generally, all binary operators except '.' and '->' should be separated from their operands by blanks."

"Omit needless local variables." -- Strunk... had he taught programming

function Get-OleDBRecordList { <# .SYNOPSIS Enumerate OLEDB providers on this machine .DESCRIPTION Find out which OLEDB Providers are installed on this computer .EXAMPLE $ProviderList = Get-OleDBRecordList -Verbose $ProviderList | Select-Object -Property name, description | Sort-Object name -unique | Format-Table name, description -AutoSize .EXAMPLE # From Powershell 64bit Get-OleDBRecordList | Where-Object { $_.Description -match 'Access' } Name : Microsoft.ACE.OLEDB.12.0 ParseName : 3be786a0-0366-4f5c-9434-25cf162e475e ClsID : 3be786a0-0366-4f5c-9434-25cf162e475e Description : Microsoft Office 12.0 Access Database Engine OLE DB Provider IsParent : False Type : DataSource_MDP .EXAMPLE # From Powershell 32bit Get-OleDBRecordList | Where-Object { $_.Description -match 'Access' } Name : Microsoft.ACE.OLEDB.12.0 ParseName : 3be786a0-0366-4f5c-9434-25cf162e475e ClsID : 3be786a0-0366-4f5c-9434-25cf162e475e Description : Microsoft Office 12.0 Access Database Engine OLE DB Provider IsParent : False Type : DataSource_MDP Name : Microsoft.ACE.OLEDB.15.0 ParseName : 3be786a1-0366-4f5c-9434-25cf162e475e ClsID : 3be786a1-0366-4f5c-9434-25cf162e475e Description : Microsoft Office 15.0 Access Database Engine OLE DB Provider IsParent : False Type : DataSource_MDP .NOTE Translated https://www.codeproject.com/Tips/1231609/Enumerating-OleDb-Providers to Powershell #> [CmdletBinding()]param() foreach ($DataRecord in [System.Data.OleDb.OleDbEnumerator]::GetRootEnumerator()) { $ProviderType = switch($DataRecord["SOURCES_TYPE"]) { 0 { "Binder" } 1 { "DataSource_MDP" } 2 { "DataSource_TDP" } 3 { "Enumerator" } default { "UNKNOWN" } } $SourceName = [string]$DataRecord["SOURCES_NAME"] $SourceDescription = [string]$DataRecord["SOURCES_DESCRIPTION"] $ProviderInformation = New-Object psobject -Property @{ Name = $SourceName ParseName = [System.Guid]::Parse( [string]$DataRecord["SOURCES_PARSENAME"] ) Type = $ProviderType Description = $SourceDescription IsParent = [bool]$DataRecord["SOURCES_ISPARENT"] ClsID = [System.Guid]::Parse( [string]$DataRecord["SOURCES_CLSID"] ) } Write-Verbose "Found $SourceName - $SourceDescription ($ProviderType)" $ProviderInformation } }

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.