Add or edit SQL 2008 DSN connections in VB.NET
Some programs created in Visual Studio 2008 require a SQL Server 2008 DSN connection to do various things like access databases, run Crystal Reports, etc. When deploying a program that needs a particular SQL 2008 DSN, it's a general pain to have to manually create a new SQL Native Client 10.0 DSN on every computer that the program is deployed too. Wouldn't you want to detect the presence of the needed SQL DSN (which we'll call SQLDSN), create it if it doesn't exist, and update it to include the needed server and database name if it does? This can be done using API calls. The first thing that you need to do is include the following line of code in the module, class, or form that will be adding or editing your DSN connection.
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
This essentially includes an entry point to the SQLConfigDataSource function in ODBCCP32.DLL. Go to the MSDN definition of SQLConfigDataSource for more information about this function. Now - I have utilized this function first by creating my DSN attributes as follows.
Dim attr As String = "SERVER=" & server & Chr(0) & "DSN=SQLDSN" & Chr(0) & "DESCRIPTION=SQL 2008 DSN" & Chr(0) & "DATABASE=" & database & Chr(0)
Then I call the SQLConfigDataSource function with the following syntax to add the DSN.
SQLConfigDataSource(0, 4, "SQL Server Native Client 10.0", attr)
And with the following syntax to edit the DSN.
SQLConfigDataSource(0, 5, "SQL Server Native Client 10.0", attr)
The second parameter indicates what type of datasource the connection is (user or system), and also the action being taken (add, edit, or remove). Adding or editing a SQL Server 2008 DSN is as simple as this in VB.NET.
Tags: api, datasource, detect dsn, dsn, odbc, sql 2008, sql server native client, vb.net, visual studio 2008
Comments
Got something to say?