在日常工作中，经常要分离和附加数据库，sqlserver自带的分离和附加工具，每次使用都非常不方便，非常慢，写了个数据库分离附加工具，以下是部分核心代码：
public class OperDBforSqlServer
{
//使用集成连接数据库
private static string ConnectionString="workstation id=\""+Dns.GetHostName()+"\";integrated security=SSPI;data source=\"" +
".\";persist security info=False;Initial Catalog=master";
private static void StartSqlServer()
{
ProcessStartInfo info=new ProcessStartInfo("net.exe","net start mssqlserver");
info.CreateNoWindow=true;
info.WindowStyle=ProcessWindowStyle.Hidden;
Process ps=Process.Start(info);
#region 注释
// Process[] process = Process.GetProcessesByName("sqlservr");
// foreach (Process inpor in process)
// {
// return;//如果sql服务已经启动则返回
// }
// Process MyProcess= new Process();
// //设定程序名
// MyProcess.StartInfo.FileName = "cmd.exe";
// //关闭Shell的使用
// MyProcess.StartInfo.UseShellExecute = false;
// //重定向标准输入
// MyProcess.StartInfo.RedirectStandardInput = true;
// //重定向标准输出
// MyProcess.StartInfo.RedirectStandardOutput = true;
// //重定向错误输出
// MyProcess.StartInfo.RedirectStandardError = true;
// //设置不显示窗口
// MyProcess.StartInfo.CreateNoWindow = true;
// //启动进程
// MyProcess.Start();
// //执行命令启动服务
// MyProcess.StandardInput.WriteLine("net start mssqlserver");
// //退出进程
// MyProcess.StandardInput.WriteLine("exit");
// // 等待200000毫秒让sql服务成功启动
// Thread.Sleep(20000);
// bool bl=true;
// Process[] process2 = Process.GetProcessesByName("sqlservr");
// foreach (Process inpor in process2)
// {
// bl=false;// 再次检查sql服务是否已经成功启动
// }
// if(bl)//如果sql服务还没有启动则抛出异常
// {
// throw new ApplicationException("无法启动sql服务！");
// }
#endregion
}
private static void EndSqlServer()
{
ProcessStartInfo info = new ProcessStartInfo("net.exe", "net stop mssqlserver");
info.CreateNoWindow = true;
info.WindowStyle = ProcessWindowStyle.Hidden;
Process ps = Process.Start(info);
}
/// <summary>
/// 附加数据库
/// </summary>
/// <param name="DbName">将数据库附加为的名字</param>
/// <param name="Mdf">MDF</param>
/// <param name="Ldf">LDF</param>
/// <param name="Path">该参数为（Application.StartupPath）即当前运行程序的路径</param>
/// <returns>附加成功返回真</returns>
public static bool AddDataBase(string DbName,string path_Mdf,string path_Ldf)
{
bool bl=false;
StartSqlServer();//启动sql服务
try
{
SqlConnection Conn=new SqlConnection(ConnectionString);
Conn.Open();
//sp_detach_db分离数据，sp_attach_db附加数据
string ComText="IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'"+DbName+"')"+
"begin EXEC sp_detach_db "+DbName+" end "+//如果数据库已经存在则先分离
"EXEC sp_attach_db @dbname = N'"+DbName+"',"+
"@filename1 = N'"+path_Mdf+"',"+
"@filename2 = N'"+path_Ldf+"'";
SqlCommand Comm = new SqlCommand(ComText,Conn);
Comm.ExecuteNonQuery();
Conn.Close();
bl=true;
}
catch(Exception ex)
{
throw new ApplicationException("附加数据库失败",ex);
}
return bl;
}
public static bool ChangeDataBase(string DbName)
{
bool result = false;
try
{
SqlConnection Conn = new SqlConnection(ConnectionString);
Conn.Open();
//切换数据库到master数据库
string ComText = @"USE master;DECLARE @s varchar(8000);SET @s = '';
SELECT
@s = @s + ';KILL ' + RTRIM(spid)
FROM(
SELECT DISTINCT
spid
FROM master.dbo.sysprocesses
WHERE spid > 50
AND dbid = DB_ID(N'"+DbName+"'))A; EXEC(@s);";
SqlCommand Comm = new SqlCommand(ComText, Conn);
Comm.ExecuteNonQuery();
Conn.Close();
result = true;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return result;
}
/// <summary>
/// 分离数据库
/// </summary>
/// <param name="DbName">要分离的数据库名</param>
/// <returns>分离成功返回真</returns>
public static bool SeverDataBase(string DbName)
{
bool bl=false;
//EndSqlServer();
StartSqlServer();//启动sql服务
try
{
SqlConnection Conn=new SqlConnection(ConnectionString);
Conn.Open();
//sp_detach_db分离数据，sp_attach_db附加数据
string ComText = "use master; EXEC sp_detach_db " + DbName;
SqlCommand Comm = new SqlCommand(ComText, Conn);
Comm.ExecuteNonQuery();
Conn.Close();
bl=true;
}
catch(Exception ex)
{
//throw new ApplicationException("分离数据库失败",ex);
throw new ApplicationException(ex.Message);
}
return bl;
}
/// <summary>
/// 获取系统数据库
/// </summary>
/// <returns></returns>
public static DataSet GetSysDataBase()
{
DataSet ds = new DataSet();
try
{
string strsql = @"select name from sys.databases where name not in
('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')";
SqlConnection Conn = new SqlConnection(ConnectionString);
SqlDataAdapter ada = new SqlDataAdapter(strsql, Conn);
ada.Fill(ds);
}
catch (Exception ex)
{
throw new ApplicationException(ex.Message);
}
return ds;
}
/// <summary>
/// 备份数据库
/// </summary>
/// <param name="DbName">要备份的数据库名</param>
/// <param name="BackupPath_name">备份设备（路径+文件.bak）</param>
/// <returns> 备份成功返回真</returns>
public static bool BackupDataBase(string DbName,string BackupPath_name)
{
bool bl=false;
//如果看不懂下面的代码请不要随便改动
StartSqlServer();//启动sql服务
try
{
SqlConnection Conn=new SqlConnection(ConnectionString);
Conn.Open();
//sp_detach_db分离数据，sp_attach_db附加数据
string ComText="use master;backup database "+DbName+" to disk = '"+BackupPath_name+"'";
SqlCommand Comm = new SqlCommand(ComText,Conn);
Comm.ExecuteNonQuery();
Conn.Close();
bl=true;
}
catch(Exception ex)
{
throw new ApplicationException("备份数据库失败",ex);
}
return bl;
}
/// <summary>
/// 还原数据库
/// </summary>
/// <param name="BackupPath_name">数据源（路径+文件）</param>
/// <returns>还原成功返回真</returns>
public static bool ReplaceDataBase(string DBName, string BackupPath_name)
{
bool bl=false;
StartSqlServer();//启动sql服务
try
{
SqlConnection Conn=new SqlConnection(ConnectionString);
Conn.Open();
string ComText="use master;restore database "+DBName+" From disk = '"+BackupPath_name+"' with replace;";
SqlCommand Comm = new SqlCommand(ComText,Conn);
Comm.ExecuteNonQuery();
Conn.Close();
bl=true;
}
catch(Exception ex)
{
throw new ApplicationException("还原数据库失败",ex);
}
return bl;
}
}
}