.NET 获取局域网的sql server 列表

SqlDataSourceEnumerator instance =SqlDataSourceEnumerator.Instance;
System.Data.DataTable table = instance.GetDataSources();

或

 

System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources();

 

返回Datatable

 

此table包含以下四个字段

Console.WriteLine("服务器名 = {0}", row["ServerName"]);
Console.WriteLine("实例名 = {0}", row["InstanceName"]);
Console.WriteLine("是否集成验证 = {0}", row["IsClustered"]);//即Windows身份验证和SQL Server验证
Console.WriteLine("版本 = {0}", row["Version"]);//8.*是SQL 2000,9.*是SQL 2005

 

 

 

方法一：通过Microsoft.SQLDMO.Object组件就可以轻松完成此项工作：
首先如何找到Microsoft.SQLDMO.Object
1.如何在您得项目中能够使用SQLDMO组件？
菜单－项目－添加引用－COM-Microsoft.SQLDMO.Object

 

 

 

2.将该功能写成一个类：

using System;
using System.Collections;
using System.Collections.Specialized;

namespace JillZhang
{
    /** <summary>
    /// Summary description for SqlInfo.
    /// </summary>
    public class SqlInfo
    {
        成员变量#region 成员变量
        private string _uid="";
        private string _pwd="";
        private StringCollection _serverList=new StringCollection();
        private Hashtable _databaseList=new Hashtable();
        #endregion

        构造函数#region 构造函数
        public SqlInfo()
        {
            this._serverList=GetSqlInstances();   
            if(this._serverList.Count>0)
            {
                foreach(string item in this._serverList)
                {
                    this._databaseList.Add(item,this.GetAllDatabases(item));
                }
            }
        }
        public SqlInfo(string uid,string pwd)
        {
            this._uid=uid;
            this._pwd=pwd;
            this._serverList=GetSqlInstances();   
            if(this._serverList.Count>0)
            {
                foreach(string item in this._serverList)
                {
                    this._databaseList.Add(item,this.GetAllDatabases(item));
                }
            }
        }
        #endregion

        公共属性#region 公共属性
        /** <summary>
        /// 服务列表
        /// </summary>
        public StringCollection ServerList
        {
            get
            {
                return _serverList;
            }
        }
        /** <summary>
        /// 用于登录Sql server得用户名
        /// </summary>
        public string Uid
        {
            get
            {
                return _uid;
            }
            set
            {
                _uid=value;
            }
        }
        /** <summary>
        /// 用于登录得密码
        /// </summary>
        public string Pwd
        {
            get
            {
                return _pwd;
            }
            set
            {
                _pwd=value;
            }
        }
        #endregion

        事件方法#region 事件方法
        /** <summary>
        /// 获得服务列表
        /// </summary>
        /// <returns></returns>
        public static System.Collections.Specialized.StringCollection GetSqlInstances()
        {
            //声明一个字符串链表，用于存放列表信息
            System.Collections.Specialized.StringCollection instaces= new System.Collections.Specialized.StringCollection();
            try
            {
                //以下代码是通过调用SQLDMO组件来实现获得服务列表
                SQLDMO.Application sqlApplication= new SQLDMO.ApplicationClass();
                SQLDMO.NameList sqlServerIntances=sqlApplication.ListAvailableSQLServers();
                for(int i=0;i<sqlServerIntances.Count;i++)
                {
                    object svr=sqlServerIntances.Item(i+1);//索引从1开始
                    if(svr!=null)
                    {
                        instaces.Add(svr.ToString());
                    }
                }
                return instaces;
            }
            catch
            {
                throw new Exception("未能获得Server得列表信息，请查看您得Sql server是否正在运行！");
            }
        }

        /** <summary>
        /// 获得Sqlserver 2000一个Server Instance上得数据库列表
        /// </summary>
        /// <param name="server">服务名称</param>
        /// <param name="uid">登录用户</param>
        /// <param name="pwd">登录密码</param>
        /// <returns>数据库列表</returns>
        public static System.Collections.Specialized.StringCollection GetAllDatabases(string server,string uid,string pwd)
        {
            System.Collections.Specialized.StringCollection databases= new System.Collections.Specialized.StringCollection();
            try
            {
                SQLDMO.SQLServer sqlServer =new  SQLDMO.SQLServerClass();
                sqlServer.Connect(server,uid,pwd);
                foreach(SQLDMO.Database db in sqlServer.Databases)
                {
                    if(db.Name!=null)
                    {
                        databases.Add(db.Name);
                    }
                }
                return databases;
            }
            catch
            {
                throw new Exception("未能获得服务"+server+"上得数据库列表，可能您得服务器没有启动或者您得用户名或密码错误");
            }
        }
        public System.Collections.Specialized.StringCollection GetAllDatabases(string server)
        {
            return GetAllDatabases(server,this._uid,this._pwd);
        }
        #endregion
       
    }
}

方法二：运行SQL语句

select name from master..sysdatabases order by name asc

SqlConnection conn = new SqlConnection(connstr);
SqlDataReader dr;
SqlCommand comm = new SqlCommand("select name from master..sysdatabases order by name asc", connection);
conn.Open();
conn.ChangeDatabase("master");
dr = comm.ExecuteReader();

 

 

查看表

用sp_tables看表