1
using
System;
2
using
System.Configuration;
3
using
System.Data;
4
using
System.Data.SqlClient;
5
using
System.Collections;
6
7
namespace
Logindemo.SQLserver
8
...
{
9
/**//// download from www.51aspx.com(51aspx.com)
10
11
/// <summary>
12
/// SqlHelper类是专门提供给广大用户用于高性能、可升级和最佳练习的sql数据操作
13
/// </summary>
14
public abstract class SqlHelper
15
...{
16
17
//数据库连接字符串
18
public static readonly string Con = System.Configuration.ConfigurationManager.AppSettings["SQLServer"];
19
20
// 用于缓存参数的HASH表
21
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
22
23
/**//// <summary>
24
/// 给定连接的数据库用假设参数执行一个sql命令(不返回数据集)
25
/// </summary>
26
/// <param name="connectionString">一个有效的连接字符串</param>
27
/// <param name="commandType">命令类型(存储过程, 文本, 等等)</param>
28
/// <param name="commandText">存储过程名称或者sql命令语句</param>
29
/// <param name="commandParameters">执行命令所用参数的集合</param>
30
/// <returns>执行命令所影响的行数</returns>
31
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
32
...{
33
34
SqlCommand cmd = new SqlCommand();
35
36
using (SqlConnection conn = new SqlConnection(connectionString))
37
...{
38
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
39
int val = cmd.ExecuteNonQuery();
40
cmd.Parameters.Clear();
41
return val;
42
}
43
}
44
45
/**//// <summary>
46
/// 用现有的数据库连接执行一个sql命令(不返回数据集)
47
/// </summary>
48
/// <param name="conn">一个现有的数据库连接</param>
49
/// <param name="commandType">命令类型(存储过程, 文本, 等等)</param>
50
/// <param name="commandText">存储过程名称或者sql命令语句</param>
51
/// <param name="commandParameters">执行命令所用参数的集合</param>
52
/// <returns>执行命令所影响的行数</returns>
53
public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
54
...{
55
56
SqlCommand cmd = new SqlCommand();
57
58
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
59
int val = cmd.ExecuteNonQuery();
60
cmd.Parameters.Clear();
61
return val;
62
}
63
64
/**//// <summary>
65
///使用现有的SQL事务执行一个sql命令(不返回数据集)
66
/// </summary>
67
/// <remarks>
68
///举例:
69
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
70
/// </remarks>
71
/// <param name="trans">一个现有的事务</param>
72
/// <param name="commandType">命令类型(存储过程, 文本, 等等)</param>
73
/// <param name="commandText">存储过程名称或者sql命令语句</param>
74
/// <param name="commandParameters">执行命令所用参数的集合</param>
75
/// <returns>执行命令所影响的行数</returns>
76
public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
77
...{
78
SqlCommand cmd = new SqlCommand();
79
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
80
int val = cmd.ExecuteNonQuery();
81
cmd.Parameters.Clear();
82
return val;
83
}
84
85
/**//// <summary>
86
/// 用执行的数据库连接执行一个返回数据集的sql命令
87
/// </summary>
88
/// <remarks>
89
/// 举例:
90
/// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
91
/// </remarks>
92
/// <param name="connectionString">一个有效的连接字符串</param>
93
/// <param name="commandType">命令类型(存储过程, 文本, 等等)</param>
94
/// <param name="commandText">存储过程名称或者sql命令语句</param>
95
/// <param name="commandParameters">执行命令所用参数的集合</param>
96
/// <returns>包含结果的读取器</returns>
97
public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
98
...{
99
//创建一个SqlCommand对象
100
SqlCommand cmd = new SqlCommand();
101
//创建一个SqlConnection对象
102
SqlConnection conn = new SqlConnection(connectionString);
103
104
//在这里我们用一个try/catch结构执行sql文本命令/存储过程,因为如果这个方法产生一个异常我们要关闭连接,因为没有读取器存在,
105
//因此commandBehaviour.CloseConnection 就不会执行
106
try
107
...{
108
//调用 PrepareCommand 方法,对 SqlCommand 对象设置参数
109
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
110
//调用 SqlCommand 的 ExecuteReader 方法
111
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
112
//清除参数
113
cmd.Parameters.Clear();
114
return reader;
115
}
116
catch
117
...{
118
//关闭连接,抛出异常
119
conn.Close();
120
throw;
121
}
122
}
123
124
/**//// <summary>
125
/// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列
126
/// </summary>
127
/// <remarks>
128
///例如:
129
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
130
/// </remarks>
131
///<param name="connectionString">一个有效的连接字符串</param>
132
/// <param name="commandType">命令类型(存储过程, 文本, 等等)</param>
133
/// <param name="commandText">存储过程名称或者sql命令语句</param>
134
/// <param name="commandParameters">执行命令所用参数的集合</param>
135
/// <returns>用 Convert.To{Type}把类型转换为想要的 </returns>
136
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
137
...{
138
SqlCommand cmd = new SqlCommand();
139
140
using (SqlConnection connection = new SqlConnection(connectionString))
141
...{
142
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
143
object val = cmd.ExecuteScalar();
144
cmd.Parameters.Clear();
145
return val;
146
}
147
}
148
149
/**//// <summary>
150
/// 用指定的数据库连接执行一个命令并返回一个数据集的第一列
151
/// </summary>
152
/// <remarks>
153
/// 例如:
154
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
155
/// </remarks>
156
/// <param name="conn">一个存在的数据库连接</param>
157
/// <param name="commandType">命令类型(存储过程, 文本, 等等)</param>
158
/// <param name="commandText">存储过程名称或者sql命令语句</param>
159
/// <param name="commandParameters">执行命令所用参数的集合</param>
160
/// <returns>用 Convert.To{Type}把类型转换为想要的 </returns>
161
public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
162
...{
163
164
SqlCommand cmd = new SqlCommand();
165
166
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
167
object val = cmd.ExecuteScalar();
168
cmd.Parameters.Clear();
169
return val;
170
}
171
172
/**//// <summary>
173
/// 将参数集合添加到缓存
174
/// </summary>
175
/// <param name="cacheKey">添加到缓存的变量</param>
176
/// <param name="cmdParms">一个将要添加到缓存的sql参数集合</param>
177
public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
178
...{
179
parmCache[cacheKey] = commandParameters;
180
}
181
182
/**//// <summary>
183
/// 找回缓存参数集合
184
/// </summary>
185
/// <param name="cacheKey">用于找回参数的关键字</param>
186
/// <returns>缓存的参数集合</returns>
187
public static SqlParameter[] GetCachedParameters(string cacheKey)
188
...{
189
SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];
190
191
if (cachedParms == null)
192
return null;
193
194
SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
195
196
for (int i = 0, j = cachedParms.Length; i < j; i++)
197
clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
198
199
return clonedParms;
200
}
201
202
/**//// <summary>
203
/// 准备执行一个命令
204
/// </summary>
205
/// <param name="cmd">sql命令</param>
206
/// <param name="conn">Sql连接</param>
207
/// <param name="trans">Sql事务</param>
208
/// <param name="cmdType">命令类型例如 存储过程或者文本</param>
209
/// <param name="cmdText">命令文本,例如:Select * from Products</param>
210
/// <param name="cmdParms">执行命令的参数</param>
211
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
212
...{
213
214
if (conn.State != ConnectionState.Open)
215
conn.Open();
216
217
cmd.Connection = conn;
218
cmd.CommandText = cmdText;
219
220
if (trans != null)
221
cmd.Transaction = trans;
222
223
cmd.CommandType = cmdType;
224
225
if (cmdParms != null)
226
...{
227
foreach (SqlParameter parm in cmdParms)
228
cmd.Parameters.Add(parm);
229
}
230
}
231
}
232
}

2

3

4


6

7

8


9


10

11

12

13

14

15


16

17

18

19

20

21

22

23


24

25

26

27

28

29

30

31

32


33

34

35

36

37


38

39

40

41

42

43

44

45


46

47

48

49

50

51

52

53

54


55

56

57

58

59

60

61

62

63

64


65

66

67

68

69

70

71

72

73

74

75

76

77


78

79

80

81

82

83

84

85


86

87

88

89

90

91

92

93

94

95

96

97

98


99

100

101

102

103

104

105

106

107


108

109

110

111

112

113

114

115

116

117


118

119

120

121

122

123

124


125

126

127

128

129

130

131

132

133

134

135

136

137


138

139

140

141


142

143

144

145

146

147

148

149


150

151

152

153

154

155

156

157

158

159

160

161

162


163

164

165

166

167

168

169

170

171

172


173

174

175

176

177

178


179

180

181

182


183

184

185

186

187

188


189

190

191

192

193

194

195

196

197

198

199

200

201

202


203

204

205

206

207

208

209

210

211

212


213

214

215

216

217

218

219

220

221

222

223

224

225

226


227

228

229

230

231

232
