Dynamically adding DSN-names

• Download source code - 0.8 Kb

Introduction

First, let me start by saying that normally I use a OLEDB-connection to my database. But after coming across a bug in the .NET Framework (on a European, in this case Dutch, Windows system, the decimal point is lost due to the different symbol), I had no other choice than to use ODBC. Now there is no problem with ODBC, it works fine. Except,... those damn DSN-names. Not wanting to add the DSN-names myself, I surfed the Net and found... well, virtually nothing. After finding some hints in a Microsoft forum, I decided to write this article on using an API-call to dynamically create DSN-names.

Building the class

Import API

In order to import the ODBCCP32.dll which contains our API-call, we have to include the InteropServices namespace.

using System.Runtime.InteropServices;

Now it is time to import the DLL and create our property:

[
DLLImport("ODBCCP32.dll")
]
private static extern bool SQLConfigDataSource(
    IntPtr parent, int request, string driver, string attributes);

The parent parameter refers to the parent window handle and can be set to 0. The request parameter is a number from 1 to 6 and refers to the requested action. This is a list of the values and their actions:

1. ODBC_ADD_DSN (use this to add a user DSN)
2. ODBC_CONFIG_DSN (use this to configure a user DSN)
3. ODBC_REMOVE_DSN (use this to remove a user DSN)
4. ODBC_ADD_SYS_DSN (use this to add a system DSN)
5. ODBC_CONFIG_SYS_DSN (use this to configure a system DSN)
6. ODBC_REMOVE_SYS_DSN (use this to remove a system DSN)

The driver parameter is the name of the driver, and the attributes parameter contains the database path and other login information.

Creating our methods

Now, it is time to create easy to use methods. Let us say that we always connect to a Microsoft Access database (for now). In that case, we only have to provide the DSN-name and database path. In this case, our AddUserDSN method will be:

public bool AddUserDSN(string DSName, string DBPath)
{
  return SQLConfigDataSource((IntPtr)0, 1, 
     "Microsoft Access Driver (*.MDB)\0",
     "DSN=" + DSName + "\0Uid=Admin\0pwd=\0DBQ=" + DBPath + "\0");
}

The method will return true if the action is successful. We now can call our method using the following syntax:

DSNAdmin.AddUserDSN("MyDSNName", "C:\MyDBPath\MyDB.mdb");

No more messing around in the configuration screen, users can now change their DB path without having to worry about changing DSN-properties.