Create an ODBC DSN entry using c# (for MySQL)
Create MySQL User:
CREATE USER 'abirami'@'localhost' IDENTIFIED BY 'abirami';
GRANT ALL PRIVILEGES ON *.* TO 'abirami'@'localhost' WITH GRANT OPTION;
MySQL ODBC 3.51 Download:
Web.config
<appSettings>
<!-- dsn Entries-->
<add key="serverName" value="localhost"/>
<add key="DSNName" value="dsnabirami"/>
<add key="driverName" value="MySQL ODBC 3.51 Driver"/>
<add key="databaseName" value="abiramittm"/>
<add key="description" value="Sample DSN"/>
<add key="User" value="abirami"/>
<add key="Password" value="abirami"/>
</appSettings>
Create New Class file(ODBCManager.cs) and add below line:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Win32;
using System.IO;
namespace WinUI
{
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\\";
///
/// Creates a new DSN entry with the specified values. If the DSN exists, the values are updated.
///
/// Name of the DSN for use by client applications
/// Description of the DSN that appears in the ODBC control panel applet
/// Network name or IP address of database server
/// Name of the driver to use
/// True to use NT authentication, false to require applications to supply username/password in the connection string
/// Name of the datbase to connect to
public static void CreateSystemDSN(string dsnName, string description, string server, string driverName, bool trustedConnection, string database, string User, string Password)
{
// Lookup driver path from driver name
RegistryKey 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
RegistryKey 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
RegistryKey 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("Server", server);
dsnKey.SetValue("Database", database);
dsnKey.SetValue("User", User);
dsnKey.SetValue("Password", Password);
dsnKey.SetValue("Trusted_Connection", trustedConnection ? "Yes" : "No");
}
public static void CreateUSERDSN(string dsnName, string description, string server, string driverName, bool trustedConnection, string database, string User, string Password)
{
// Lookup driver path from driver name for HKEY_CURRENT_USER
RegistryKey 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
RegistryKey datasourcesKey = Registry.CurrentUser.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
RegistryKey dsnKey = Registry.CurrentUser.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("Server", server);
dsnKey.SetValue("Database", database);
dsnKey.SetValue("User", User);
dsnKey.SetValue("Password", Password);
dsnKey.SetValue("Trusted_Connection", trustedConnection ? "Yes" : "No");
}
///
/// Removes a DSN entry
///
/// Name of the DSN to remove.
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
RegistryKey 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);
}
///
/// Checks the registry to see if a DSN exists with the specified name
///
public static bool DSNExists(string dsnName)
{
bool retval = false;
string DSNPath = "";
try
{
RegistryKey dsnKey = Registry.CurrentUser.CreateSubKey(ODBC_INI_REG_PATH, RegistryKeyPermissionCheck.ReadSubTree);
if (dsnKey.OpenSubKey(dsnName) != null)
{
DSNPath = dsnKey.OpenSubKey(dsnName).Name;
if (DSNPath.Equals(dsnKey.Name + @"\" + dsnName))
{
retval = true;
}
}
}
catch (Exception ex)
{
throw ex;
//MessageBox.Show(ex.ToString());
}
return retval;
}
///
/// Returns an array of driver names installed on the system
///
}
}
Form Load of frmLogin.cs
private void frmLogin_Load(object sender, EventArgs e)
{
try
{
string serverName = ConfigurationManager.AppSettings["serverName"].ToString();
string DSNName = ConfigurationManager.AppSettings["DSNName"].ToString();
string driverName = ConfigurationManager.AppSettings["driverName"].ToString();
string databaseName = ConfigurationManager.AppSettings["databaseName"].ToString();
string description = ConfigurationManager.AppSettings["description"].ToString();
string User = ConfigurationManager.AppSettings["User"].ToString();
string Password = ConfigurationManager.AppSettings["Password"].ToString();
if (!ODBCManager.DSNExists(DSNName))
{
ODBCManager.CreateUSERDSN(DSNName, description, serverName, driverName, false, databaseName, User, Password);
logger.Info(DSNName + " Created Sucessfully");
}
}
catch (Exception ex)
{
logger.Error(ex.Message);
MessageBox.Show(ex.Message);
}
}
0 comments:
Post a Comment