前面写过一篇《如何用SQLDMO在ASP.NET页面下实现数据库的备份与恢复》的随笔,有朋友希望能多介绍一些SQLDMO的用法。现在,我简单介绍一下在Asp.net中如何用SQLDMO来获取SQL Server中的对象信息。首先我们想到的是所在的局域网内有多少SQL Server服务器,知道了服务器,有了登录的用户名和密码,就可以知道在指定的服务器上有多少张表,视图,存储过程,以及每张表中包含的字段信息等。
1.获取服务器的列表:
获取服务器时,主要是利用了SQLDMO的Application对象,该对象主要有以下几个主要的属性和方法:
方法:ListAvailableSQLServers()
属性:Name
SQLServers
Properties
示例程序:
1
/**/
/// <summary>
2
/// 获取局域网内数据库服务器列表
3
/// </summary>
4
private
void
FormatServerList()
5
![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
6
sqlServers = sqlApp.ListAvailableSQLServers();
7![](http://terrylee.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
8
if(sqlServers != null)
9![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
10
for(int i=0;i<sqlServers.Count;i++)
11![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
12
object srv = sqlServers.Item( i + 1);
13
14![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////添加到服务器的列表中去
15
this.wdrp_ServerList.Items.Add(srv.ToString());
16
}
17
}
18
}
2.获取指定服务器的数据库列表:
这时需要创建一个SQLDMO的SQLServer对象,用它来建立连接,并获取数据库列表,该对象的方法和属性:
方法:Connect(object servername,object Login,object Password)
1
/**/
/// <summary>
2
/// 连接服务器
3
/// </summary>
4
/// <param name="ServerName">服务器名</param>
5
/// <param name="Login">登录名</param>
6
/// <param name="Password">密码</param>
7
public
void
Connect(
object
ServerName,
object
Login,
object
Password)
8
![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
9
10
}
属性:Databases
Name
Login
Password
QueryTimeOut
在获取数据库列表时,利用它的Databases属性,示例代码如下:
1
/**/
/// <summary>
2
/// 获取指定服务器的数据库的列表
3
/// </summary>
4
private
void
FormatDatabaseList()
5
![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
6
SQLDMO.tSQLServer sr = new SQLDMO.SQLServerClass();
7
sr.Connect(this.wdrp_ServerList.SelectedItem.ToString(),txtUserName,txtUserPwd);
8
9![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////创建一个DataTable
10
DataTable dt = new DataTable();
11
dt.Columns.Add("Name");
12
dt.Columns.Add("Owner");
13
dt.Columns.Add("Size");
14
dt.Columns.Add("CreatDate");
15![](http://terrylee.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
16
foreach(Database db in sr.Databases)
17![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
18
if(db.Name != null)
19![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
20
DataRow dr = dt.NewRow();
21![](http://terrylee.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
22![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////获取数据库的名称
23
dr["Name"] = db.Name;
24![](http://terrylee.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
25![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////获取数据库的所有者
26
dr["Owner"] = db.Owner;
27![](http://terrylee.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
28![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////获取数据库的大小
29
dr["Size"] = db.pSize;
30![](http://terrylee.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
31![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////获取数据库的创建日期
32
dr["CreatDate"] = db.CreateDate;
33![](http://terrylee.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
34
dt.Rows.Add(dr);
35
}
36
}
37
38![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////绑定数据
39
this.wgrd_Database.DataSource = dt;
40
this.wgrd_Database.DataBind();
41
}
3.获取数据中的表的集合:
这时需要创建一个SQLDMO的Database对象,利用它的属性和方法来获取,主要的属性和方法:
方法:Item(object Index,object Owner)
1
/**/
/// <summary>
2
/// 获取指定项
3
/// </summary>
4
/// <param name="Index">索引</param>
5
/// <param name="Owner">所有者</param>
6
/// <returns></returns>
7
public
Database Item(
object
Index,
object
Owner)
8
![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
9
10
}
属性:Name
Owner
Size
CreateDate
Tables
Views
StoredProcedures
示例代码如下:
1
/**/
/// <summary>
2
/// 获取指定服务器,指定数据库的表集合
3
/// </summary>
4
private
void
FormatTableList()
5
![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
6
SQLDMO.SQLServer sr = new SQLDMO.SQLServerClass();
7
sr.Connect(this.wdrp_ServerList.SelectedItem.ToString(),txtUserName,txtUserPwd);
8
9![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////创建一个DataTable
10
DataTable dt = new DataTable();
11
dt.Columns.Add("dbName");
12
dt.Columns.Add("Name");
13
dt.Columns.Add("Owner");
14
dt.Columns.Add("CreatDate");
15
dt.Columns.Add("PrimaryKey");
16![](http://terrylee.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
17
for(int j=0;j<sr.Databases.Count;j++)
18![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
19
if(sr.Databases.Item(j+1,"dbo").Name == this.wdrpDatabase.SelectedItem.ToString())
20![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
21
SQLDMO.Database db= sr.Databases.Item(j+1,"dbo");
22![](http://terrylee.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
23
for(int i=0;i<db.Tables.Count;i++)
24![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
25
DataRow dr = dt.NewRow();
26![](http://terrylee.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
27![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////表所属数据库
28
dr["dbName"] = db.Name;
29
30![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////获取表名
31
dr["Name"] = db.Tables.Item(i+1,"dbo").Name;
32![](http://terrylee.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
33![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////获取表的所有者
34
dr["Owner"] = db.Tables.Item(i+1,"dbo").Owner;
35![](http://terrylee.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
36![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////获取表的创建日期
37
dr["CreatDate"] = db.Tables.Item(i+1,"dbo").CreateDate;
38
39![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////获取表的主键
40
dr["PrimaryKey"] = db.Tables.Item(i+1,"dbo").PrimaryKey;
41![](http://terrylee.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
42
dt.Rows.Add(dr);
43
}
44
}
45
}
46
}
4.获取表中的其他信息:
我们看到,在SQLDMO中获取信息时对象是一级一级嵌套使用的,利用各个对象的属性来获取信息。同理,我们在获取表的信息时,同样需要创建一个SQLDMO的Table的对象,它主要的属性和方法:
方法:Item(object Index,object Owner)
1
/**/
/// <summary>
2
/// 获取指定项
3
/// </summary>
4
/// <param name="Index">索引</param>
5
/// <param name="Owner">所有者</param>
6
/// <returns></returns>
7
public
Table Item(
object
Index,
object
Owner)
8
![](http://terrylee.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
9
10
}
属性:Name
Owner
CreateDate
PrimaryKey
Keys
Triggers
Indexs
Rows
Columns
利用这些属性和方法,大家可以很轻松地去获取数据表,字段等信息,这里就不写示例程序了,同样有了这些信息,就可以去写自己的代码生成器了^_^