How to Create an Odbc Dsn Entry Using C#

How do I create an ODBC DSN entry using C#?

I actually solved this myself in the end by manipulating the registry. I've created a class to contain the functionality, the contents of which I've included here:

///<summary>
/// Class to assist with creation and removal of ODBC DSN entries
///</summary>
public static class ODBCManager
{
    private const string ODBC_INI_REG_PATH = "SOFTWARE\\ODBC\\ODBC.INI\\";
    private const string ODBCINST_INI_REG_PATH = "SOFTWARE\\ODBC\\ODBCINST.INI\\";

    /// <summary>
    /// Creates a new DSN entry with the specified values. If the DSN exists, the values are updated.
    /// </summary>
    /// <param name="dsnName">Name of the DSN for use by client applications</param>
    /// <param name="description">Description of the DSN that appears in the ODBC control panel applet</param>
    /// <param name="server">Network name or IP address of database server</param>
    /// <param name="driverName">Name of the driver to use</param>
    /// <param name="trustedConnection">True to use NT authentication, false to require applications to supply username/password in the connection string</param>
    /// <param name="database">Name of the datbase to connect to</param>
    public static void CreateDSN(string dsnName, string description, string server, string driverName, bool trustedConnection, string database)
    {
        // Lookup driver path from driver name
        var driverKey = Registry.LocalMachine.CreateSubKey(ODBCINST_INI_REG_PATH + driverName);
        if (driverKey == null) throw new Exception(string.Format("ODBC Registry key for driver '{0}' does not exist", driverName));
        string driverPath = driverKey.GetValue("Driver").ToString();

        // Add value to odbc data sources
        var datasourcesKey = Registry.LocalMachine.CreateSubKey(ODBC_INI_REG_PATH + "ODBC Data Sources");
        if (datasourcesKey == null) throw new Exception("ODBC Registry key for datasources does not exist");
        datasourcesKey.SetValue(dsnName, driverName);

        // Create new key in odbc.ini with dsn name and add values
        var dsnKey = Registry.LocalMachine.CreateSubKey(ODBC_INI_REG_PATH + dsnName);
        if (dsnKey == null) throw new Exception("ODBC Registry key for DSN was not created");
        dsnKey.SetValue("Database", database);
        dsnKey.SetValue("Description", description);
        dsnKey.SetValue("Driver", driverPath);
        dsnKey.SetValue("LastUser", Environment.UserName);
        dsnKey.SetValue("Server", server);
        dsnKey.SetValue("Database", database);
        dsnKey.SetValue("Trusted_Connection", trustedConnection ? "Yes" : "No");
    }

    /// <summary>
    /// Removes a DSN entry
    /// </summary>
    /// <param name="dsnName">Name of the DSN to remove.</param>
    public static void RemoveDSN(string dsnName)
    {
        // Remove DSN key
        Registry.LocalMachine.DeleteSubKeyTree(ODBC_INI_REG_PATH + dsnName);

        // Remove DSN name from values list in ODBC Data Sources key
        var datasourcesKey = Registry.LocalMachine.CreateSubKey(ODBC_INI_REG_PATH + "ODBC Data Sources");
        if (datasourcesKey == null) throw new Exception("ODBC Registry key for datasources does not exist");
        datasourcesKey.DeleteValue(dsnName);
    }

    ///<summary>
    /// Checks the registry to see if a DSN exists with the specified name
    ///</summary>
    ///<param name="dsnName"></param>
    ///<returns></returns>
    public static bool DSNExists(string dsnName)
    {
        var driversKey = Registry.LocalMachine.CreateSubKey(ODBCINST_INI_REG_PATH + "ODBC Drivers");
        if (driversKey == null) throw new Exception("ODBC Registry key for drivers does not exist");

        return driversKey.GetValue(dsnName) != null;
    }

    ///<summary>
    /// Returns an array of driver names installed on the system
    ///</summary>
    ///<returns></returns>
    public static string[] GetInstalledDrivers()
    {
        var driversKey = Registry.LocalMachine.CreateSubKey(ODBCINST_INI_REG_PATH + "ODBC Drivers");
        if (driversKey == null) throw new Exception("ODBC Registry key for drivers does not exist");

        var driverNames = driversKey.GetValueNames();

        var ret = new List<string>();

        foreach (var driverName in driverNames)
        {
            if (driverName != "(Default)")
            {
                ret.Add(driverName);
            }
        }

        return ret.ToArray();
    }
}

How do I make a programmatically-created System DSN show up as an ODBC Data Source?

Well, I realized my own mistake. When creating the subkey off of ODBC Data Sources, I never actually put in the line that creates that key:

dataSourcesKey.SetValue(information.DSNName, information.DriverName);

Adding that line made it work as intended! Thanks for the suggestions and comments, though.

C# connect to System ODBC datasource

Just put in the DSN name that's been configured:

using System.Data.Odbc;
OdbcConnection DbConnection = new OdbcConnection("DSN=SAMPLE_ISAM");
// Your code here
DbConnection.Close();

Everything else is the same, the "Connection String" information is all contained in the DSN itself, if properly configured.

Check for System DSN and create System DSN if NOT Existing (iSeries Access ODBC Driver)

After going through the few less complicated examples available online, this is what i managed to come up with (and it works fine for me).

using System;
using System.Runtime.InteropServices;

public class ODBC_Manager
{
    [DllImport("ODBCCP32.dll")]
    public static extern bool SQLConfigDataSource(IntPtr parent, int request, string driver, string attributes);

    [DllImport("ODBCCP32.dll")]
    public static extern int SQLGetPrivateProfileString(string lpszSection, string lpszEntry, string lpszDefault, string @RetBuffer, int cbRetBuffer, string lpszFilename);

    private const short ODBC_ADD_DSN = 1;
    private const short ODBC_CONFIG_DSN = 2;
    private const short ODBC_REMOVE_DSN = 3;
    private const short ODBC_ADD_SYS_DSN = 4;
    private const short ODBC_CONFIG_SYS_DSN = 5;
    private const short ODBC_REMOVE_SYS_DSN = 6;
    private const int vbAPINull = 0;

    public void CreateDSN(string strDSNName)
    {
        string strDriver;
        string strAttributes;

        try
        {
            string strDSN = "";

            string _server = //ip address of the server
            string _user = //username
            string _pass = //password
            string _description = //not required. give a description if you want to

            strDriver = "iSeries Access ODBC Driver";

            strAttributes = "DSN=" + strDSNName + "\0";
            strAttributes += "SYSTEM=" + _server + "\0";
            strAttributes += "UID=" + _user + "\0";
            strAttributes += "PWD=" + _pass + "\0";

            strDSN = strDSN + "System = " + _server + "\n";
            strDSN = strDSN + "Description = " + _description + "\n";

            if (SQLConfigDataSource((IntPtr)vbAPINull, ODBC_ADD_SYS_DSN, strDriver, strAttributes))
            {
                Console.WriteLine("DSN was created successfully");
            }
            else
            {
                Console.WriteLine("DSN creation failed...");
            }
        }
        catch (Exception ex)
        {
            if (ex.InnerException != null)
            {
                Console.WriteLine(ex.InnerException.ToString());
            }
            else
            {
                Console.WriteLine(ex.Message.ToString());
            }
        }
    }

    public int CheckForDSN(string strDSNName)
    {
        int iData;
        string strRetBuff = "";
        iData = SQLGetPrivateProfileString("ODBC Data Sources", strDSNName, "", strRetBuff, 200, "odbc.ini");
        return iData;
    }
}

... and then call the methods from your application.

static void Main(string[] args)
{
    ODBC_Manager odbc = new ODBC_Manager();
    string dsnName = //Name of the DSN connection here

    if (odbc.CheckForDSN(dsnName) > 0)
    {
        Console.WriteLine("\n\nODBC Connection " + dsnName + " already exists on the system");
    }
    else
    {
        Console.WriteLine("\n\nODBC Connection " + dsnName + " does not exist on the system");
        Console.WriteLine("\n\nPress 'Y' to create the connection?");

        string cont = Console.ReadLine();
        if (cont == "Y" || cont == "y")
        {
            odbc.CreateDSN(dsnName);
            Environment.Exit(1);
        }
        else
        {
            Environment.Exit(1);
        }
    }
}

How do I install an ODBC driver using C# during my apllication setup?

I found a good example for the first part of my question on this website.

As for the second part, "how to configure the DSN", I found another code snippet which only needed some tweaking.

    private const string ODBC_INI_REG_PATH = "SOFTWARE\\ODBC\\ODBC.INI\\";
    private const string ODBCINST_INI_REG_PATH = "SOFTWARE\\ODBC\\ODBCINST.INI\\";

    /// <summary>
    /// Creates a new System-DSN entry with the specified values. If the DSN exists, the values are updated.
    /// </summary>
    /// <param name="dsnName">Name of the DSN for use by client applications</param>
    /// <param name="description">Description of the DSN that appears in the ODBC control panel applet</param>
    /// <param name="server">Network name or IP address of database server</param>
    /// <param name="driverName">Name of the driver to use</param>
    /// <param name="trustedConnection">True to use NT authentication, false to require applications to supply username/password in the connection string</param>
    /// <param name="database">Name of the datbase to connect to</param>
    public static void CreateDSN2(string dsnName, string description, string server, string driverName, bool trustedConnection, string database, string user, string password, string port)
    {
        // Lookup driver path from driver name
        var driverKey = Registry.LocalMachine.CreateSubKey(ODBCINST_INI_REG_PATH + driverName);
        if (driverKey == null) throw new Exception(string.Format("ODBC Registry key for driver '{0}' does not exist", driverName));
        string driverPath = driverKey.GetValue("Driver").ToString();

        // Add value to odbc data sources
        var datasourcesKey = Registry.LocalMachine.CreateSubKey(ODBC_INI_REG_PATH + "ODBC Data Sources");
        if (datasourcesKey == null) throw new Exception("ODBC Registry key for datasources does not exist");
        datasourcesKey.SetValue(dsnName, driverName);

        // Create new key in odbc.ini with dsn name and add values
        var dsnKey = Registry.LocalMachine.CreateSubKey(ODBC_INI_REG_PATH + dsnName);
        //MessageBox.Show(dsnKey.ToString());
        if (dsnKey == null) throw new Exception("ODBC Registry key for DSN was not created");
        dsnKey.SetValue("Data Source", dsnName);
        dsnKey.SetValue("Database", database);
        dsnKey.SetValue("Description", description);
        dsnKey.SetValue("Driver", driverPath);
        dsnKey.SetValue("Server", server);
        dsnKey.SetValue("User name", user);
        dsnKey.SetValue("Password", password);
        dsnKey.SetValue("Port", port);
        dsnKey.SetValue("Trusted_Connection", trustedConnection ? "Yes" : "No");
    }

Is it possible to create a DSN on a remote server in C#

I would recommend using ps-exec for a task like this.

---

Related Topics