Listing Installed OLE DB Providers

[ Team LiB ] Recipe 10.13 Listing Installed OLE DB Providers Problem You need a list of the OLE DB providers installed on the machine running your code. Solution Use a SQL Server extended stored procedure or search the registry.
Nội dung trích xuất từ tài liệu:
Listing Installed OLE DB Providers [ Team LiB ]Recipe 10.13 Listing Installed OLE DB ProvidersProblemYou need a list of the OLE DB providers installed on the machine running your code.SolutionUse a SQL Server extended stored procedure or search the registry.In the first case, the sample code executes the extended stored procedurexp_enum_oledb_providers. The result set containing the installed OLE DB providers isdisplayed.In the second case, the sample code uses the Microsoft.Win32.Registry class to examinethe registry, identify OLE DB provider subkeys, and retrieve and display the OLE DBprovider names from these subkeys.The C# code is shown in Example 10-13.Example 10-13. File: OleDbProvidersForm.cs// Namespaces, variables, and constantsusing System;using System.Configuration;using System.Text;using Microsoft.Win32;using System.Data;using System.Data.SqlClient;// . . .// SQL Server extended stored procedureStringBuilder result = new StringBuilder(Using SQL Server xp_enum_oledb_providers. + Environment.NewLine);int count = 0;SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings[Sql_Master_ConnectString]);// Create a command to execute the extended stored procedure to// retrieve OLE DB providers.SqlCommand cmd = new SqlCommand(xp_enum_oledb_providers, conn);cmd.CommandType = CommandType.StoredProcedure;// Create the DataReader.conn.Open( );SqlDataReader rdr = cmd.ExecuteReader( );// Iterate through the OLE DB providers in the DataReader.while(rdr.Read( )){ result.Append(++count + : + rdr[Provider Description].ToString( ) + Environment.NewLine);}conn.Close( );resultTextBox.Text = result.ToString( );// Registry ScanStringBuilder result = new StringBuilder(Using Registry scan. + Environment.NewLine);int count = 0;// Get the HKEY_CLASSES_ROOT/CLSID key.RegistryKey keyCLSID = Registry.ClassesRoot.OpenSubKey(CLSID, false);// Iterate through the collection of subkeys.String[] keys = keyCLSID.GetSubKeyNames( );for(int i = 0; i < keys.Length; i++){ // Look for the OLE DB Provider subkey and retrieve the value if found. RegistryKey key = keyCLSID.OpenSubKey(keys[i], false); RegistryKey subKey = key.OpenSubKey(OLE DB Provider, false); if(subKey != null) { result.Append(++count + : + subKey.GetValue(subKey.GetValueNames( )[0]) + Environment.NewLine); }}resultTextBox.Text = result.ToString( );DiscussionThe solution shows two ways to get a list of OLE DB providers installed on a computer.The first technique uses an extended stored procedure xp_enum_oledb_providersavailable in SQL Server 7.0, or later. Executing the stored procedure against the masterdatabase returns a result set of all OLE DB providers installed on the SQL Server. Theresult set contains the information described in Table 10-8. Table 10-8. xp_enum_oledb_providers result set Column Name DescriptionProvider Name Default value of the class ID (CLSID) keyParse Name Class ID (CLSID)Provider Description Name of the OLE DB providerThe SQL Server extended stored procedure xp_enum_oledb_providers does not list allinstalled OLE DB providers. Providers such as MSDataShape are excluded because theydo not work as linked servers. Other providers, such as Microsoft Jet 3.51 OLE DB, areexcluded because a later version of the provider is installed, for example Microsoft Jet4.0 OLE DB.The second technique uses a registry scan and is necessary if SQL Server 7.0, or later, isnot installed on the computer, although it can be used with later versions as well.The .NET Framework classes that manipulate the registry are found in theMicrosoft.Win32 namespace. The class IDs that represent OLE DB providers can beidentified by the presence of a subkey OLE DB Provider in a class ID. So, to enumeratethe OLE DB providers on a computer, iterate over all of the subkeys of theHKEY_CLASSES_ROOT\CLSID key and check for the presence of the OLE DBProvider subkey. The provider name returned by the SQL Server extended storedprocedure is the default value for the ProgID subkey while the OLE DB provider name isthe default value for the OLE DB Provider subkey.[ Team LiB ]