C# IsODBCServerAvailable

Question

Hi there,

I want to implement a "smartODBCLogin". I know how to get a list of all engineNames and drivers from all ODBC data sources out of the registry.

Now I want to know, which of these sources are available (active). (To open a new odbcConnection wouldn't work/help, would take to much time.)

Does anybody know how to implement this in c#?

Answer 1

There is no .NET API for this (that I know of), but there is a native ODBC API for this, detailed on the Microsoft site here. There is also a nice code sample that might help you over here that lists User and System DSNs. There is also a code sample at CodeProject that looks like it gets the driver list.

If you're in a hurry, here is the first article samples (to get user and system DSNs) in all their plagiarized glory:

Get system DSNs:

/// <summary>
/// Gets all System data source names for the local machine.
/// </summary>
public System.Collections.SortedList GetSystemDataSourceNames()
{
    System.Collections.SortedList dsnList = new System.Collections.SortedList();

    // get system dsn's
    Microsoft.Win32.RegistryKey reg = (Microsoft.Win32.Registry.LocalMachine).OpenSubKey("Software");
    if (reg != null)
    {
        reg = reg.OpenSubKey("ODBC");
        if (reg != null)
        {
            reg = reg.OpenSubKey("ODBC.INI");
            if (reg != null)
            {
                reg = reg.OpenSubKey("ODBC Data Sources");
                if (reg != null)
                {
                    // Get all DSN entries defined in DSN_LOC_IN_REGISTRY.
                    foreach (string sName in reg.GetValueNames())
                    {
                        dsnList.Add(sName, DataSourceType.System);
                    }
                }
                try
                {
                    reg.Close();
                }
                catch { /* ignore this exception if we couldn't close */ }
            }
        }
    }

    return dsnList;
}

Get User DSN's:

/// <summary>
/// Gets all User data source names for the local machine.
/// </summary>
public System.Collections.SortedList GetUserDataSourceNames()
{
    System.Collections.SortedList dsnList = new System.Collections.SortedList();

    // get user dsn's
    Microsoft.Win32.RegistryKey reg = (Microsoft.Win32.Registry.CurrentUser).OpenSubKey("Software");
    if (reg != null)
    {
        reg = reg.OpenSubKey("ODBC");
        if (reg != null)
        {
            reg = reg.OpenSubKey("ODBC.INI");
            if (reg != null)
            {
                reg = reg.OpenSubKey("ODBC Data Sources");
                if (reg != null)
                {
                    // Get all DSN entries defined in DSN_LOC_IN_REGISTRY.
                    foreach (string sName in reg.GetValueNames())
                    {
                        dsnList.Add(sName, DataSourceType.User);
                    }
                }
                try
                {
                    reg.Close();
                }
                catch { /* ignore this exception if we couldn't close */ }
            }
        }
    }

    return dsnList;
}

Get all DSN's:

// Returns a list of data source names from the local machine.
public System.Collections.SortedList GetAllDataSourceNames()
{
    // Get the list of user DSN's first.
    System.Collections.SortedList dsnList = GetUserDataSourceNames();

    // Get list of System DSN's and add them to the first list.
    System.Collections.SortedList systemDsnList = GetSystemDataSourceNames();
    for (int i = 0; i < systemDsnList.Count; i++)
    {
        string sName = systemDsnList.GetKey(i) as string;
        DataSourceType type = (DataSourceType)systemDsnList.GetByIndex(i);
        try
        {
            // This dsn to the master list
            dsnList.Add(sName, type);
        }
        catch 
        { 
            // An exception can be thrown if the key being added is a duplicate so 
            // we just catch it here and have to ignore it.
        }
    }

    return dsnList;
}

Bind them to a combo box:

// fill data source names
DevToolShed.OdbcDataSourceManager dsnManager = new DevToolShed.OdbcDataSourceManager();
System.Collections.SortedList dsnList = dsnManager.GetAllDataSourceNames();
for (int i = 0; i < dsnList.Count; i++)
{
    string sName = (string)dsnList.GetKey(i);
    DevToolShed.DataSourceType type = (DevToolShed.DataSourceType)dsnList.GetByIndex(i);
    cbxDataSourceName.Items.Add(sName + " - (" + type.ToString() + " DSN)");
}

Full source code is available at the link, above.

Answer 2

Hi,

thanks for your answer!

With your code I get a full list of all DSNs. I also tried to implement the SQLDataSources function (All DSNs returned SQL_SUCCESS). Both lists seams to be equal.

But:

Both lists include unavailable services. How do i get just the DSNs of the active servers?

Example: ODBC Data Sources:

• DS_A_local

• DS_B_local

• DS_C_intranet

• DS_D_intranet

I didn't start the local service B yet. So the available DSNs are:

• DS_A_local

• DS_C_intranet

• DS_D_intranet

In my list I want these three items only. With both existing implementations I always get four items. :-(

Is there another way to get my three "available" DSNs only?