Managing Data Sources in C#

• Download source files - 21.6 Kb

• Introduction
• The problem
• The Solution
• Conclusion

Introduction

Generally, developers write their own administration programs only if they want to retain complete control over data source configuration, or if they are configuring data sources directly from an application that is acting as an administration program. An administration program, the ODBC Administrator, is shipped with the Data Access SDK and can be redistributed by users of the SDK.

The problem

The need to let a user select his own data source is important in many applications. I set off to design a dialog that allows the user to select his own data source, and while I was doing so, I found out that my user will also need to create his own data source and sometimes change parameters for his data source (like tracing, login user, password, ...) thereby raising the need for the ODBC Administration program.

The Solution

ODBCCP32.dll contains functions to do data source administration. It is a Win32 DLL that exports API for managing data source. Being an unmanaged DLL, interop is a way to access the function. I wrote a class to wrap the functions I needed from the DLL and voila.

public class ODBCCP32
{
    public ODBCCP32()
    {
    }

    #region Interop Methods

    /// <SUMMARY>
    /// Win32 API Imports
    /// </SUMMARY>
    [DllImport( "ODBCCP32.dll")]private static
         extern bool SQLManageDataSources(IntPtr hwnd);
    [DllImport( "ODBCCP32.dll")]private static
         extern bool SQLCreateDataSource(IntPtr hwnd, string lpszDS);

    #endregion

    #region Methods
    public bool ManageDatasources(IntPtr hwnd)
    {
        return SQLManageDataSources(hwnd);
    }

    public bool CreateDatasource(IntPtr hwnd, string szDsn)
    {
        return SQLCreateDataSource(hwnd, szDsn);
    }
    #endregion
}

To read data source information from the registry, two other classes were created; the first one to read registry, and the second one to read data source's information from the registry:

1. How to read the list of data source names:
   C#

   public static string [] DsnList(HKEY hkey)
   {
       string []odbcs;
       if ( hkey == HKEY.CurrentUser )
           odbcs = HKCU.ValueNames(ODBC_SOURCES);
       else
           odbcs = HKLM.ValueNames(ODBC_SOURCES);
   
       return odbcs;
   }
   

2. How to read the database name:
   C#

   public static string Database(string dsn, HKEY hkey)
   {
       string source;
       string database = "";
       string subkey = ODBCREG + "\\" + dsn;
   
       if ( hkey == HKEY.CurrentUser )
           source = (string)HKCU.ReadOption(ODBC_SOURCES, dsn, dsn);
       else
           source = (string)HKLM.ReadOption(ODBC_SOURCES, dsn, dsn);
   
       if ( source != "Microsoft Access Driver (*.mdb)" )
       {
           if ( hkey == HKEY.CurrentUser)
             database = (string)HKCU.ReadOption(subkey, "database", database);
           else
             database = (string)HKLM.ReadOption(subkey, "database", database);
       }
       else
       {
           if ( hkey == HKEY.CurrentUser)
               database = (string)HKCU.ReadOption(subkey, "DBQ", database);
           else
               database = (string)HKLM.ReadOption(subkey, "DBQ", database);
       }
   
       return database;
   }
   

3. How to read the the server name:
   C#

   public static string Server(string dsn, HKEY hkey)
   {
       string source;
       string server = "";
       string subkey = ODBCREG + "\\" + dsn;
   
       if ( hkey == HKEY.CurrentUser)
           source = (string)HKCU.ReadOption(ODBC_SOURCES, dsn, dsn);
       else
           source = (string)HKLM.ReadOption(ODBC_SOURCES, dsn, dsn);
   
       if ( source != "Microsoft Access Driver (*.mdb)" )
       {
           if ( hkey == HKEY.CurrentUser)
               server = (string)HKCU.ReadOption(subkey, "server", server);
           else
               server = (string)HKLM.ReadOption(subkey, "server", server);
       }
   
       return server;
   }
   

Conclusion

For more information, see ODBC SDK Programmer's Reference, Chapter 23, Setup DLL Function Reference, and Chapter 24, Installer DLL Function Reference.