在Asp.net中如何用SQLDMO来获取SQL Server中的对象

我们知道，用SQLDMO可以实现对数据库的备份与恢复，下面给出简单的实现方法。
首先需要添加对SQLDMO引用


1.实现数据库的备份：
1/**

2          /// 数据库备份
3          ///
4          /// 备份是否成功
5          public bool DbBackup()
6          {
7              string path = CreatePath();
8              SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
9              SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
10              try
11              {
12                  oSQLServer.LoginSecure = false;
13                  oSQLServer.Connect(server,uid, pwd);
14                  oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
15                  oBackup.Database = database;
16                  oBackup.Files = path;
17                  oBackup.BackupSetName = database;
18                  oBackup.BackupSetDescription = "数据库备份";
19                  oBackup.Initialize = true;
20                  oBackup.SQLBackup(oSQLServer);
21
22                  return true;
23              }
24              catch(Exception ex)
25              {
26                  return false;
27                  throw ex;
28              }
29              finally
30              {
31                  oSQLServer.DisConnect();
32              }
33          }
2.实现数据库恢复：
在恢复时要注意先杀掉当前数据库的所有进程
1/**

2          /// 数据库恢复
3          ///
4          public string DbRestore()
5          {
6              if(exepro()!=true)//执行存储过程
7              {
8                  return "操作失败";
9              }
10              else
11              {
12                  SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
13                  SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
14                  try
15                  {
16                      exepro();
17                      oSQLServer.LoginSecure = false;
18                      oSQLServer.Connect(server, uid, pwd);
19                      oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
20                      oRestore.Database = database;
21                      /**自行修改
22                      oRestore.Files = @"d:/aaa/aaa.bak";
23                      oRestore.FileNumber = 1;
24                      oRestore.ReplaceDatabase = true;
25                      oRestore.SQLRestore(oSQLServer);
26
27                      return "ok";
28                  }
29                  catch(Exception e)
30                  {
31                      return "恢复数据库失败";
32                      throw e;
33                  }
34                  finally
35                  {
36                      oSQLServer.DisConnect();
37                  }
38              }
39          }
40        
41          /**

42          /// 杀死当前库的所有进程
43          ///
44          ///
45          private bool exepro()
46          {
47
48              SqlConnection conn1 = new SqlConnection("server="+server+";uid="+uid+";pwd="+pwd+";database=master");
49              SqlCommand cmd = new SqlCommand("killspid",conn1);
50              cmd.CommandType = CommandType.StoredProcedure;
51              cmd.Parameters.Add("@dbname","aaa");
52              try
53              {
54                  conn1.Open();
55                  cmd.ExecuteNonQuery();
56                  return true;
57              }
58              catch(Exception ex)
59              {
60                  return false;
61              }
62              finally
63              {
64                  conn1.Close();
65              }
66          }
完整的操作类如下：
    1using System;
    2using System.Collections;
    3using System.Data;
    4using System.Data.SqlClient;
    5
    6namespace DbBackUp
    7{
    8      /**

    9      /// 创建人：Terrylee
10      /// 创建时间：2005年8月1日
11      /// 功能描述：实现数据库的备份和还原
12      /// 更新记录：
13      ///
14      public class DbOperate
15      {    
16          /**

17          /// 服务器
18          ///
19          private string server;
20        
21          /**

22          /// 登录名
23          ///
24          private string uid;
25        
26          /**

27          /// 登录密码
28          ///
29          private string pwd;
30        
31          /**

32          /// 要操作的数据库
33          ///
34          private string database;
35        
36          /**

37          /// 数据库连接字符串
38          ///
39          private string conn;
40
41          /**

42          /// DbOperate类的构造函数
43          /// 在这里进行字符串的切割，获取服务器，登录名，密码，数据库
44          ///
45          public DbOperate()
46          {
47              conn = System.Configuration.ConfigurationSettings.AppSettings["constr"].ToString();
48              server = StringCut(conn,"server=",";");
49              uid = StringCut(conn,"uid=",";");
50              pwd = StringCut(conn,"pwd=",";");
51              database = StringCut(conn,"database=",";");
52          }
53        
54          /**

55          /// 切割字符串
56          ///
57          ///
58          ///
59          ///
60          ///
61          public string StringCut(string str,string bg,string ed)
62          {
63              string sub;
64              sub=str.Substring(str.IndexOf(bg)+bg.Length);
65              sub=sub.Substring(0,sub.IndexOf(";"));
66              return sub;
67          }
68        
69          /**

70          /// 构造文件名
71          ///
72          /// 文件名
73          private string CreatePath()
74          {
75              string CurrTime = System.DateTime.Now.ToString();
76              CurrTime = CurrTime.Replace("-","");
77              CurrTime = CurrTime.Replace(":","");
78              CurrTime = CurrTime.Replace(" ","");
79              CurrTime = CurrTime.Substring(0,12);
80              string path = @"d://aaa//";
81              path += database;
82              path += "_db_";
83              path += CurrTime;
84              path += ".BAK";
85              return path;
86          }
87
88          /**

89          /// 数据库备份
90          ///
91          /// 备份是否成功
92          public bool DbBackup()
93          {
94              string path = CreatePath();
95              SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
96              SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
97              try
98              {
99                  oSQLServer.LoginSecure = false;
100                  oSQLServer.Connect(server,uid, pwd);
101                  oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
102                  oBackup.Database = database;
103                  oBackup.Files = path;
104                  oBackup.BackupSetName = database;
105                  oBackup.BackupSetDescription = "数据库备份";
106                  oBackup.Initialize = true;
107                  oBackup.SQLBackup(oSQLServer);
108
109                  return true;
110              }
111              catch(Exception ex)
112              {
113                  return false;
114                  throw ex;
115              }
116              finally
117              {
118                  oSQLServer.DisConnect();
119              }
120          }
121
122          /**

123          /// 数据库恢复
124          ///
125          public string DbRestore()
126          {
127              if(exepro()!=true)//执行存储过程
128              {
129                  return "操作失败";
130              }
131              else
132              {
133                  SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
134                  SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
135                  try
136                  {
137                      exepro();
138                      oSQLServer.LoginSecure = false;
139                      oSQLServer.Connect(server, uid, pwd);
140                      oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
141                      oRestore.Database = database;
142                      /**自行修改
143                      oRestore.Files = @"d:/aaa/aaa.bak";
144                      oRestore.FileNumber = 1;
145                      oRestore.ReplaceDatabase = true;
146                      oRestore.SQLRestore(oSQLServer);
147
148                      return "ok";
149                  }
150                  catch(Exception e)
151                  {
152                      return "恢复数据库失败";
153                      throw e;
154                  }
155                  finally
156                  {
157                      oSQLServer.DisConnect();
158                  }
159              }
160          }
161        
162          /**

163          /// 杀死当前库的所有进程
164          ///
165          ///
166          private bool exepro()
167          {
168
169              SqlConnection conn1 = new SqlConnection("server="+server+";uid="+uid+";pwd="+pwd+";database=master");
170              SqlCommand cmd = new SqlCommand("killspid",conn1);
171              cmd.CommandType = CommandType.StoredProcedure;
172              cmd.Parameters.Add("@dbname","aaa");
173              try
174              {
175                  conn1.Open();
176                  cmd.ExecuteNonQuery();
177                  return true;
178              }
179              catch(Exception ex)
180              {
181                  return false;
182              }
183              finally
184              {
185                  conn1.Close();
186              }
187          }
188
189      }
190
191}
192
在相应的按钮
1 单击事件里调用即可：
1/**

2          /// 备份按钮
3          ///
4          ///
5          ///
6          private void wbtn_Backup_Click(object sender, System.EventArgs e)
7          {
8              DbOperate dbop = new DbOperate();
9              dbop.DbBackup();
10          }

程序代码：

[ 复制代码到剪贴板 ]

CREATE PROCEDURE killspid
/*----------------------------------
创建人：Terrylee
创建事件：2005年8月1日
功能说明：
参数说明：
----------------------------------*/
(
@dbname varchar(20) --数据库的名称
)
AS
BEGIN
DECLARE @sql nvarchar(500)
DECLARE @spid int
SET @sql=’declare getspid cursor for select spid from sysprocesses where dbid=db_id(’’’+@dbname+’’’)’
EXECUTE (@sql)
OPEN getspid
FETCH NEXT FROM getspid INTO @spid
WHILE @@fetch_status<>-1
    BEGIN
     EXECUTE(’kill ’+@spid)
     FETCH NEXT FROM getspid INTO @spid
    END
CLOSE getspid
DEALLOCATE getspid
END
GO