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
}
