I'm working on a legacy application that has a C++ extended stored procedure. This xsproc uses ODBC to connect to the database, which means it requires a DSN to be configured.
I'm updating the installer (created using Visual Studio 2008 setup project), and want to have a custom action that can create the ODBC DSN entry, but am struggling to find useful information on Google.
Can anyone help?
There is a CodeProject page on reading ODBC information.
Reading that should give you the information you need to reverse engineer writing the registry entries you need.
From that code;
private const string ODBC_LOC_IN_REGISTRY = "SOFTWARE\\ODBC\\";
private const string ODBC_INI_LOC_IN_REGISTRY =
ODBC_LOC_IN_REGISTRY + "ODBC.INI\\";
private const string DSN_LOC_IN_REGISTRY =
ODBC_INI_LOC_IN_REGISTRY + "ODBC Data Sources\\";
private const string ODBCINST_INI_LOC_IN_REGISTRY =
ODBC_LOC_IN_REGISTRY + "ODBCINST.INI\\";
private const string ODBC_DRIVERS_LOC_IN_REGISTRY =
ODBCINST_INI_LOC_IN_REGISTRY + "ODBC Drivers\\";
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();
}
}
Not bad, this one helped me out! but only working for SQL! If you using e.g. MS Access you have to edit some other keys!
Thanks for providing this code, I have used it myself. I had to change two things tough:
To get the driverName I had to use OpenSubKey instead of CreateSubKey to get the values:
// Lookup driver path from driver name
var driverKey = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(
ODBCINST_INI_REG_PATH + driverName);
Since I am running Vista, I had to use an application manifest and set the requestedPrivileges to:
<requestedExecutionLevel level="requireAdministrator" uiAccess="false"/>
The following article helped me to find the OpenSubKey issue: http://www.daveoncsharp.com/2009/08/read-write-delete-from-windows-registry-with-csharp/
+1 for Barnwell's code!
However, I think his DSNExists() is querying the wrong key. I think it should be this:
public static bool DSNExists(string dsnName)
{
var sourcesKey = Registry.LocalMachine.CreateSubKey(ODBC_INI_REG_PATH + "ODBC Data Sources");
if (sourcesKey == null) throw new Exception("ODBC Registry key for sources does not exist");
return sourcesKey.GetValue(dsnName) != null;
}
Excellent piece of code. Helped my in my Setup application.
Replaced the keyword "var" with "RegistryKey" and all went well.
Thanks!!
There is an API for doing stuff like this. Using the API will also make sure that your application will stay compatible with newer versions of Windows. The API can be found here:
http://msdn.microsoft.com/en-us/library/ms716476(VS.85).aspx
PInvoking this function in c# can be found on PInvoke.net.
thanks it was a great help if you are making a dsn to excel maybe need add something like this
var dsnKeyEng = Microsoft.Win32.Registry.LocalMachine.CreateSubKey(ODBC_INI_REG_PATH + dsnName + "\\Engines");
var dsnKeyExl = Microsoft.Win32.Registry.LocalMachine.CreateSubKey(ODBC_INI_REG_PATH + dsnName + "\\Engines\\Excel");
dsnKeyExl.SetValue("FirstRowHasNames", 01);
dsnKeyExl.SetValue("MaxScanRows", 8);
dsnKeyExl.SetValue("Threads",3);
dsnKeyExl.SetValue("UserCommitSync", "Yes")
Further to chrfalch's post, here is some sample code for updating a DSN (I know the OP is asking for creation, however this code is easily translatable to whatever you need to do) using the API call rather than via the registry direct (using the information from the pinvoke.net page):-
[DllImport("ODBCCP32.DLL", CharSet = CharSet.Unicode, SetLastError = true)]
static extern bool SQLConfigDataSourceW(UInt32 hwndParent, RequestFlags fRequest, string lpszDriver, string lpszAttributes);
enum RequestFlags : int
{
ODBC_ADD_DSN = 1,
ODBC_CONFIG_DSN = 2,
ODBC_REMOVE_DSN = 3,
ODBC_ADD_SYS_DSN = 4,
ODBC_CONFIG_SYS_DSN = 5,
ODBC_REMOVE_SYS_DSN = 6,
ODBC_REMOVE_DEFAULT_DSN = 7
}
bool UpdateDsnServer(string name, string server)
{
var flag = RequestFlags.ODBC_CONFIG_SYS_DSN;
string dsnNameLine = "DSN=" + name;
string serverLine = "Server=" + server;
string configString = new[] { dsnNameLine, serverLine }.Aggregate("", (str, line) => str + line + "\0");
return SQLConfigDataSourceW(0, flag, "SQL Server", configString);
}
Will this code create a 32-bit or 64-bit ODBC driver on 64-bit machines?