8477127 programmatically adding a dsn to a machine using vbnet

Programmatically Adding a DSN to a Machine Using Visual Basic .NET 2005

Jennifer Lewis

Programmatically Adding a DSN to a Machine Using Visual Basic .NET 2005 of 5

Document Written on 11/24/2008

Overview While it is relatively simple to manually add a DSN to a machine, you may have a project where you have to automatically add a DSN to a machine. For example, if you are installing a PC-based application on a machine that uses a DSN that connects to a SQL Server or to a local database, you may want to improve the user experience by adding an additional program in your Setup and Deployment package that automatically creates the DSN rather than have the user manually create the DSN. Using .NET, you can create a program that will automatically create a DSN on a machine. In a nutshell, here is what you will be doing:

• You will be using one of the Windows DLLs (ODBCCP32.dll) that contains the function SQLConfigDataSource. This function adds the DSN to your machine.

• You will also be importing the Microsoft.Win32 library because you will need to access the registry to add the User ID that connects to the data source (if applicable)

In this illustration, Visual Basic .NET 2005 will be used to connect to a SQL Server data source. However, you should be able to use other .NET languages and/or other versions of .NET, and you should be able to connect to other data source types, like Access or Oracle. What You Need in Your Project 1) For registry access, you will need to import the Microsoft.Win32 library at the top of your

code.

2) Right after the class declaration statement (Public Class or Module), declare the

function SQLConfigDataSource. Option 1 Public Class Startup

' This ODBC function allows you to automatically create a DSN Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Integer, ByVal fRequest As Integer, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Integer

Option 2 Module CreateDSN ‘ This ODBC function allows you to automatically create a DSN Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL"

(ByVal hwndParent As Integer, ByVal fRequest As Integer, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Integer

3) The function SQLConfigDataSource accepts 4 parameters:

a. Parent – this value is almost always zero. b. The type of DSN to create. A value of 4 is a system DSN, while a value of 1 is

a user DSN. c. The driver name. This is the driver of the data source. For example, if you are

creating a DSN to connect to a SQL Server data source, the driver name is SQL Server. Reference your database documentation to find out what the database driver is called.

d. The attributes. This contains the information such as: the server, the name you want to give to the DSN, the database to connect to on the data source, and whether it’s a trusted connection or not.



Before you call the function, it’s best to create variables to hold the information. In this example, we are using an instance of the StringBuilder class to build the attributes.

serverName = "Enter the server name here" DSNName = "Enter the name of the DSN Here" driverName = "SQL Server" databaseName = "Enter the database name here" description = "Enter the DSN description here" AttrBuilder.Append("SERVER=") AttrBuilder.Append(serverName) AttrBuilder.Append(Chr(0)) AttrBuilder.Append("DSN=") AttrBuilder.Append(DSNName) AttrBuilder.Append(Chr(0)) AttrBuilder.Append("DESCRIPTION=") AttrBuilder.Append(description) AttrBuilder.Append(Chr(0)) AttrBuilder.Append("DATABASE=") AttrBuilder.Append(databaseName) AttrBuilder.Append(Chr(0)) AttrBuilder.Append("TRUSTED_CONNECTION=NO") AttrBuilder.Append(Chr(0)) Attr = AttrBuilder.ToString

After building the attributes, call the SQLConfigDataSource function. In this example, we are building a System DSN.

iReturn = SQLConfigDataSource(0, 4, driverName, Attr)

The function returns a 1 if it was created successfully. Be sure to add logic to check the return code to make sure the function returned a successful result.

4) If your data source requires an ID and password, you need to add the ID to the registry.

Add the following lines to create the registry entry: Dim regKey As RegistryKey, regSubKeySW As RegistryKey

Dim regSubKeyODBC As RegistryKey, regSubKeyODBCINI As RegistryKey Dim regSales As RegistryKey regKey = Registry.LocalMachine regSubKeySW = regKey.OpenSubKey("SOFTWARE") regSubKeyODBC = regSubKeySW.OpenSubKey("ODBC") regSubKeyODBCINI = regSubKeyODBC.OpenSubKey("ODBC.INI") regSales = regSubKeyODBCINI.OpenSubKey(DSNName, True) regSales.SetValue("LastUser", "Enter the User ID to connect to SQL Server here")

Note in the final line, you would need to change the line Enter the User ID to connect to SQL Server here to the user ID needed to connect to the SQL Server data source.

If your data source doesn’t require a user ID and password to connect (ex: many Access databases), you do not need to do this step.

The following page contains a full illustration of the source code that creates a DSN called Sample that connects to SQL Server DevSQL and access SQL Server database Bogus. The user to connect to the SQL Server DevSQL is SampleUser. Note: This code is a console application.



Appendix: The Source Code Illustration Imports Microsoft.Win32 ' for registry access Imports System.Text ' for the StringBuilder Module CreateDSN ' This ODBC function allows you to automatically create a DSN Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Integer, ByVal fRequest As Integer, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Integer Sub Main() Dim iReturn As Integer ' return code for SQLConfigDataSource Dim AttrBuilder As New StringBuilder Dim Attr As String ' Attributes to be passed to SQLConfigDataSource Dim serverName As String ' SQL Server Machine Name. Dim DSNName As String ' DSN Name Dim driverName As String ' DB Driver Dim databaseName As String ' Database Dim description As String ' DSN Description ' Set up the variables to be passed to SQLConfigDataSource ' In this example, we are setting up a DSN to connect to SQL Server serverName = "DevSQL" ' CHANGE THIS VALUE TO THE SERVER NAME DSNName = "Sample" ' CHANGE THIS VALUE TO WHAT YOU WANT TO CALL THE DSN driverName = "SQL Server" databaseName = "Bogus" ' CHANGE THIS VALUE TO THE SQL SERVER DB NAME description = "Sample DSN" ' CHANGE THIS VALUE TO THE DSN DESCRIPTION AttrBuilder.Append("SERVER=") AttrBuilder.Append(serverName) AttrBuilder.Append(Chr(0)) AttrBuilder.Append("DSN=") AttrBuilder.Append(DSNName) AttrBuilder.Append(Chr(0)) AttrBuilder.Append("DESCRIPTION=") AttrBuilder.Append(description) AttrBuilder.Append(Chr(0)) AttrBuilder.Append("DATABASE=") AttrBuilder.Append(databaseName) AttrBuilder.Append(Chr(0)) AttrBuilder.Append("TRUSTED_CONNECTION=NO") AttrBuilder.Append(Chr(0)) Attr = AttrBuilder.ToString ' ADD the DSN ' If the 2nd parameter = 1, add as a User DSN ' If the 2nd parameter = 4, add as a System DSN ' Leave the 1st parameter as 0



iReturn = SQLConfigDataSource(0, 4, driverName, Attr) If iReturn <> 1 Then ' ERROR! Console.WriteLine("ERROR with creating " & DSNName) Else ' Update the registry with the user id - can't pass User ID and Password for creating ' a SQL Server DSN, so you have to do it through the registry Dim regKey As RegistryKey, regSubKeySW As RegistryKey Dim regSubKeyODBC As RegistryKey, regSubKeyODBCINI As RegistryKey Dim regSales As RegistryKey regKey = Registry.LocalMachine regSubKeySW = regKey.OpenSubKey("SOFTWARE") regSubKeyODBC = regSubKeySW.OpenSubKey("ODBC") regSubKeyODBCINI = regSubKeyODBC.OpenSubKey("ODBC.INI") regSales = regSubKeyODBCINI.OpenSubKey(DSNName, True) ' CHANGE THE FOLLOWING LINE'S VALUE TO THE USER ID NEEDED TO CONNECT TO THE SQL DATA SOURCE regSales.SetValue("LastUser", "SampleUser") Console.WriteLine(DSNName & " created") End If Console.WriteLine("Press any key to continue....") Console.ReadLine() End Sub End Module

8477127 programmatically adding a dsn to a machine using vbnet

Documents