获取局域网内所有SqlServer
*-----------------------------------------------
* 函数: 获取局域网内所有SqlServer
* 设计: 红雨
* 时间: 2005.04.01
*-----------------------------------------------
Function NetEnumSqlServer( tcTableName )
m.tcTableName = Iif(Type([m.tcTableName])=[C], m.tcTableName, [TNetEnumSqlServer])
Create Cursor (m.tcTableName) ( ServerName C(254) )
Declare SHORT SQLBrowseConnect In odbc32 Integer ConnectionHandle, String InConnectionString, Integer StringLength1, String @ OutConnectionString, Integer BufferLength, Integer @ StringLength2Ptr
Declare SHORT SQLAllocHandle In odbc32 Integer HandleType, Integer InputHandle, Integer @ OutputHandlePtr
Declare SHORT SQLFreeHandle In odbc32 Integer HandleType, Integer Handle
Declare SHORT SQLSetEnvAttr In odbc32 Integer EnvironmentHandle, Integer Attribute, Integer ValuePtr, Integer StringLength Local hEnv, hConn, cInString, cOutString, nLenOutString, nCnt, iCnt
m.nCnt = 0
m.hEnv = 0
m.hConn = 0
m.cInString = "DRIVER=SQL SERVER"
m.cOutString = Space(2048)
m.nLenOutString = 0
Local Array aServerList[1] If SQLAllocHandle(1, 0, @hEnv) = 0
If SQLSetEnvAttr(m.hEnv, 200, 3, 0) = 0
If SQLAllocHandle(2, m.hEnv, @hConn) = 0
If SQLBrowseConnect(m.hConn, @cInString, Len(m.cInString), @cOutString, 2048, @nLenOutString) = 99
m.nCnt = Alines(aServerList, Strextract(m.cOutString, '{', '}'), .T., ',')
For m.iCnt = 1 To m.nCnt
Insert Into (m.tcTableName) Values ( aServerList[iCnt] )
Endfor
Endif
Endif
Endif
Endif
Endfunc
*-----------------------------------------------
* 函数: 获取局域网内所有SqlServer
* 设计: 红雨
* 时间: 2005.04.01
*-----------------------------------------------
Function NetEnumSqlServer( tcTableName )
m.tcTableName = Iif(Type([m.tcTableName])=[C], m.tcTableName, [TNetEnumSqlServer])
Create Cursor (m.tcTableName) ( ServerName C(254) )
Declare SHORT SQLBrowseConnect In odbc32 Integer ConnectionHandle, String InConnectionString, Integer StringLength1, String @ OutConnectionString, Integer BufferLength, Integer @ StringLength2Ptr
Declare SHORT SQLAllocHandle In odbc32 Integer HandleType, Integer InputHandle, Integer @ OutputHandlePtr
Declare SHORT SQLFreeHandle In odbc32 Integer HandleType, Integer Handle
Declare SHORT SQLSetEnvAttr In odbc32 Integer EnvironmentHandle, Integer Attribute, Integer ValuePtr, Integer StringLength Local hEnv, hConn, cInString, cOutString, nLenOutString, nCnt, iCnt
m.nCnt = 0
m.hEnv = 0
m.hConn = 0
m.cInString = "DRIVER=SQL SERVER"
m.cOutString = Space(2048)
m.nLenOutString = 0
Local Array aServerList[1] If SQLAllocHandle(1, 0, @hEnv) = 0
If SQLSetEnvAttr(m.hEnv, 200, 3, 0) = 0
If SQLAllocHandle(2, m.hEnv, @hConn) = 0
If SQLBrowseConnect(m.hConn, @cInString, Len(m.cInString), @cOutString, 2048, @nLenOutString) = 99
m.nCnt = Alines(aServerList, Strextract(m.cOutString, '{', '}'), .T., ',')
For m.iCnt = 1 To m.nCnt
Insert Into (m.tcTableName) Values ( aServerList[iCnt] )
Endfor
Endif
Endif
Endif
Endif
Endfunc
/// <summary>/// 获取网内的数据库服务器名称/// </summary>public class SqlLocator
{ [System.Runtime.InteropServices.DllImport("odbc32.dll")] private static extern short SQLAllocHandle(short hType, IntPtr inputHandle, out IntPtr outputHandle); [System.Runtime.InteropServices.DllImport("odbc32.dll")] private static extern short SQLSetEnvAttr(IntPtr henv, int attribute, IntPtr valuePtr, int strLength); [System.Runtime.InteropServices.DllImport("odbc32.dll")] private static extern short SQLFreeHandle(short hType, IntPtr handle); [System.Runtime.InteropServices.DllImport("odbc32.dll",CharSet= System.Runtime.InteropServices.CharSet.Ansi)] private static extern short SQLBrowseConnect(IntPtr hconn, System.Text.StringBuilder inString, short inStringLength, System.Text.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 SqlLocator(){}
/// <summary> /// 获取网内的数据库服务器名称,是一个字符串数组。 /// </summary> /// <returns></returns> public static string[] GetServers() { string list = string.Empty; IntPtr henv = IntPtr.Zero; IntPtr hconn = IntPtr.Zero;
System.Text.StringBuilder inString = new System.Text.StringBuilder(SQL_DRIVER_STR);
System.Text.StringBuilder outString = new System.Text.StringBuilder(DEFAULT_RESULT_SIZE); short inStringLength = (short) inString.Length;
short lenNeeded = 0;
try {
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.");
}
}
list = outString.ToString();
int start = list.IndexOf("{") + 1;
int len = list.IndexOf("}") - start;
if ((start > 0) && (len > 0))
{
list = list.Substring(start,len);
}
else
{
list = string.Empty;
}
}
}
}
}
}
catch
{
list = string.Empty;
}
finally
{
if (hconn != IntPtr.Zero) {
SQLFreeHandle(SQL_HANDLE_DBC,hconn);
}
if (henv != IntPtr.Zero)
{
SQLFreeHandle(SQL_HANDLE_ENV,hconn);
}
}
string[] array = null;
if (list.Length > 0)
{
array = list.Split(,);
}
return array;
}
}
看看!:)
-----------------
先装一个最新的SP2包,确保SQLDMO.dll是最新的。然后在C#中引用SqlDMO.dll,以下方法将局域网内的可用的SQL server列出到组合框中。
public long listSqlservers(ComboBox servers)
{
SQLDMO.Application sql = new SQLDMO.Application();
SQLDMO.NameList mynamelist = sql.Application.ListAvailableSQLServers();
long lCount = mynamelist.Count;
for (int i=1; i < lCount + 1; i++)
{
string str = mynamelist.Item (i);
if (str != null)
if (servers.FindString (str) == -1)
{
servers.Items.Add (str);
}
}
return lCount;
}
以下方法列出指定SQL server中的所有数据库
public void listdatabase(string servername, string username,string password)
{
SQLDMO.SQLServer s = new SQLDMO.SQLServer();
s.Connect(servername,username,password);
foreach (SQLDMO.Database d in s.Databases)
{
listBox.Item.add(d.Name);
}
}
Dim mySQL As Sql.SqlDataSourceEnumerator = Sql.SqlDataSourceEnumerator.Instance
Return mySQL.GetDataSources
End Function
返回的是DataTable,其中有四个字段ServerName,InstanceName,IsClustered,Version
一般情况下,我们需要的是第一个ServerName,至于其他3个,如果大家有兴趣,可以自己去看看MSDN,我自己并没有深究。
不要告诉我..这个代码..你不会改写成VB.Net引用SQLDMO (COM组件)
包含两个基本对象Application、SQLServer(先实例化)
Application
属性/方法
NameList 保存服务器清单的集合
ListAllAvailableServers 取得网络上可用服务器的方法
SQLServer
属性/方法
Connect 连接SQLServer以便访问数据库
LoginSecure 指定用可信任连接连接SQLServer
Databases 指定SQLServer的数据库集合程序段示例
int i;
SQLDMO.NameList oNames = new SQLDMO.NameList();
SQLDMO.Application oSQLApp= new SQLDMO.Application();
oNames=oSQLApp.ListAvailableSQLServers();
if (oNames.Count==0)
{
Listbox1.Items.Add("(local)");
}
for (i=1;i=oNames.Count;i++)
{
Listbox1.Items.Add(oNames.Item(i));
}
===============================
Object db;
SQLDMO.SQLServer osvr=new SQLDMO.SQLServer();
osvr.LoginSecure=true;
osvr.Connect(strSQLServer);//这个字符串就是上面的listitem
foreach (db in osvr.Databases)
{
Listbox2.Items.Add(db.Name);
}