如何取得 SQL Server 的服务器列表

[little_iwf]

关于如何取得 Sql Server 的服务器列表和取得一个服务器的数据库列表
我使用 COM 组件 SQLDMO ,可是在没有安装 SQLServer 的机子上不能运行
有没有其他方法可以做到呢?
恳求帮助

[孟子E章]

http://www.knowsky.com/5847.html

[qimini]

给你贴点代码，不过是C#的


//引入外部DLL
[DllImport("odbc32.dll")]
private static extern short SQLAllocHandle(short hType,
IntPtr inputHandle, out IntPtr outputHandle);
[DllImport("odbc32.dll")]
private static extern short SQLSetEnvAttr(IntPtr henv,
int attribute, IntPtr valuePtr, int strLength);
[DllImport("odbc32.dll")]
private static extern short SQLFreeHandle(short hType, IntPtr handle);
[DllImport("odbc32.dll",CharSet=CharSet.Ansi)]
private static extern short SQLBrowseConnect(IntPtr hconn, StringBuilder inString,
short inStringLength, StringBuilder outString, short outStringLength,
out short outLengthNeeded);
//设置参数
private const short SQL_HANDLE_ENV = 1;
private const short SQL_HANDLE_DBC = 2;
private const int SQL_ATTR_ODBC_VERSION = 200;
private const int SQL_OV_ODBC3 = 3;
private const short SQL_SUCCESS = 0;
private const short SQL_NEED_DATA = 99;
private const short DEFAULT_RESULT_SIZE = 1024;
private const string SQL_DRIVER_STR = "DRIVER=SQL SERVER";
private const string Oracle_DRIVER_STR = "DRIVER=Oracle";

/// <summary>
/// 获取服务器或者服务名列表
/// </summary>
/// <param name="dbType">数据提供程序类型</param>
/// <returns>列表字符串数组</returns>
public static string[] GetDataBaseServer(DataBaseType dbType)
{
//定义返回值
string[] retval = null;
//临时存储服务器列表
ArrayList ar =new ArrayList();

switch(dbType)
{
//处理数据提供程序类型为SQL Server
case DataBaseType.SQLServer:
{
#region "get Sql Server list"
string txt = string.Empty;
IntPtr henv = IntPtr.Zero;
IntPtr hconn = IntPtr.Zero;
//指定类型
StringBuilder inString = new StringBuilder(SQL_DRIVER_STR);
//存储返回结果
StringBuilder outString = new StringBuilder(DEFAULT_RESULT_SIZE);
short inStringLength = (short) inString.Length;
short lenNeeded = 0;
try
{
//取得局域网中同一网段的SQL Server
if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_ENV, henv, out henv))
{
if (SQL_SUCCESS == SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(IntPtr)SQL_OV_ODBC3,0))
{
if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_DBC, henv, out hconn))
{
if (SQL_NEED_DATA == SQLBrowseConnect(hconn, inString, inStringLength, outString, DEFAULT_RESULT_SIZE, out lenNeeded))
{
if (DEFAULT_RESULT_SIZE < lenNeeded)
{
outString.Capacity = lenNeeded;
if (SQL_NEED_DATA != SQLBrowseConnect(hconn, inString, inStringLength, outString, lenNeeded,out lenNeeded))
{
throw new ApplicationException("Unabled to aquire SQL Servers from ODBC driver.");
}
}
//分析数据连接字段
txt = outString.ToString();
int start = txt.IndexOf("{") + 1;
int len = txt.IndexOf("}") - start;
if ((start > 0) && (len > 0))
{
txt = txt.Substring(start,len);
}
else
{
txt = string.Empty;
}
}
}
}
}
}
catch
{ //Throw away any error if we are not in debug mode
txt = string.Empty;
}
finally
{
if (hconn != IntPtr.Zero)
{
SQLFreeHandle(SQL_HANDLE_DBC,hconn);
}
if (henv != IntPtr.Zero)
{
SQLFreeHandle(SQL_HANDLE_ENV,hconn);
}
}
//取得列表
if (txt.Length > 0)
{
retval = txt.Split(",".ToCharArray());
}
#endregion

break;
}
//处理数据提供程序类型为Oracle
case DataBaseType.Oracle:
{
#region "get Oracle Server List"
RegistryKey machineKey = Registry.LocalMachine;
RegistryKey serversKey = machineKey.OpenSubKey(@"SOFTWARE\ORACLE\ALL_HOMES");

if (serversKey != null && serversKey.SubKeyCount > 0)
{

#region "获取Oracle的注册表项"
for(int i = 0;i < serversKey.SubKeyCount ;i++)
{
//打开注册表
RegistryKey serverKey = serversKey.OpenSubKey("ID" + i);

if (serverKey != null && serverKey.ValueCount > 0)
{
//从注册表中取得Oracle配置文件路径
string configFilePath = serverKey.GetValue("PATH") + @"\network\admin\tnsnames.ora";

if (File.Exists(configFilePath))
{
FileStream stream = File.OpenRead(configFilePath);
byte[] byteContent = new byte[(int)stream.Length];

stream.Read(byteContent ,0 ,byteContent.Length);
//读取Oracle配置文件
StringBuilder strContent = new StringBuilder(Encoding.Default.GetString(byteContent ,0 ,byteContent.Length));

int iPosition = 0;

#region "循环读取服务名"
while(iPosition <= strContent.Length)
{
int iState = strContent.ToString().IndexOf("=\r\n (DESCRIPTION",iPosition);
if (iState > 0)
{

int iStartPosition = strContent.ToString().IndexOf("\r\n\r\n",iPosition) + 4;
int iEndPosition = iState -1 ;
//获取列表
ar.Add(strContent.ToString().Substring(iStartPosition,iEndPosition - iStartPosition));

iPosition = iState;

}
iPosition++;
}
#endregion

stream.Close();
}
}
// else
// {
// throw new Exception("没有检测到Oracle客户端！");
// }
}
#endregion
}
// else
// {
// throw new Exception("没有检测到Oracle客户端！");
// }
#endregion
break;
}
//处理数据提供程序类型为ODBC
case DataBaseType.ODBC:
{
#region "get ODBC Server List"
//打开注册表
RegistryKey machineKey = Registry.LocalMachine;
RegistryKey serversKey;
serversKey = machineKey.OpenSubKey(@"SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources");
//获取列表
ar.AddRange(serversKey.GetValueNames());

RegistryKey userKey = Registry.CurrentUser;
serversKey = userKey.OpenSubKey(@"SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources");
//获取列表
ar.AddRange(serversKey.GetValueNames());
#endregion
break;
}
}
//从临时存储变量得到列表值
int iValueCount = ar.Count;
if (iValueCount > 0)
{
retval = new string[iValueCount];
for(int i =0 ;i < iValueCount ;i++)
{
retval[i] = (string) ar[i];
}
}
return retval;
}

[little_iwf]

谢谢楼上

也就是说在没有安装 SQL Server 的机子上没有办法使用 SQLDMO 了?
换句话说就是生成的安装文件不可以在没有安装SQL数据库的机子上安装,是这样吗?

[windsea123]

isql -L
或者 SQLDmo

[little_iwf]

[System.Runtime.InteropServices.DllImport("odbc32.dll")]
private static extern short SQLAllocHandle(short hType, IntPtr inputHandle, out IntPtr outputHandle);
如何转化成 VB.NET 代码?

[hedonister]

上面代码翻译成vb.net（未经测试）
<DllImport("odbc32.dll")> _
Private Shared Function SQLAllocHandle(ByVal hType As Short, ByVal inputHandle As IntPtr, ByRef outputHandle As IntPtr) As Short
End Function

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

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

<DllImport("odbc32.dll"CharSet=CharSet.Ansi)> _
Private Shared Function SQLBrowseConnect(ByVal hconn As IntPtr, ByVal inString As StringBuilder, ByVal inStringLength As Short, ByVal outString As StringBuilder, ByVal 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"
Private Const Oracle_DRIVER_STR As String = "DRIVER=Oracle"

Public Shared Function GetDataBaseServer(ByVal dbType As DataBaseType) As String()
Dim retval As String() = Nothing
Dim ar As ArrayList = New ArrayList
Select dbType
Case DataBaseType.SQLServer
Dim txt As String = String.Empty
Dim henv As IntPtr = IntPtr.Zero
Dim hconn As IntPtr = IntPtr.Zero
Dim inString As StringBuilder = New StringBuilder(SQL_DRIVER_STR)
Dim outString As StringBuilder = New StringBuilder(DEFAULT_RESULT_SIZE)
Dim inStringLength As Short = CType(inString.Length, Short)
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, CType(SQL_OV_ODBC3, IntPtr), 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 Not (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
txt = outString.ToString
Dim start As Integer = txt.IndexOf("{") + 1
Dim len As Integer = txt.IndexOf("}") - start
If (start > 0) AndAlso (len > 0) Then
txt = txt.Substring(start, len)
Else
txt = String.Empty
End If
End If
End If
End If
End If
Catch
txt = String.Empty
Finally
If Not (hconn = IntPtr.Zero) Then
SQLFreeHandle(SQL_HANDLE_DBC, hconn)
End If
If Not (henv = IntPtr.Zero) Then
SQLFreeHandle(SQL_HANDLE_ENV, hconn)
End If
End Try
If txt.Length > 0 Then
retval = txt.Split(",".ToCharArray)
End If
' break
Case DataBaseType.Oracle
Dim machineKey As RegistryKey = Registry.LocalMachine
Dim serversKey As RegistryKey = machineKey.OpenSubKey("SOFTWARE\ORACLE\ALL_HOMES")
If Not (serversKey Is Nothing) AndAlso serversKey.SubKeyCount > 0 Then
Dim i As Integer = 0
While i < serversKey.SubKeyCount
Dim serverKey As RegistryKey = serversKey.OpenSubKey("ID" + i)
If Not (serverKey Is Nothing) AndAlso serverKey.ValueCount > 0 Then
Dim configFilePath As String = serverKey.GetValue("PATH") + "\network\admin\tnsnames.ora"
If File.Exists(configFilePath) Then
Dim stream As FileStream = File.OpenRead(configFilePath)
Dim byteContent(CType(stream.Length, Integer)) As Byte
stream.Read(byteContent, 0, byteContent.Length)
Dim strContent As StringBuilder = New StringBuilder(Encoding.Default.GetString(byteContent, 0, byteContent.Length))
Dim iPosition As Integer = 0
While iPosition <= strContent.Length
Dim iState As Integer = strContent.ToString.IndexOf("=" & Microsoft.VisualBasic.Chr(13) & "" & Microsoft.VisualBasic.Chr(10) & " (DESCRIPTION", iPosition)
If iState > 0 Then
Dim iStartPosition As Integer = strContent.ToString.IndexOf("" & Microsoft.VisualBasic.Chr(13) & "" & Microsoft.VisualBasic.Chr(10) & "" & Microsoft.VisualBasic.Chr(13) & "" & Microsoft.VisualBasic.Chr(10) & "", iPosition) + 4
Dim iEndPosition As Integer = iState - 1
ar.Add(strContent.ToString.Substring(iStartPosition, iEndPosition - iStartPosition))
iPosition = iState
End If
System.Math.Min(System.Threading.Interlocked.Increment(iPosition),iPosition-1)
End While
stream.Close
End If
End If
System.Math.Min(System.Threading.Interlocked.Increment(i),i-1)
End While
End If
' break
Case DataBaseType.ODBC
Dim machineKey As RegistryKey = Registry.LocalMachine
Dim serversKey As RegistryKey
serversKey = machineKey.OpenSubKey("SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources")
ar.AddRange(serversKey.GetValueNames)
Dim userKey As RegistryKey = Registry.CurrentUser
serversKey = userKey.OpenSubKey("SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources")
ar.AddRange(serversKey.GetValueNames)
' break
End Select
Dim iValueCount As Integer = ar.Count
If iValueCount > 0 Then
retval = New String(iValueCount) {}
Dim i As Integer = 0
While i < iValueCount
retval(i) = CType(ar(i), String)
System.Math.Min(System.Threading.Interlocked.Increment(i),i-1)
End While
End If
Return retval
End Function