.net 获得 MSSQL Server 数据库列表

efn阿克说

方法一：通过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();