Create SQL DSN in C#

Introduction

This tip is about creating DSN through C#

Using the code

In order to create a DSN in C#, we just need to write few lines of code. Consider the code below:

            string ODBC_PATH = "SOFTWARE\\ODBC\\ODBC.INI\\";     
            string driverName = "SQL Server";
            string dsnName = "DSNfromCode";
            string database = "MyDBName";
            string description = "This DSN was created from code!";
            string server = "192.168.56.154";
            bool trustedConnection = false;

            // Lookup driver path from driver name         
            string driverPath = "C:\\WINDOWS\\System32\\sqlsrv32.dll"; 
                     
            var datasourcesKey = Registry.LocalMachine.CreateSubKey(ODBC_PATH + "ODBC Data Sources");         
            if (datasourcesKey == null) 
{
throw new Exception("ODBC Registry key 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_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", "sa");         
            dsnKey.SetValue("Server", server);         
            dsnKey.SetValue("Database", database);
            dsnKey.SetValue("username", "sa");
            dsnKey.SetValue("password", "system123#");
            dsnKey.SetValue("Trusted_Connection", trustedConnection ? "Yes" : "No");

Please note, here I am using the hardcoded values. you can pass these values through the user input also or as per your requirement. Note the following lines in the code:

string dsnName = "DSNfromCode";
string database = "MyDBName";
string description = "This DSN was created from code!";
string server = "192.168.56.154";
bool trustedConnection = false;

Here,

1. dsnName is the name of the DSN that you want to create.

2. database name is name of your Database.

3. description and server is the description and Database IP respectively.

4. trustedconnection "false" indicates SQL server Authentication. If it's true then Authentication mode is Windows.

Next, you need to pass the driver (sqlsrv32.dll).

At last, pass all these values to "SetValue" (see last few line of code). I have also pass the SQL username and Pwd hardcoded. you can also pass thse through user inputs.

Points of Interest

If you do not pass the LastUser, it will enter Windows User.