C# Signature:

/// <summary>
/// SQLDataSources returns information about a data source. This function is implemented solely by the Driver Manager.
/// </summary>
/// <param name="EnvironmentHandle">[Input] Environment handle.</param>
/// <param name="Direction">[Input] Determines which data source the Driver Manager returns information on.</param>
/// <param name="ServerName">[Output] Pointer to a buffer in which to return the data source name.</param>
/// <param name="BufferLength1">[Input] Length of the *ServerName buffer, in characters; this does not need to be longer than SQL_MAX_DSN_LENGTH plus the null-termination character.</param>
/// <param name="NameLength1Ptr">[Output] Pointer to a buffer in which to return the total number of bytes (excluding the null-termination byte)
/// available to return in *ServerName. If the number of bytes available to return is greater than or equal to BufferLength1, the data
/// source name in *ServerName is truncated to BufferLength1 minus the length of a null-termination character. </param>
/// <param name="Description">[Output] Pointer to a buffer in which to return the description of the driver associated with the data source.
/// For example, dBASE or SQL Server.</param>
/// <param name="BufferLength2">[Input] Length in characters of the *Description buffer.</param>
/// <param name="NameLength2Ptr">[Output] Pointer to a buffer in which to return the total number of bytes (excluding the null-termination
/// byte) available to return in *Description. If the number of bytes available to return is greater than or equal to BufferLength2,
/// the driver description in *Description is truncated to BufferLength2 minus the length of a null-termination character.</param>
/// <returns>SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA, SQL_ERROR, or SQL_INVALID_HANDLE.</returns>
[DllImport("odbc32.dll", CharSet=CharSet.Ansi))]
static extern short SQLDataSources(IntPtr EnvironmentHandle, short Direction,
    StringBuilder ServerName, short BufferLength1, ref short NameLength1Ptr,
    StringBuilder Description, short BufferLength2, ref short NameLength2Ptr);

VB Signature:

Declare Function SQLDataSources Lib "odbc32.dll" (ByVal EnvironmentHandle As Integer, ByVal Direction As Short, _
    ByVal ServerName As String, ByVal BufferLength1 As Short, ByRef NameLength1Ptr As Short, _
    ByVal Description As String, ByVal BufferLength2 As Short, ByRef NameLength2Ptr As Short) As Short

User-Defined Types:

None.

Notes:

MSDN Documentation was sourced for the precise parameter definitions and return values.

You must provide an Environment Handle provided by SQLAllocHandle as the first parameter. SQLAllocHandle is a replacement for SQLAllocEnv. When using SQLAllocHandle, you must call SQLSetEnvAttr and set the SQL_ATTR_ODBC_VERSION attribute to SQL_OV_ODBC3 before calling SQLDataSources. You must release Environment handle with SQLFreeHandle at the end.

Tips & Tricks:

Depending on the value of Direction, you may access System DSN, User DSN or both. Since the same DSN name may be used in User and System DSN, watch out for possible duplicate values.

Sample Code:

One use of this function is to populate a combobox with available DSN's in a login screen...

Here is a simple function to obtain the Data Source Names installed on the system (this functionality is not available in ODBC.net):

public const ushort SQL_HANDLE_ENV = 1;
public const short SQL_SUCCESS = 0;
public const short SQL_SUCCESS_WITH_INFO = 1;
public const short SQL_NO_DATA = 100;
public const int SQL_ATTR_ODBC_VERSION = 200;
public const int SQL_OV_ODBC3 = 3;
public const short SQL_FETCH_NEXT = 1;
public const short SQL_FETCH_FIRST = 2;
public const int MAX_DSN_LENGTH = 32;

/// <summary>
/// Obtains all the ODBC DSNs installed on the system and returns them in a List<string> object.
/// </summary>
public static List<string> GetOdbcDataSources()
{
    IntPtr sql_env_handle = IntPtr.Zero;
    short rc = 0;
    StringBuilder dsn_name = new StringBuilder(MAX_DSN_LENGTH);
    StringBuilder desc_name = new StringBuilder(128);
    short dsn_name_len = 0,desc_len = 0;
    List<String> rv = new List<String>();

    try
    {
    rc = SQLAllocHandle(SQL_HANDLE_ENV, 0, out sql_env_handle);
    if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO))
        throw new Exception("Could not allocate ODBC Environment handle");
    rc = SQLSetEnvAttr(sql_env_handle, SQL_ATTR_ODBC_VERSION, (IntPtr)SQL_OV_ODBC3, 0);
    if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO))
        throw new Exception("Could not setup ODBC Environment handle");
    rc = SQLDataSources(sql_env_handle, SQL_FETCH_FIRST, dsn_name, (short)dsn_name.Capacity, ref dsn_name_len, desc_name, (short) desc_name.Capacity, ref desc_len);
    if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO) && (rc != SQL_NO_DATA))
        throw new Exception("Error getting ODBC Data Sources");
    while ((rc == SQL_SUCCESS) || (rc == SQL_SUCCESS_WITH_INFO))
    {
        rv.Add(dsn_name.ToString());
        rc = SQLDataSources(sql_env_handle, SQL_FETCH_NEXT, dsn_name, (short)dsn_name.Capacity, ref dsn_name_len, desc_name, (short)desc_name.Capacity, ref desc_len);
        if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO) && (rc != SQL_NO_DATA))
        throw new Exception("Error getting ODBC Data Sources");
    }
    }
    finally
    {
    if (sql_env_handle != IntPtr.Zero)
    {
        rc = SQLFreeHandle(SQL_HANDLE_ENV, sql_env_handle);
        if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO))
        throw new Exception("Could not free ODBC Environment handle");
    }
    }
    return rv;
}

VB.Net example

Populate a combobox control with a list of DSNs, uses SQLAllocEnv and SQLFreeEnv

    Private Sub OnGetODBCConnectionNames(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbExpSID.DropDown, cmbImpSid.DropDown
    Dim c As ComboBox = sender

    Dim hEnv As IntPtr
    c.Items.Clear()

    Try
        SQLAllocEnv(hEnv)

        If (hEnv) Then
        Dim iNameLen As Short = 0
        Dim iNameLen2 As Short = 0
        Dim nResult As Short = 0
        Dim cDSNBuf(64) As Char
        Dim cDescBuf(64) As Char
        Dim sDSN As String = New String(cDSNBuf)
        Dim sDesc As String = New String(cDescBuf)

        Dim Dir As SQLVals = SQLVals.SQL_FETCH_NEXT

        Dim i As Integer = 0
        nResult = SQLDataSources(hEnv, Dir, sDSN, 64, iNameLen, sDesc, 64, iNameLen2)
        While ((nResult <> SQLVals.SQL_NO_DATA_FOUND) And (nResult <> SQLVals.SQL_ERROR))

            c.Items.Add(Trim(sDSN))
            nResult = SQLDataSources(hEnv, Dir, sDSN, 64, iNameLen, sDesc, 64, iNameLen2)
            i = i + 1
        End While

        End If

    Finally
        If (hEnv) Then
        SQLFreeEnv(hEnv)
        End If
    End Try

    End Sub

Alternative Managed API:

Do you know one? Please contribute it!

This VB.Net code loads a combo box with only the SQL Server related System DSNs.

On XP, Win2k and Win2k3, the system DSN's are stored in the registry in

HKey_Machine\Software\ODBC\ODBC.INI\ODBC Data Sources

        Dim strKeyNames() As String
        Dim intKeyValues As Integer
        Dim intCount As Integer
        Dim key As Microsoft.Win32.RegistryKey

        key = Microsoft.Win32.Registry.LocalMachine.OpenSubKey("software\odbc\odbc.ini\odbc data sources")
        strKeyNames = key.GetValueNames() 'Get an array of the value names
        intKeyValues = key.ValueCount() 'Get the number of values
        For intCount = 0 To intKeyValues - 1
        If key.GetValue(strKeyNames(intCount)) = "SQL Server" Then 'only add DSNs that are for SQL Server
            cboDSN.Items.Add(strKeyNames(intCount))
        End If
        Next

Please edit this page!

Do you have...

• helpful tips or sample code to share for using this API in managed code?
• corrections to the existing content?
• variations of the signature you want to share?
• additional languages you want to include?

Select "Edit This Page" on the right hand toolbar and edit it! Or add new pages containing supporting types needed for this API (structures, delegates, and more).