I don’t really know why, but every now and then, some programmers are asking for a way to create/edit/delete ODBC entries to/from the registry.

I personally haven’t used ODBC entries for a long time but I understand that it might still be useful in some circumstances.

This month downloadable demo

I have made the extra effort to convert this month demo application to C#. All the code shown in this page is VB but C# the zip file contains both VB and C#.

Create the application

To be able to test this month code, we first need to create a simple Windows application that looks like this. It is made of some labels, textboxes, 2 radio buttons, a combo box and 3 buttons.

Figure 1: The demo application in action

Once you have created the UI, you need those declarations to import some functions from Windows DLLs because the calls to manage ODBC entries are not native to .Net:

Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" ( _
                      ByVal hwndParent As Integer, _
                      ByVal Request As Integer, _
                      ByVal lpszDriver As String, _
                      ByVal lpszAttributes As String) As Integer

'The API understands these values:
Private Enum enuOperation As Integer
    ODBC_ADD_DSN = 1        'Add User DSN
    ODBC_CONFIG_DSN = 2     'Edit System DSN
    ODBC_REMOVE_DSN = 3     'Delete User DSN
    ODBC_ADD_SYS_DSN = 4    'Add System DSN
    ODBC_CONFIG_SYS_DSN = 5 'Edit System DSN
    ODBC_REMOVE_SYS_DSN = 6 'Remove System DSN
End Enum

Now that we have the main declarations, we can create a method that will wrap all the calls (add/edit/delete) into a single method because the main difference between all the calls is the second parameter (called Request here). This wrapper method as the following code:

Private Function ManageDSN(ByVal pOperation As enuOperation, _
                           ByVal pDSNName As String, _
                           ByVal pServerPath As String, _
                           ByVal pDefaultDB As String, _
                           ByVal pDescription As String, _
                           ByVal pDriverName As String) As Boolean
    Dim intResult As Integer
    Dim strAttr As String

    If pDriverName = "SQL Server" Then
        strAttr = "DSN=" + pDSNName + Convert.ToChar(0) & _
                      "SERVER=" + pServerPath & Convert.ToChar(0) & _
                      "DATABASE=" + pDefaultDB + Convert.ToChar(0) & _
                      "TRUSTED_CONNECTION=True" + Convert.ToChar(0) & _
                      "DESCRIPTION=" + pDescription + Convert.ToChar(0)
    Else
        strAttr = "DSN=" + pDSNName + Convert.ToChar(0) & _
                  "DBQ=" + pServerPath + Convert.ToChar(0) & _
                  "DESCRIPTION=" + pDescription + Convert.ToChar(0)
    End If

    ' create/edit/delete DSN
    intResult = SQLConfigDataSource(0, pOperation, pDriverName, strAttr)
    Return (intResult = 1)
End Function

This demo application supports 2 providers (SQL Server and Microsoft Access). Depending on your provider, the string of attributes (that fills the strAttr variable) varies but the method always remains the same.

Creating ODBC entries

Now that we have the main parts in place, all that’s left is to call that wrapper method.

The code you will find to handle the Click event of the Create button is the following:

Dim intOperation As enuOperation

' User-DSN or System-DSN?
If optDSNSystem.Checked Then
    intOperation = enuOperation.ODBC_ADD_SYS_DSN
Else
    intOperation = enuOperation.ODBC_ADD_DSN
End If

If ManageDSN(intOperation, _
             txtName.Text, _
             txtServer.Text, _
             txtDefaultDB.Text, _
             txtDescription.Text, _
             cboDriver.Text) Then
    MessageBox.Show("DSN added successfully")
Else
    MessageBox.Show("DSN NOT added successfully")
End If

The first thing it does is to detect if the user wants to create a user DSN or a system DSN (might prevented depending on user’s privileges).

Then we call the wrapper method passing all the controls’ value to the method.

Finally, according to the result returned by the wrapper method, we display a message for success or failure.

Editing ODBC entries

I don’t know why this option exists! While I was testing, I found that using the ADD operation overwrites any existing values. So I don’t know why we should lose time using it. We better always use the code to add an entry. But don’t try to edit an entry that does not exist, that won’t add it.

Anyway, if you really want to modify an existing entry, the only thing that is different from the add button is the 2 constants used to determine the operation:

If optDSNSystem.Checked Then
    intOperation = enuOperation.ODBC_CONFIG_SYS_DSN
Else
    intOperation = enuOperation.ODBC_CONFIG_DSN
End If

I have also changed the message at the end of the method to show a message related to the operation that was just done (edited instead of added).

Deleting ODBC entries

Deleting is not really different.

You set the correct operation and you don’t even have to provide all the values we sent before (that’s why I send the String.Empty values 3 times.

This is the code I have in the Click event handler of the Delete button:

Dim intOperation As enuOperation

' User-DSN or System-DSN?
If optDSNSystem.Checked Then
    intOperation = enuOperation.ODBC_REMOVE_SYS_DSN
Else
    intOperation = enuOperation.ODBC_REMOVE_DSN
End If

If ManageDSN(intOperation, _
             txtName.Text, _
             String.Empty, _
             String.Empty, _
             String.Empty, _
             cboDriver.Text) Then
    MessageBox.Show("DSN removed successfully")
Else
    MessageBox.Show("DSN NOT removed successfully")
End If

Where are those entries saved?

If you open the registry editor (RegEdit.exe), you will be able to see the entries you just created. Do I have to warn you to be extra cautious when opening the registry? You can seriously damage your Windows system if you edit/delete just about anything.

I really recommend that you use User DSN.

Figure 2: The registry opened on the new entry

As you can see in this image, the path to the user’ DSN entry is HKEY_Current_User\Software\ODBC\ODBC.INI. In that folder, you will find the value you have set in the Name textbox of the UI.

Conclusion

It is very easy to configure ODBC entries in the registry once you know which method to call. If it is justified to use this method, use it. If you can connect more directly use this latest method.

Also remember that depending on the user’s privileges, the user might be prevented from creating those settings.

I hope you appreciated the topic and see you next month.