Programmatically Adding A Dsn To A Machine Using Vb.net

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 Page 2 of 5

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.

Document Written on 11/24/2008

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

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.

Document Written on 11/24/2008

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

Appendix: The Source Code Illustration Imports Microsoft.Win32 Imports System.Text

' for registry access ' 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 SQLConfigDataSource Dim serverName As String Dim DSNName As String Dim driverName As String Dim databaseName As String Dim description As String

Server NAME

' Attributes to be passed to ' ' ' ' '

SQL Server Machine Name. DSN Name DB Driver Database DSN Description

' Set up the variables to be passed to SQLConfigDataSource ' In this example, we are setting up a DSN to connect to SQL serverName = "DevSQL"

' CHANGE THIS VALUE TO THE SERVER

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

Document Written on 11/24/2008

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

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

Document Written on 11/24/2008