以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  获取SQL服务器名称  (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=187946)

--  作者:zhuxinhui
--  发布时间:2023/8/22 16:10:00
--  获取SQL服务器名称

全局代码

 

Public Class SqlLocator
 #Region "供使用API方式时使用"
 <System.Runtime.InteropServices.DllImport("odbc32.dll")> _
 Private Shared Function SQLAllocHandle(hType As Short, inputHandle As IntPtr, ByRef outputHandle As IntPtr) As Short
 End Function

 <System.Runtime.InteropServices.DllImport("odbc32.dll")> _
 Private Shared Function SQLSetEnvAttr(henv As IntPtr, attribute As Integer, valuePtr As IntPtr, strLength As Integer) As Short
 End Function

 <System.Runtime.InteropServices.DllImport("odbc32.dll")> _
 Private Shared Function SQLFreeHandle(hType As Short, handle As IntPtr) As Short
 End Function


 <System.Runtime.InteropServices.DllImport("odbc32.dll", CharSet := System.Runtime.InteropServices.CharSet.Ansi)> _
 Private Shared Function SQLBrowseConnect(hconn As IntPtr, inString As System.Text.StringBuilder, inStringLength As Short, outString As System.Text.StringBuilder, outStringLength As Short, ByRef outLengthNeeded As Short) As Short
 End Function

 Private Const SQL_HANDLE_ENV As Short = 1
 Private Const SQL_HANDLE_DBC As Short = 2
 Private Const SQL_ATTR_ODBC_VERSION As Integer = 200
 Private Const SQL_OV_ODBC3 As Integer = 3
 Private Const SQL_SUCCESS As Short = 0
 Private Const SQL_NEED_DATA As Short = 99
 Private Const DEFAULT_RESULT_SIZE As Short = 1024

 Private Const SQL_DRIVER_STR As String = "DRIVER=SQL SERVER"

 #End Region

 \'\'\' <summary>
 \'\'\' 禁止实例化
 \'\'\' </summary>
 Private Sub New()
 End Sub

 \'\'\' <summary>
 \'\'\' 获取网内的数据库服务器名称
 \'\'\' </summary>
 \'\'\' <returns>服务器名称数组</returns>
 Public Shared Function GetLocalSqlServerNamesWithAPI() As String()
  Dim List As String = String.Empty
  Dim henv As IntPtr = IntPtr.Zero
  Dim hconn As IntPtr = IntPtr.Zero
  Dim inString As New System.Text.StringBuilder(SQL_DRIVER_STR)
  Dim outString As New System.Text.StringBuilder(DEFAULT_RESULT_SIZE)
  Dim inStringLength As Short = CShort(inString.Length)
  Dim lenNeeded As Short = 0
  Try
   If SQL_SUCCESS = SQLAllocHandle(SQL_HANDLE_ENV, henv, henv) Then
    If SQL_SUCCESS = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, 0) Then
     If SQL_SUCCESS = SQLAllocHandle(SQL_HANDLE_DBC, henv, hconn) Then

      If SQL_NEED_DATA = SQLBrowseConnect(hconn, inString, inStringLength, outString, DEFAULT_RESULT_SIZE, lenNeeded) Then
       If DEFAULT_RESULT_SIZE < lenNeeded Then
        outString.Capacity = lenNeeded
        If SQL_NEED_DATA <> SQLBrowseConnect(hconn, inString, inStringLength, outString, lenNeeded, lenNeeded) Then
         Throw New ApplicationException("Unabled to aquire SQL Servers from ODBC driver.")
        End If
       End If
       List = outString.ToString()
       Dim start As Integer = List.IndexOf("{") + 1
       Dim len As Integer = list.IndexOf("}") - start
       If (start > 0) AndAlso (len > 0) Then
        list = list.Substring(start, len)
       Else
        list = String.Empty
       End If
      End If
     End If
    End If
   End If
  Catch
   list = String.Empty
  Finally

   If hconn <> IntPtr.Zero Then
    SQLFreeHandle(SQL_HANDLE_DBC, hconn)
   End If

   If henv <> IntPtr.Zero Then
    SQLFreeHandle(SQL_HANDLE_ENV, hconn)
   End If
  End Try

  Dim array As String() = Nothing

  If List.Length > 0 Then


   array = List.Split(","C)
  End If
  Return array
 End Function
End Class

 

 

调用代码

 

Dim ary() as string = SqlLocator.GetLocalSqlServerNamesWithAPI
For Each a As String In ary
    output.show(a)
Next

请教下这个怎样使用,太深了

--  作者:zhuxinhui
--  发布时间:2023/8/22 16:39:00
--  
可以使用,看看如何转化成下拉式进行选择
--  作者:有点蓝
--  发布时间:2023/8/22 16:46:00
--  
我也没看懂。测试了一下出错,用不了,可能玩意是win7用的
--  作者:zhuxinhui
--  发布时间:2023/8/22 17:01:00
--  
十年的代码老师都不记得了
--  作者:zhuxinhui
--  发布时间:2023/8/22 17:10:00
--  

图片点击可在新窗口打开查看此主题相关图片如下:dingtalk_20230822170624.jpg
图片点击可在新窗口打开查看
我测试显示这个出来

--  作者:有点蓝
--  发布时间:2023/8/22 17:12:00
--  
我win10测试出错
--  作者:zhuxinhui
--  发布时间:2023/8/22 17:29:00
--  
我连接了四个数据源的,只有一个显示出来
老师可以帮我调调
太高深了,无法转得过来

--  作者:有点蓝
--  发布时间:2023/8/22 17:32:00
--  
我也不懂