Need a simple CRM and Project Management system?
Check out JobNimbus - CRM for Contractors and Service Professionals.

Get the list of ODBC data source names programatically using C#

Many times, you may need to get the list of currently set up ODBC data source names on the machine where your code is running from. There is no .NET API to get this list so you have to monkey with Windows registry a bit to find the list. There are 2 different types of ODBC data sources you can set up (User and System). The Data Source Names can be found in Windows by going to Administrative Tools->Data Sources(ODBC). The dialog looks like this: Data Sources (ODBC) To get this list of data source names programmatically, I've created a simple class that you can use to get all System DSN's, User DSN's, or all DSN's. The list is returned already sorted so that it can be put directly into a drop down list easily and displayed to the user as needed. Getting System DSN's Here is the method that returns System DSN's:

/// <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;
}

Getting User DSN's Here is the method that returns 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;
}

Getting all DSN's Now we can call the 2 previous methods and combine their results to get a full list of 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;
}

To use these methods, you can bind them to a Combo Box with code similar to this:

// 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)");
}

The entire class for OdbcDataSourceManager can be downloaded here: OdbcDataSourceManager.zip

Get DSn

hii, really work for me thank you very much, but how get type of database using dsn mean its sql server,ms access or any database if know pls reply

Call me a novice 2

I don't see a sample on how to connect once you have the data source. Also how would you get the datasource if the ODBC is a 32 bit in c:\windows\syswow64.

Thanks

How to retrieve ODBC names only for a particular driver

Hi, from the 'ODBC Data Sources' registry,
how can i read only those names that belong to particular driver (Data column)?
For example i want to get ODBC Data Sources for only 'Microsoft Excel Driver'

I wish there was a function to return a list of subkeys instead of GetSubKeyNames or GetValueNames

Thanks,
-srinivas y.

Call me a novice but

Once I use this and have the data sources how do I use it to connect to once. What's the syntax for created a connection?

thank a lot

thank a lot, it works...

Windows 7 64 bit Mysql driver

I am only getting "Xtreme Sample Database 2008 - (System DSN)" for system connections. Works fine in my windows XP 32 bit. I tried installing the 32 bit mysql driver but that did not work. I looked in my registry and the entry is there for my test connection but it does not show in the combo box.

Awesome!

Thank you x 1,000,000.

Excellent stuff !

Thank you very much. Just what I was looking for..

VB.Net Version of Code??

Does anyone have the VB.net version of this code. It would really be a lifesaver. Thanks in advance!

vb.net code for the above c# code

Partial Public Class ListingUserDSNsByRegistryAccessing
Inherits Form
Public Sub New()
InitializeComponent()
End Sub
Public Enum DataSourceType
System
User
End Enum
Private Sub ListingUserDSNsByRegistryAccessing_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim SL As SortedList = GetUserDataSourceNames()
Dim mKeys As [String]() = New String(SL.Count - 1) {}
SL.Keys.CopyTo(mKeys, 0)
For i As Integer = 0 To mKeys.Length - 1
comboBox1.Items.Add(mKeys(i))
Next
End Sub
'''


''' Gets all User data source names for the local machine.
'''

Public Function GetUserDataSourceNames() As System.Collections.SortedList
Dim dsnList As New System.Collections.SortedList()

' get user dsn's
Dim reg As Microsoft.Win32.RegistryKey = (Microsoft.Win32.Registry.CurrentUser).OpenSubKey("Software")
If reg IsNot Nothing Then
reg = reg.OpenSubKey("ODBC")
If reg IsNot Nothing Then
reg = reg.OpenSubKey("ODBC.INI")
If reg IsNot Nothing Then
reg = reg.OpenSubKey("ODBC Data Sources")
If reg IsNot Nothing Then
' Get all DSN entries defined in DSN_LOC_IN_REGISTRY.
For Each sName As String In reg.GetValueNames()
dsnList.Add(sName, DataSourceType.User)
Next
End If
Try
reg.Close()
' ignore this exception if we couldn't close
Catch
End Try
End If
End If
End If

Return dsnList
End Function

End Class

for the system dns

u can use

' get system dsn's
Dim reg As Microsoft.Win32.RegistryKey = (Microsoft.Win32.Registry.LocalMachine).OpenSubKey("SOFTWARE")

use this tool

Very good tool for code conversion -- from C# to vb.net and vice versa.
http://www.developerfusion.com/tools/convert/csharp-to-vb/

I LOVE YOU!

Thank you so much for this code, really saved me some time!

Thanks

good example, straightforward example

Thanks

This code was of great help

thx

that is cool codes. i'll try it. thank you.....

tnx

a great help. i converted it in vb.net and modified some of it making the class view ODBC for all sql servers only

VB.Net version???

Does anyone have vb.net version of this code? It would sure be a lifesaver..

Fantastic

Thanks for this clear and easy example, can I use it in my application?

The list of valid C-Types

The list of valid C-Types defined in the ODBC specification is the same for all ODBC drivers exams preparation. Typically, data store manufacturers create new data types for new scenarios or new customer needs 000-330 exam. Applications usually use the generic C-type SQL_C_BINARY to work with these new data-source specific types 000-331 exam. ODBC 3.80 allows driver manufacturers to define their own C-Types. This means that a driver can define its own client-side type conversion rule for its new driver-specific data type, and thus provide a better developer experience.