How to Use SQLConfigDataSource in a Visual Studio 2012 C# Project

Question

I want to create a User DSN programmatically. I have read this and similar SO questions; trolled the MSDN forums; and looked in several other places, to find out how to make SQLConfigDataSource work. In the SO post I've linked above, I see SQLConfigDataSourceW is referenced, but cannot find a reference to that.

I want to use SQLConfigDataSource or a similar function in a Visual Studio 2012 C# project, and do not know what should be included in my .cs file.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

I've looked at System.Data.Odbc and SQLConfigDataSource appears not to be part of that. I believe the ODBC drivers are installed on my workstation. Other than using a .reg file or manually creating a DSN, what other steps can I try?

Answer 1

Other than using a .reg file or manually creating a DSN, what other steps can I try to create a User DSN programmatically?

You could use the ODBCCONF.exe, a command-line tool that allows you to configure ODBC drivers and data source names. It uses the SQLConfigDataSource function. However, that is such old technology and Microsoft warns:

ODBCCONF.exe will be removed in a future version of Windows Data Access Components. Avoid using this feature, and plan to modify applications that currently use this feature.

I am not sure what impact decommissioning ODBCCONF.exe will have on SQLConfigDataSource but it doesn't look good. Hence one of the best way forward is to use the recommended PowerShell commands to manage drivers and data sources, here is the list of Windows Data Access Components PowerShell Commands.

You just run cmdlets like Add-OdbcDsn:

Add-OdbcDsn [-Name] <String> -DriverName <String> -DsnType <String> [-AsJob] [-CimSession <CimSession> ] [-PassThru] [-Platform <String> ] [-SetPropertyValue <String> ] [-ThrottleLimit <Int32> ] [ <CommonParameters>]

eg:

PS C:> Add-OdbcDsn -Name "MyPayroll" -DriverName "SQL Server Native Client 10.0" -DsnType "System" -SetPropertyValue @("Server=MyServer", "Trusted_Connection=Yes", "Database=Payroll")

_{I'm guessing you already know or can easily find out how to execute these powershell commands from in C#.}

---

Unfortunately the above powershell commands are for Windows 8 and above.

For Windows 7 and below you could either:
a) use ODBCCONF.exe
b) write to the registry directly in C#
c) use the SQLConfigDataSource function, or
d) use a Powershell script that works for Windows 7:

PS C:\> Add-ODBCconnection2HKCU.ps1

Here is the add-ODBCconnection2HKCU.ps1:

## Add to the "HKEY_CURRENT_USER" - registry ODBC update Add a ODBC Connection as a User DSN tab
$HKCUaddpath = "HKCU:\SOFTWARE\ODBC\ODBC.INI\LocalDeveloper"
cd HKCU:\SOFTWARE\ODBC\ODBC.INI\LocalDeveloper
md LocalDeveloper
Set-ItemProperty -path $HKCUaddpath -name Database -value "Developer"
Set-ItemProperty -path $HKCUaddpath -name Description -value "Local Developer database"
Set-ItemProperty -path $HKCUaddpath -name Driver -value "C:\Windows\System32\SQLSRV32.dll"
Set-ItemProperty -path $HKCUaddpath -name LastUser -value ""
Set-ItemProperty -path $HKCUaddpath -name Server -value "Max-PCWIN1"
Set-ItemProperty -path $HKCUaddpath -name Trusted_Connection -value "Yes"

## This is needed for this ODBC connection to show in the "ODBC Administator" application
cd HKCU:\SOFTWARE\ODBC\ODBC.INI\'ODBC Data Sources'
Set-ItemProperty -path HKCU:\SOFTWARE\ODBC\ODBC.INI\'ODBC Data Sources' -name LocalDeveloper -value "SQL Server"

REF: http://maxt2posh.wordpress.com/2009/06/07/powershell-working-with-odbc-dsn-connection/