1 using System; 2 using System.Data.SqlClient; 3 using System.Data; 4 using System.IO; 5 6 namespace NET.Common 7 { 8 /// <summary> 9 /// 数据库操作帮助类 10 /// </summary> 11 public class DatabaseHelper 12 { 13 private const string SQL_DATABASE_BACK = "BACKUP DATABASE {0} TO DISK = '{1}'"; //数据库备份语句 14 private const string SQL_DATABASE_RESTORE = "RESTORE DATABASE {0} FROM DISK = '{1}' WITH REPLACE"; //数据库还原语句 15 private const string SQL_SELECT_ALL_CONN = "SELECT spid FROM master..sysprocesses WHERE dbid=db_id('{0}')"; //获取指定数据库所有链接语句 16 17 /// <summary> 18 /// 实例数据库操作 19 /// </summary> 20 /// <param name="masterConnectionString">master数据库链接字符串</param> 21 public DatabaseHelper(string masterConnectionString) 22 { 23 this.MasterConnectionString = masterConnectionString; 24 } 25 26 /// <summary> 27 /// master数据库链接字符串 28 /// </summary> 29 private string MasterConnectionString { get; set; } 30 31 /// <summary> 32 /// SQLServer数据库备份 33 /// </summary> 34 /// <param name="connectionString">数据库连接字符串</param> 35 /// <param name="backName">备份文件名称,不包含后缀名</param> 36 /// <param name="path">备份文件存放的物理路径</param> 37 public bool Back(string dbName, string backName, string path) 38 { 39 //判断存放备份文件的目录是否存在 40 if (!Directory.Exists(path)) 41 { 42 //不存在,新建目录 43 Directory.CreateDirectory(path); 44 } 45 46 try 47 { 48 using (SqlConnection conn = new SqlConnection(MasterConnectionString)) 49 { 50 //设置存放备份文件的完整物理路径 51 string backPath = path + "\\" + backName + ".bak"; 52 53 //设置数据库备份的T-SQL语句 54 string t_sql_back = string.Format(SQL_DATABASE_BACK, dbName, backPath); 55 56 using (SqlCommand cmd = new SqlCommand(t_sql_back, conn)) 57 { 58 cmd.CommandType = CommandType.Text; 59 conn.Open(); 60 61 //开始备份 62 cmd.ExecuteNonQuery(); 63 64 return true; 65 } 66 } 67 } 68 catch 69 { 70 return false; 71 } 72 } 73 74 /// <summary> 75 /// SQLServer数据库还原 76 /// </summary> 77 /// <param name="dbName">要进行还原的数据库名称</param> 78 /// <param name="fullPath">备份文件的完整物理路径</param> 79 public bool Restore(string dbName, string fullPath) 80 { 81 //判断还原使用的文件是否存在 82 if (!File.Exists(fullPath)) 83 { 84 return false; 85 } 86 87 try 88 { 89 using (SqlConnection conn = new SqlConnection(MasterConnectionString)) 90 { 91 //设置数据库还原的T-SQL语句 92 string t_sql_restore = string.Format(SQL_DATABASE_RESTORE, dbName, fullPath); 93 94 //设置查询指定数据库的所有连接 95 string t_sql_select_all_conn = string.Format(SQL_SELECT_ALL_CONN, dbName); 96 97 string t_sql_kill; 98 99 conn.Open(); 100 101 //获取指定数据库所有连接 102 SqlCommand cmd = new SqlCommand(t_sql_select_all_conn, conn); 103 SqlDataAdapter reSDA = new SqlDataAdapter(cmd); 104 DataTable dtConn = new DataTable(); 105 reSDA.Fill(dtConn); 106 107 //逐一删除连接 108 for (int i = 0; i < dtConn.Rows.Count; i++) 109 { 110 t_sql_kill = " kill " + dtConn.Rows[i][0].ToString(); 111 cmd = new SqlCommand(t_sql_kill, conn); 112 cmd.ExecuteNonQuery(); 113 } 114 115 //执行还原操作 116 cmd = new SqlCommand(t_sql_restore, conn); 117 cmd.ExecuteNonQuery(); 118 119 //清空当前连接池,这个很主要,如果不清空还原后第一次访问会出现“在向服务器发送请求时发生传输级错误。 (provider: 共享内存提供程序, error: 0 - 管道的另一端上无任何进程。)”错误 120 SqlConnection.ClearPool(conn); 121 return true; 122 } 123 124 } 125 catch 126 { 127 return false; 128 } 129 } 130 } 131 }