创建SQL Server ODBC数据源的最简单，最易维护的方法是什么？

I need a programmatic way of creating a SQL Server ODBC Data Source. I can do this by directly accessing the Registry. It would be better if this could be done via an available (SQL Server/Windows) API to protect against changes in the registry keys or values with updated SQL Server drivers.

我需要一种编程方式来创建SQL Server ODBC数据源。我可以通过直接访问注册表来完成此操作。如果可以通过可用的(SQL Server / Windows)API来防止注册表项或更新的SQL Server驱动程序的值发生更改,那将会更好。

Accepted Answer Note: Using SQLConfigDataSource abstracts the code from the details of Registry keys etc. so this is more robust. I was hoping, however, that SQL Server would have wrapped this with a higher level function which took strongly typed attributes (rather than a delimited string) and exposed it through the driver.

已接受的答案注意:使用SQLConfigDataSource从注册表项等的详细信息中抽象出代码,因此这更加强大。但是,我希望SQL Server能够使用更高级别的函数来包装它,该函数采用强类型属性(而不是分隔字符串)并通过驱动程序公开它。

5 个解决方案

#1

SQLConfigDataSource() does the job.

SQLConfigDataSource()完成这项工作。

MSDN article

Just in case here is a VB6 example:

以防万一这是一个VB6示例:

Const ODBC_ADD_DSN = 1 'user data source
Const ODBC_ADD_SYS_DSN = 4 'system data source

Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal
hwndParent As Long, ByVal fRequest As Long, ByVal lpszDriver As String, ByVal
lpszAttributes As String) As Long

strDriver = "SQL Server"
strAttributes = "DSN=Sample" & Chr$(0) _
& "Database=Northwind" & Chr$(0) _
& "Description= Sample Data Source" & Chr$(0) _
& "Server=(local)" & Chr$(0) _
& "Trusted_Connection=No" & Chr$(0)

SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, strDriver, strAttributes)

#2

For VB.NET it can be done this way:

对于VB.NET,可以这样做:

Import for 'DllImport':

导入'DllImport':

Imports System.Runtime.InteropServices

Declaration of SQLConfigDataSource:

SQLConfigDataSource声明:

<DllImport("ODBCCP32.DLL")> Shared Function SQLConfigDataSource _
(ByVal hwndParent As Integer, ByVal fRequest As Integer, _
    ByVal lpszDriver As String, _
    ByVal lpszAttributes As String) As Boolean
End Function

Example usage:

Const ODBC_ADD_DSN = 1 'User data source
Const ODBC_ADD_SYS_DSN = 4 'System data source

Public Function CreateSqlServerDataSource
    Dim strDriver As String : strDriver = "SQL Server"
    Dim strAttributes As String : strAttributes = _
        "DSN=Sample" & Chr(0) & _
        "Database=Northwind" & Chr(0) & _
        "Description= Sample Data Source" & Chr(0) & _
        "Server=(local)" & Chr(0) & _
        "Trusted_Connection=No" & Chr(0)

    SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, strDriver, strAttributes)
End Function

#3

I'd use odbcad32.exe which is located in your system32 folder.

我将使用位于system32文件夹中的odbcad32.exe。

This will add your odbc data sources to the correcct location, which won't be effected by any patches.

这会将您的odbc数据源添加到更正位置,这不会受到任何补丁的影响。

#4

To do this directly in the registry you can add a String Value to:

要在注册表中直接执行此操作,您可以将字符串值添加到:

HKLM\SOFTWARE\Microsoft\ODBC\ODBC.INI\ODBC Data Sources

to add a System DSN, or:

添加系统DSN,或:

HKCU\Software\ODBC\ODBC.INI\ODBC Data Sources

to add a User DSN.

添加用户DSN。

The Name of the Value is the name of the Data Source you want to create and the Data must be 'SQL Server'.

值的名称是您要创建的数据源的名称,数据必须是“SQL Server”。

At the same level as 'ODBC Data Sources' in the Registry create a Key with the name of the Data Source you want to create.

与注册表中的“ODBC数据源”处于同一级别,创建一个具有要创建的数据源名称的密钥。

This key needs the following String Values:

此密钥需要以下字符串值:

Database     - Name of default database to which to connect
Description  - A description of the Data Source
Driver       - C:\WINDOWS\system32\SQLSRV32.dll
LastUser     - Name of a database user (e.g. sa)
Server       - Hostname of machine on which database resides

For example, using the reg.exe application from the command line to add a User Data Source called 'ExampleDSN':

例如,使用命令行中的reg.exe应用程序添加名为“ExampleDSN”的用户数据源:

reg add "HKCU\Software\ODBC\ODBC.INI\ODBC Data Sources" 
    /v ExampleDSN /t REG_SZ /d "SQL Server"
reg add HKCU\Software\ODBC\ExampleDSN 
    /v Database /t REG_SZ /d ExampleDSN
reg add HKCU\Software\ODBC\ExampleDSN 
    /v Description /t REG_SZ /d "An Example Data Source"
reg add HKCU\Software\ODBC\ExampleDSN
    /v Driver /t REG_SZ /d "C:\WINDOWS\system32\SQLSRV32.DLL"
reg add HKCU\Software\ODBC\ExampleDSN
    /v LastUser /t REG_SZ /d sa
reg add HKCU\Software\ODBC\ExampleDSN
    /v Server /t REG_SZ /d localhost

#5

Sample Using C#:

使用C#的示例:

( Detailed SQL Server param reference at http://msdn.microsoft.com/en-us/library/aa177860.aspx )

(详细的SQL Server参数参考,请访问http://msdn.microsoft.com/en-us/library/aa177860.aspx)

using System.Runtime.InteropServices; 

        private enum RequestFlags : int
        {

            ODBC_ADD_DSN = 1,
            ODBC_CONFIG_DSN = 2,
            ODBC_REMOVE_DSN = 3,
            ODBC_ADD_SYS_DSN = 4,
            ODBC_CONFIG_SYS_DSN = 5,
            ODBC_REMOVE_SYS_DSN = 6,
            ODBC_REMOVE_DEFAULT_DSN = 7

        }

        [DllImport("ODBCCP32.DLL", CharSet = CharSet.Unicode, SetLastError = true)]
        private static extern bool SQLConfigDataSource(UInt32 hwndParent, RequestFlags  fRequest, 
                                 string lpszDriver, string lpszAttributes);

        public static void CreateDSN()
        {

            string strDrivername = "SQL Server";
            string strConfig =  "DSN=*\0" +
                                   "Database=Northwind\0" +
                                   "Description=* Sample\0" +
                                   "Server=(local)\0" +
                                   "Trusted_Connection=No\0";

            bool success = SQLConfigDataSource(0, RequestFlags.ODBC_ADD_SYS_DSN, strDrivername, strConfig);

        }

#1