C#实现杀掉MSSQL中数据库当前连接的所有进程（进行数据还原的时候需要用到）

//不好意思，上次少了句最重要的话，光找到没有KILL了^_^ //后来在网上发现有两种方式实现SQLSERVER2000数据库备份还原，也一起放到下面了 //结束目标数据库的所有当前用户的连接 SqlConnection conn = new SqlConnection(); conn.C; conn.Open(); //获取连接用户 string sql = "SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name= '" + databaseName + "'"; SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader dr; ArrayList list = new ArrayList(); try { dr = cmd.ExecuteReader(); while (dr.Read()) { list.Add(dr.GetInt16(0)); } dr.Close(); for (int i = 0; i < list.Count; i++) { cmd = new SqlCommand(string.Format("KILL {0}", list), conn); try { cmd.ExecuteNonQuery(); } catch (Exception eee) { throw eee; } } } catch (Exception eee) { throw eee; } finally { conn.Close(); } /******************************************************************************************/ C#实现SQLSERVER2000数据库备份还原的两种方法 方法一（不使用SQLDMO）： /// ///备份方法 /// SqlConnection conn = new SqlConnection("Server=.;Database=master;User ID=sa;Password=sa;"); SqlCommand cmdBK = new SqlCommand(); cmdBK.CommandType = CommandType.Text; cmdBK.Connection = conn; cmdBK.CommandText = @"backup database test to disk='C:/ba' with init"; try { conn.Open(); cmdBK.ExecuteNonQuery(); MessageBox.Show("Backup successed."); } catch(Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); conn.Dispose(); } /// ///还原方法 /// SqlConnection conn = new SqlConnection("Server=.;Database=master;User ID=sa;Password=sa;Trusted_Connection=False"); conn.Open(); //KILL DataBase Process SqlCommand cmd = new SqlCommand("SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='test'", conn); SqlDataReader dr; dr = cmd.ExecuteReader(); ArrayList list = new ArrayList(); while(dr.Read()) { list.Add(dr.GetInt16(0)); } dr.Close(); for(int i = 0; i < list.Count; i++) { cmd = new SqlCommand(string.Format("KILL {0}", list), conn); cmd.ExecuteNonQuery(); } SqlCommand cmdRT = new SqlCommand(); cmdRT.CommandType = CommandType.Text; cmdRT.Connection = conn; cmdRT.CommandText = @"restore database test from disk='C:/ba'"; try { cmdRT.ExecuteNonQuery(); MessageBox.Show("Restore successed."); } catch(Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } 方法二（使用SQLDMO）： /// ///备份方法 ///添加的引用在com下面的以Micosoft SQLDMO object for...开头的dll文件 /// SQLDMO.Backup backup = new SQLDMO.BackupClass(); SQLDMO.SQLServer server = new SQLDMO.SQLServerClass(); //显示进度条 SQLDMO.BackupSink_PercentCompleteEventHandler progress = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step); backup.PercentComplete += progress; try { server.LoginSecure = false; server.Connect(".", "sa", "sa"); backup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database; backup.Database = "test"; backup.Files = @"D:/test/myProg/backupTest"; backup.BackupSetName = "test"; backup.BackupSetDescription = "Backup the database of test"; backup.Initialize = true; backup.SQLBackup(server); MessageBox.Show("Backup successed."); } catch(Exception ex) { MessageBox.Show(ex.Message); } finally { server.DisConnect(); } this.pbDB.Value = 0; /// ///还原方法 /// SQLDMO.Restore restore = new SQLDMO.RestoreClass(); SQLDMO.SQLServer server = new SQLDMO.SQLServerClass(); //显示进度条 SQLDMO.RestoreSink_PercentCompleteEventHandler progress = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step); restore.PercentComplete += progress; //KILL DataBase Process SqlConnection conn = new SqlConnection("Server=.;Database=master;User ID=sa;Password=sa;Trusted_Connection=False"); conn.Open(); SqlCommand cmd = new SqlCommand("SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='test'", conn); SqlDataReader dr; dr = cmd.ExecuteReader(); ArrayList list = new ArrayList(); while(dr.Read()) { list.Add(dr.GetInt16(0)); } dr.Close(); for(int i = 0; i < list.Count; i++) { cmd = new SqlCommand(string.Format("KILL {0}", list), conn); cmd.ExecuteNonQuery(); } conn.Close(); try { server.LoginSecure = false; server.Connect(".", "sa", "sa"); restore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database; restore.Database = "test"; restore.Files = @"D:/test/myProg/backupTest"; restore.FileNumber = 1; restore.ReplaceDatabase = true; restore.SQLRestore(server); MessageBox.Show("Restore successed."); } catch(Exception ex) { MessageBox.Show(ex.Message); } finally { server.DisConnect(); } this.pbDB.Value = 0; /******************************************************************************************/