Listing ODBC Data Sources in C#

Question

I'm looking for a properly abstract way to get a list of ODBC data sources from the system in C#. I've tried the "Poking-around-in-the-registry" trick, which I've found works fine in English:

        RegistryKey reg = (Registry.CurrentUser).OpenSubKey("Software");
        reg = reg.OpenSubKey("ODBC");
        reg = reg.OpenSubKey("ODBC.INI");
        reg = reg.OpenSubKey("ODBC Data Sources");

        and then, of course, iterating over reg.GetValueNames()

Only problem is that I've discovered on at least one Spanish machine that their Registry keys are, well, in Spanish, so clearly violating this abstraction (if it exists) has already gotten me into trouble.

Is there a library function to do this?

Answer 1

I don't think there is anything in .NET, and a quick check of the (native) ODBC API shows some functions that might be of help:

• SQLBrowseConnec
• SQLDrivers

Given the way buffers are used in the ODBC API, careful pinning of character arrays will be needed.

Answer 2

You could call the SQLDataSources-function in ODBC32.DLL:

 using System.Runtime.InteropServices;
    public static class OdbcWrapper
    {
        [DllImport("odbc32.dll")]
        public static extern int SQLDataSources(int EnvHandle, int Direction, StringBuilder ServerName, int ServerNameBufferLenIn,
    ref int ServerNameBufferLenOut, StringBuilder Driver, int DriverBufferLenIn, ref int DriverBufferLenOut);

        [DllImport("odbc32.dll")]
        public static extern int SQLAllocEnv(ref int EnvHandle);
    }

Example that lists the Data Sources:

public void ListODBCsources()
    {
        int envHandle=0;
        const int SQL_FETCH_NEXT = 1;
        const int SQL_FETCH_FIRST_SYSTEM = 32;

        if (OdbcWrapper.SQLAllocEnv(ref envHandle) != -1)
        {
            int ret;
            StringBuilder serverName = new StringBuilder(1024);
            StringBuilder driverName = new StringBuilder(1024);
            int snLen = 0;
            int driverLen = 0;
            ret = OdbcWrapper.SQLDataSources(envHandle, SQL_FETCH_FIRST_SYSTEM, serverName, serverName.Capacity, ref snLen,
                        driverName, driverName.Capacity, ref driverLen);
            while (ret == 0)
            {
                System.Windows.Forms.MessageBox.Show(serverName + System.Environment.NewLine + driverName);
                ret = OdbcWrapper.SQLDataSources(envHandle, SQL_FETCH_NEXT, serverName, serverName.Capacity, ref snLen,
                        driverName, driverName.Capacity, ref driverLen);
            } 
        }

    }

The first call to SQLDataSources with SQL_FETCH_FIRST_SYSTEM tells the function to start the listing with the System-DSNs. If you simply started with SQL_FETCH_NEXT it would first list the drivers. Link to the function ref on Microsofts site

Answer 3

I use the following code to retrieve the DSNs from the registry :

    private List<string> EnumDsn()
    {
        List<string> list = new List<string>();
        list.AddRange(EnumDsn(Registry.CurrentUser));
        list.AddRange(EnumDsn(Registry.LocalMachine));
        return list;
    }

    private IEnumerable<string> EnumDsn(RegistryKey rootKey)
    {
        RegistryKey regKey = rootKey.OpenSubKey(@"Software\ODBC\ODBC.INI\ODBC Data Sources");
        if (regKey != null)
        {
            foreach (string name in regKey.GetValueNames())
            {
                string value = regKey.GetValue(name, "").ToString();
                yield return name;
            }
        }
    }

It's strange that you have non-english name for the "ODBC Data Sources" key... I have a French version of Windows, and the name is still in English

Answer 4

If you are using a Windows Forms Application (not a Web environment), you could use the Visual Studio's "Choose Data Source" dialog.

It's included in an assembly and can be easily used.

The article where I found this info: http://www.mztools.com/articles/2007/MZ2007011.aspx

In any case, I'm from Spain and I also use the Registry solution (specially in Web apps). I've never found a machine with those entries in a language different from English.

Espero ser de ayuda...