SqlHelper类
1
using System;
2using System.Data;
3using System.Data.SqlClient;
4using System.Configuration;
5using System.ComponentModel;
6
7namespace SQLHelper
8
{
9
/// <summary>
10
/// SQLHelper类封装对SQL Server数据库的添加、删除、修改和选择等操作
11
/// </summary>
12 public class SQLHelper
13 {
14 /// 连接数据源
15 private SqlConnection myConnection = null;
16 private readonly string RETURNVALUE = "RETURNVALUE";
17
18 /// <summary>
19 /// 打开数据库连接.
20 /// </summary>
21 private void Open()
22 {
23 // 打开数据库连接
24 if (myConnection == null)
25 {
26 myConnection = new SqlConnection(ConfigurationSettings.AppSettings["SQLCONNECTIONSTRING"].ToString());
27 }
28 if(myConnection.State == ConnectionState.Closed)
29 {
30 try
31 {
32 ///打开数据库连接
33 myConnection.Open();
34 }
35 catch(Exception ex)
36 {
37 SystemError.CreateErrorLog(ex.Message);
38 }
39 finally
40 {
41 ///关闭已经打开的数据库连接
42 }
43 }
44 }
45
46 /// <summary>
47 /// 关闭数据库连接
48 /// </summary>
49 public void Close()
50 {
51 ///判断连接是否已经创建
52 if(myConnection != null)
53 {
54 ///判断连接的状态是否打开
55 if(myConnection.State == ConnectionState.Open)
56 {
57 myConnection.Close();
58 }
59 }
60 }
61
62 /// <summary>
63 /// 释放资源
64 /// </summary>
65 public void Dispose()
66 {
67 // 确认连接是否已经关闭
68 if (myConnection != null)
69 {
70 myConnection.Dispose();
71 myConnection = null;
72 }
73 }
74
75 /// <summary>
76 /// 执行存储过程
77 /// </summary>
78 /// <param name="procName">存储过程的名称</param>
79 /// <returns>返回存储过程返回值</returns>
80 public int RunProc(string procName)
81 {
82 SqlCommand cmd = CreateProcCommand(procName, null);
83 try
84 {
85 ///执行存储过程
86 cmd.ExecuteNonQuery();
87 }
88 catch(Exception ex)
89 {
90 ///记录错误日志
91 SystemError.CreateErrorLog(ex.Message);
92 }
93 finally
94 {
95 ///关闭数据库的连接
96 Close();
97 }
98
99 ///返回存储过程的参数值
100 return (int)cmd.Parameters[RETURNVALUE].Value;
101 }
102
103 /// <summary>
104 /// 执行存储过程
105 /// </summary>
106 /// <param name="procName">存储过程名称</param>
107 /// <param name="prams">存储过程所需参数</param>
108 /// <returns>返回存储过程返回值</returns>
109 public int RunProc(string procName, SqlParameter[] prams)
110 {
111 SqlCommand cmd = CreateProcCommand(procName, prams);
112 try
113 {
114 ///执行存储过程
115 cmd.ExecuteNonQuery();
116 }
117 catch(Exception ex)
118 {
119 ///记录错误日志
120 SystemError.CreateErrorLog(ex.Message);
121 }
122 finally
123 {
124 ///关闭数据库的连接
125 Close();
126 }
127
128 ///返回存储过程的参数值
129 return (int)cmd.Parameters[RETURNVALUE].Value;
130 }
131
132 /// <summary>
133 /// 执行存储过程
134 /// </summary>
135 /// <param name="procName">存储过程的名称</param>
136 /// <param name="dataReader">返回存储过程返回值</param>
137 public void RunProc(string procName, out SqlDataReader dataReader)
138 {
139 ///创建Command
140 SqlCommand cmd = CreateProcCommand(procName, null);
141
142 try
143 {
144 ///读取数据
145 dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
146 }
147 catch(Exception ex)
148 {
149 dataReader = null;
150 ///记录错误日志
151 SystemError.CreateErrorLog(ex.Message);
152 }
153 }
154
155 /// <summary>
156 /// 执行存储过程
157 /// </summary>
158 /// <param name="procName">存储过程的名称</param>
159 /// <param name="prams">存储过程所需参数</param>
160 /// <param name="dataSet">返回DataReader对象</param>
161 public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
162 {
163 ///创建Command
164 SqlCommand cmd = CreateProcCommand(procName, prams);
165
166 try
167 {
168 ///读取数据
169 dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
170 }
171 catch(Exception ex)
172 {
173 dataReader = null;
174 ///记录错误日志
175 SystemError.CreateErrorLog(ex.Message);
176 }
177 }
178
179 /// <summary>
180 /// 执行存储过程
181 /// </summary>
182 /// <param name="procName">存储过程的名称</param>
183 /// <param name="dataSet">返回DataSet对象</param>
184 public void RunProc(string procName, ref DataSet dataSet)
185 {
186 if(dataSet == null)
187 {
188 dataSet = new DataSet();
189 }
190 ///创建SqlDataAdapter
191 SqlDataAdapter da = CreateProcDataAdapter(procName,null);
192
193 try
194 {
195 ///读取数据
196 da.Fill(dataSet);
197 }
198 catch(Exception ex)
199 {
200 ///记录错误日志
201 SystemError.CreateErrorLog(ex.Message);
202 }
203 finally
204 {
205 ///关闭数据库的连接
206 Close();
207 }
208 }
209
210 /// <summary>
211 /// 执行存储过程
212 /// </summary>
213 /// <param name="procName">存储过程的名称</param>
214 /// <param name="prams">存储过程所需参数</param>
215 /// <param name="dataSet">返回DataSet对象</param>
216 public void RunProc(string procName, SqlParameter[] prams,ref DataSet dataSet)
217 {
218 if(dataSet == null)
219 {
220 dataSet = new DataSet();
221 }
222 ///创建SqlDataAdapter
223 SqlDataAdapter da = CreateProcDataAdapter(procName,prams);
224
225 try
226 {
227 ///读取数据
228 da.Fill(dataSet);
229 }
230 catch(Exception ex)
231 {
232 ///记录错误日志
233 SystemError.CreateErrorLog(ex.Message);
234 }
235 finally
236 {
237 ///关闭数据库的连接
238 Close();
239 }
240 }
241
242 /// <summary>
243 /// 执行SQL语句
244 /// </summary>
245 /// <param name="cmdText">SQL语句</param>
246 /// <returns>返回值</returns>
247 public int RunSQL(string cmdText)
248 {
249 SqlCommand cmd = CreateSQLCommand(cmdText, null);
250 try
251 {
252 ///执行存储过程
253 cmd.ExecuteNonQuery();
254 }
255 catch(Exception ex)
256 {
257 ///记录错误日志
258 SystemError.CreateErrorLog(ex.Message);
259 }
260 finally
261 {
262 ///关闭数据库的连接
263 Close();
264 }
265
266 ///返回存储过程的参数值
267 return (int)cmd.Parameters[RETURNVALUE].Value;
268 }
269
270
271 /// <summary>
272 /// 执行SQL语句
273 /// </summary>
274 /// <param name="cmdText">SQL语句</param>
275 /// <param name="prams">SQL语句所需参数</param>
276 /// <returns>返回值</returns>
277 public int RunSQL(string cmdText, SqlParameter[] prams)
278 {
279 SqlCommand cmd = CreateSQLCommand(cmdText,prams);
280 try
281 {
282 ///执行存储过程
283 cmd.ExecuteNonQuery();
284 }
285 catch(Exception ex)
286 {
287 ///记录错误日志
288 SystemError.CreateErrorLog(ex.Message);
289 }
290 finally
291 {
292 ///关闭数据库的连接
293 Close();
294 }
295
296 ///返回存储过程的参数值
297 return (int)cmd.Parameters[RETURNVALUE].Value;
298 }
299
300 /// <summary>
301 /// 执行SQL语句
302 /// </summary>
303 /// <param name="cmdText">SQL语句</param>
304 /// <param name="dataReader">返回DataReader对象</param>
305 public void RunSQL(string cmdText, out SqlDataReader dataReader)
306 {
307 ///创建Command
308 SqlCommand cmd = CreateSQLCommand(cmdText, null);
309
310 try
311 {
312 ///读取数据
313 dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
314 }
315 catch(Exception ex)
316 {
317 dataReader = null;
318 ///记录错误日志
319 SystemError.CreateErrorLog(ex.Message);
320 }
321 }
322
323 /// <summary>
324 /// 执行SQL语句
325 /// </summary>
326 /// <param name="cmdText">SQL语句</param>
327 /// <param name="prams">SQL语句所需参数</param>
328 /// <param name="dataReader">返回DataReader对象</param>
329 public void RunSQL(string cmdText, SqlParameter[] prams, out SqlDataReader dataReader)
330 {
331 ///创建Command
332 SqlCommand cmd = CreateSQLCommand(cmdText, prams);
333
334 try
335 {
336 ///读取数据
337 dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
338 }
339 catch(Exception ex)
340 {
341 dataReader = null;
342 ///记录错误日志
343 SystemError.CreateErrorLog(ex.Message);
344 }
345 }
346
347 /// <summary>
348 /// 执行SQL语句
349 /// </summary>
350 /// <param name="cmdText">SQL语句</param>
351 /// <param name="dataSet">返回DataSet对象</param>
352 public void RunSQL(string cmdText, ref DataSet dataSet)
353 {
354 if(dataSet == null)
355 {
356 dataSet = new DataSet();
357 }
358 ///创建SqlDataAdapter
359 SqlDataAdapter da = CreateSQLDataAdapter(cmdText,null);
360
361 try
362 {
363 ///读取数据
364 da.Fill(dataSet);
365 }
366 catch(Exception ex)
367 {
368 ///记录错误日志
369 SystemError.CreateErrorLog(ex.Message);
370 }
371 finally
372 {
373 ///关闭数据库的连接
374 Close();
375 }
376 }
377
378 /// <summary>
379 /// 执行SQL语句
380 /// </summary>
381 /// <param name="cmdText">SQL语句</param>
382 /// <param name="prams">SQL语句所需参数</param>
383 /// <param name="dataSet">返回DataSet对象</param>
384 public void RunSQL(string cmdText, SqlParameter[] prams,ref DataSet dataSet)
385 {
386 if(dataSet == null)
387 {
388 dataSet = new DataSet();
389 }
390 ///创建SqlDataAdapter
391 SqlDataAdapter da = CreateProcDataAdapter(cmdText,prams);
392
393 try
394 {
395 ///读取数据
396 da.Fill(dataSet);
397 }
398 catch(Exception ex)
399 {
400 ///记录错误日志
401 SystemError.CreateErrorLog(ex.Message);
402 }
403 finally
404 {
405 ///关闭数据库的连接
406 Close();
407 }
408 }
409
410 /// <summary>
411 /// 创建一个SqlCommand对象以此来执行存储过程
412 /// </summary>
413 /// <param name="procName">存储过程的名称</param>
414 /// <param name="prams">存储过程所需参数</param>
415 /// <returns>返回SqlCommand对象</returns>
416 private SqlCommand CreateProcCommand(string procName, SqlParameter[] prams)
417 {
418 ///打开数据库连接
419 Open();
420
421 ///设置Command
422 SqlCommand cmd = new SqlCommand(procName, myConnection);
423 cmd.CommandType = CommandType.StoredProcedure;
424
425 ///添加把存储过程的参数
426 if (prams != null)
427 {
428 foreach (SqlParameter parameter in prams)
429 {
430 cmd.Parameters.Add(parameter);
431 }
432 }
433
434 ///添加返回参数ReturnValue
435 cmd.Parameters.Add(
436 new SqlParameter(RETURNVALUE, SqlDbType.Int,4,ParameterDirection.ReturnValue,
437 false,0,0,string.Empty, DataRowVersion.Default,null));
438
439 ///返回创建的SqlCommand对象
440 return cmd;
441 }
442
443 /// <summary>
444 /// 创建一个SqlCommand对象以此来执行存储过程
445 /// </summary>
446 /// <param name="cmdText">SQL语句</param>
447 /// <param name="prams">SQL语句所需参数</param>
448 /// <returns>返回SqlCommand对象</returns>
449 private SqlCommand CreateSQLCommand(string cmdText, SqlParameter[] prams)
450 {
451 ///打开数据库连接
452 Open();
453
454 ///设置Command
455 SqlCommand cmd = new SqlCommand(cmdText,myConnection);
456
457 ///添加把存储过程的参数
458 if (prams != null)
459 {
460 foreach (SqlParameter parameter in prams)
461 {
462 cmd.Parameters.Add(parameter);
463 }
464 }
465
466 ///添加返回参数ReturnValue
467 cmd.Parameters.Add(
468 new SqlParameter(RETURNVALUE, SqlDbType.Int,4,ParameterDirection.ReturnValue,
469 false,0,0,string.Empty, DataRowVersion.Default,null));
470
471 ///返回创建的SqlCommand对象
472 return cmd;
473 }
474
475 /// <summary>
476 /// 创建一个SqlDataAdapter对象，用此来执行存储过程
477 /// </summary>
478 /// <param name="procName">存储过程的名称</param>
479 /// <param name="prams">存储过程所需参数</param>
480 /// <returns>返回SqlDataAdapter对象</returns>
481 private SqlDataAdapter CreateProcDataAdapter(string procName,SqlParameter[] prams)
482 {
483 ///打开数据库连接
484 Open();
485
486 ///设置SqlDataAdapter对象
487 SqlDataAdapter da = new SqlDataAdapter(procName,myConnection);
488 da.SelectCommand.CommandType = CommandType.StoredProcedure;
489
490 ///添加把存储过程的参数
491 if (prams != null)
492 {
493 foreach (SqlParameter parameter in prams)
494 {
495 da.SelectCommand.Parameters.Add(parameter);
496 }
497 }
498
499 ///添加返回参数ReturnValue
500 da.SelectCommand.Parameters.Add(
501 new SqlParameter(RETURNVALUE, SqlDbType.Int,4,ParameterDirection.ReturnValue,
502 false,0,0,string.Empty, DataRowVersion.Default,null));
503
504 ///返回创建的SqlDataAdapter对象
505 return da;
506 }
507
508 /// <summary>
509 /// 创建一个SqlDataAdapter对象，用此来执行SQL语句
510 /// </summary>
511 /// <param name="cmdText">SQL语句</param>
512 /// <param name="prams">SQL语句所需参数</param>
513 /// <returns>返回SqlDataAdapter对象</returns>
514 private SqlDataAdapter CreateSQLDataAdapter(string cmdText,SqlParameter[] prams)
515 {
516 ///打开数据库连接
517 Open();
518
519 ///设置SqlDataAdapter对象
520 SqlDataAdapter da = new SqlDataAdapter(cmdText,myConnection);
521
522 ///添加把存储过程的参数
523 if (prams != null)
524 {
525 foreach (SqlParameter parameter in prams)
526 {
527 da.SelectCommand.Parameters.Add(parameter);
528 }
529 }
530
531 ///添加返回参数ReturnValue
532 da.SelectCommand.Parameters.Add(
533 new SqlParameter(RETURNVALUE, SqlDbType.Int,4,ParameterDirection.ReturnValue,
534 false,0,0,string.Empty, DataRowVersion.Default,null));
535
536 ///返回创建的SqlDataAdapter对象
537 return da;
538 }
539
540 /// <summary>
541 /// 生成存储过程参数
542 /// </summary>
543 /// <param name="ParamName">存储过程名称</param>
544 /// <param name="DbType">参数类型</param>
545 /// <param name="Size">参数大小</param>
546 /// <param name="Direction">参数方向</param>
547 /// <param name="Value">参数值</param>
548 /// <returns>新的 parameter 对象</returns>
549 public SqlParameter CreateParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
550 {
551 SqlParameter param;
552
553 ///当参数大小为0时，不使用该参数大小值
554 if(Size > 0)
555 {
556 param = new SqlParameter(ParamName, DbType, Size);
557 }
558 else
559 {
560 ///当参数大小为0时，不使用该参数大小值
561 param = new SqlParameter(ParamName, DbType);
562 }
563
564 ///创建输出类型的参数
565 param.Direction = Direction;
566 if (!(Direction == ParameterDirection.Output && Value == null))
567 {
568 param.Value = Value;
569 }
570
571 ///返回创建的参数
572 return param;
573 }
574
575 /// <summary>
576 /// 传入输入参数
577 /// </summary>
578 /// <param name="ParamName">存储过程名称</param>
579 /// <param name="DbType">参数类型</param></param>
580 /// <param name="Size">参数大小</param>
581 /// <param name="Value">参数值</param>
582 /// <returns>新的parameter 对象</returns>
583 public SqlParameter CreateInParam(string ParamName, SqlDbType DbType, int Size, object Value)
584 {
585 return CreateParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
586 }
587
588 /// <summary>
589 /// 传入返回值参数
590 /// </summary>
591 /// <param name="ParamName">存储过程名称</param>
592 /// <param name="DbType">参数类型</param>
593 /// <param name="Size">参数大小</param>
594 /// <returns>新的 parameter 对象</returns>
595 public SqlParameter CreateOutParam(string ParamName, SqlDbType DbType, int Size)
596 {
597 return CreateParam(ParamName, DbType, Size, ParameterDirection.Output, null);
598 }
599
600 /// <summary>
601 /// 传入返回值参数
602 /// </summary>
603 /// <param name="ParamName">存储过程名称</param>
604 /// <param name="DbType">参数类型</param>
605 /// <param name="Size">参数大小</param>
606 /// <returns>新的 parameter 对象</returns>
607 public SqlParameter CreateReturnParam(string ParamName, SqlDbType DbType, int Size)
608 {
609 return CreateParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null);
610 }
611 }
612
}
613
using System;2
using System.Data;3
using System.Data.SqlClient;4
using System.Configuration;5
using System.ComponentModel;6
7
namespace SQLHelper8
{
9
/// <summary>
10
/// SQLHelper类封装对SQL Server数据库的添加、删除、修改和选择等操作11
/// </summary>12
public class SQLHelper13
{14
/// 连接数据源15
private SqlConnection myConnection = null;16
private readonly string RETURNVALUE = "RETURNVALUE";17
18
/// <summary>19
/// 打开数据库连接.20
/// </summary>21
private void Open() 22
{23
// 打开数据库连接24
if (myConnection == null) 25
{26
myConnection = new SqlConnection(ConfigurationSettings.AppSettings["SQLCONNECTIONSTRING"].ToString()); 27
} 28
if(myConnection.State == ConnectionState.Closed)29
{ 30
try31
{32
///打开数据库连接33
myConnection.Open();34
}35
catch(Exception ex)36
{37
SystemError.CreateErrorLog(ex.Message);38
}39
finally40
{41
///关闭已经打开的数据库连接 42
}43
}44
}45
46
/// <summary>47
/// 关闭数据库连接48
/// </summary>49
public void Close() 50
{51
///判断连接是否已经创建52
if(myConnection != null)53
{54
///判断连接的状态是否打开55
if(myConnection.State == ConnectionState.Open)56
{57
myConnection.Close();58
}59
}60
}61
62
/// <summary>63
/// 释放资源64
/// </summary>65
public void Dispose() 66
{67
// 确认连接是否已经关闭68
if (myConnection != null) 69
{70
myConnection.Dispose();71
myConnection = null;72
} 73
}74
75
/// <summary>76
/// 执行存储过程77
/// </summary>78
/// <param name="procName">存储过程的名称</param>79
/// <returns>返回存储过程返回值</returns>80
public int RunProc(string procName) 81
{82
SqlCommand cmd = CreateProcCommand(procName, null);83
try84
{85
///执行存储过程86
cmd.ExecuteNonQuery();87
}88
catch(Exception ex)89
{90
///记录错误日志91
SystemError.CreateErrorLog(ex.Message);92
}93
finally94
{95
///关闭数据库的连接96
Close();97
}98
99
///返回存储过程的参数值100
return (int)cmd.Parameters[RETURNVALUE].Value;101
}102
103
/// <summary>104
/// 执行存储过程105
/// </summary>106
/// <param name="procName">存储过程名称</param>107
/// <param name="prams">存储过程所需参数</param>108
/// <returns>返回存储过程返回值</returns>109
public int RunProc(string procName, SqlParameter[] prams) 110
{111
SqlCommand cmd = CreateProcCommand(procName, prams);112
try113
{114
///执行存储过程115
cmd.ExecuteNonQuery();116
}117
catch(Exception ex)118
{119
///记录错误日志120
SystemError.CreateErrorLog(ex.Message);121
}122
finally123
{124
///关闭数据库的连接125
Close();126
}127
128
///返回存储过程的参数值129
return (int)cmd.Parameters[RETURNVALUE].Value;130
}131
132
/// <summary>133
/// 执行存储过程134
/// </summary>135
/// <param name="procName">存储过程的名称</param>136
/// <param name="dataReader">返回存储过程返回值</param>137
public void RunProc(string procName, out SqlDataReader dataReader) 138
{139
///创建Command140
SqlCommand cmd = CreateProcCommand(procName, null);141
142
try143
{144
///读取数据145
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 146
}147
catch(Exception ex)148
{149
dataReader = null;150
///记录错误日志151
SystemError.CreateErrorLog(ex.Message);152
}153
}154
155
/// <summary>156
/// 执行存储过程157
/// </summary>158
/// <param name="procName">存储过程的名称</param>159
/// <param name="prams">存储过程所需参数</param>160
/// <param name="dataSet">返回DataReader对象</param>161
public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader) 162
{163
///创建Command164
SqlCommand cmd = CreateProcCommand(procName, prams);165
166
try167
{168
///读取数据169
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);170
}171
catch(Exception ex)172
{173
dataReader = null;174
///记录错误日志175
SystemError.CreateErrorLog(ex.Message);176
}177
} 178
179
/// <summary>180
/// 执行存储过程181
/// </summary>182
/// <param name="procName">存储过程的名称</param>183
/// <param name="dataSet">返回DataSet对象</param>184
public void RunProc(string procName, ref DataSet dataSet) 185
{186
if(dataSet == null)187
{188
dataSet = new DataSet();189
}190
///创建SqlDataAdapter191
SqlDataAdapter da = CreateProcDataAdapter(procName,null);192
193
try194
{195
///读取数据196
da.Fill(dataSet);197
}198
catch(Exception ex)199
{200
///记录错误日志201
SystemError.CreateErrorLog(ex.Message);202
}203
finally204
{205
///关闭数据库的连接206
Close(); 207
}208
}209
210
/// <summary>211
/// 执行存储过程212
/// </summary>213
/// <param name="procName">存储过程的名称</param>214
/// <param name="prams">存储过程所需参数</param>215
/// <param name="dataSet">返回DataSet对象</param>216
public void RunProc(string procName, SqlParameter[] prams,ref DataSet dataSet) 217
{218
if(dataSet == null)219
{220
dataSet = new DataSet();221
}222
///创建SqlDataAdapter223
SqlDataAdapter da = CreateProcDataAdapter(procName,prams);224
225
try226
{227
///读取数据228
da.Fill(dataSet);229
}230
catch(Exception ex)231
{232
///记录错误日志233
SystemError.CreateErrorLog(ex.Message);234
}235
finally236
{237
///关闭数据库的连接238
Close(); 239
}240
}241
242
/// <summary>243
/// 执行SQL语句244
/// </summary>245
/// <param name="cmdText">SQL语句</param>246
/// <returns>返回值</returns>247
public int RunSQL(string cmdText) 248
{249
SqlCommand cmd = CreateSQLCommand(cmdText, null);250
try251
{252
///执行存储过程253
cmd.ExecuteNonQuery();254
}255
catch(Exception ex)256
{257
///记录错误日志258
SystemError.CreateErrorLog(ex.Message);259
}260
finally261
{262
///关闭数据库的连接263
Close(); 264
}265
266
///返回存储过程的参数值267
return (int)cmd.Parameters[RETURNVALUE].Value;268
}269
270
271
/// <summary>272
/// 执行SQL语句273
/// </summary>274
/// <param name="cmdText">SQL语句</param>275
/// <param name="prams">SQL语句所需参数</param>276
/// <returns>返回值</returns>277
public int RunSQL(string cmdText, SqlParameter[] prams) 278
{279
SqlCommand cmd = CreateSQLCommand(cmdText,prams);280
try281
{282
///执行存储过程283
cmd.ExecuteNonQuery();284
}285
catch(Exception ex)286
{287
///记录错误日志288
SystemError.CreateErrorLog(ex.Message);289
}290
finally291
{292
///关闭数据库的连接293
Close(); 294
}295
296
///返回存储过程的参数值297
return (int)cmd.Parameters[RETURNVALUE].Value;298
} 299
300
/// <summary>301
/// 执行SQL语句302
/// </summary>303
/// <param name="cmdText">SQL语句</param> 304
/// <param name="dataReader">返回DataReader对象</param>305
public void RunSQL(string cmdText, out SqlDataReader dataReader) 306
{307
///创建Command308
SqlCommand cmd = CreateSQLCommand(cmdText, null);309
310
try311
{312
///读取数据313
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 314
}315
catch(Exception ex)316
{317
dataReader = null;318
///记录错误日志319
SystemError.CreateErrorLog(ex.Message);320
}321
}322
323
/// <summary>324
/// 执行SQL语句325
/// </summary>326
/// <param name="cmdText">SQL语句</param>327
/// <param name="prams">SQL语句所需参数</param>328
/// <param name="dataReader">返回DataReader对象</param>329
public void RunSQL(string cmdText, SqlParameter[] prams, out SqlDataReader dataReader) 330
{331
///创建Command332
SqlCommand cmd = CreateSQLCommand(cmdText, prams);333
334
try335
{336
///读取数据337
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);338
}339
catch(Exception ex)340
{341
dataReader = null;342
///记录错误日志343
SystemError.CreateErrorLog(ex.Message);344
}345
}346
347
/// <summary>348
/// 执行SQL语句349
/// </summary>350
/// <param name="cmdText">SQL语句</param>351
/// <param name="dataSet">返回DataSet对象</param>352
public void RunSQL(string cmdText, ref DataSet dataSet) 353
{354
if(dataSet == null)355
{356
dataSet = new DataSet();357
}358
///创建SqlDataAdapter359
SqlDataAdapter da = CreateSQLDataAdapter(cmdText,null);360
361
try362
{363
///读取数据364
da.Fill(dataSet);365
}366
catch(Exception ex)367
{368
///记录错误日志369
SystemError.CreateErrorLog(ex.Message);370
}371
finally372
{373
///关闭数据库的连接374
Close(); 375
}376
}377
378
/// <summary>379
/// 执行SQL语句380
/// </summary>381
/// <param name="cmdText">SQL语句</param>382
/// <param name="prams">SQL语句所需参数</param>383
/// <param name="dataSet">返回DataSet对象</param>384
public void RunSQL(string cmdText, SqlParameter[] prams,ref DataSet dataSet) 385
{386
if(dataSet == null)387
{388
dataSet = new DataSet();389
}390
///创建SqlDataAdapter391
SqlDataAdapter da = CreateProcDataAdapter(cmdText,prams);392
393
try394
{395
///读取数据396
da.Fill(dataSet);397
}398
catch(Exception ex)399
{400
///记录错误日志401
SystemError.CreateErrorLog(ex.Message);402
}403
finally404
{405
///关闭数据库的连接406
Close(); 407
}408
}409
410
/// <summary>411
/// 创建一个SqlCommand对象以此来执行存储过程412
/// </summary>413
/// <param name="procName">存储过程的名称</param>414
/// <param name="prams">存储过程所需参数</param>415
/// <returns>返回SqlCommand对象</returns>416
private SqlCommand CreateProcCommand(string procName, SqlParameter[] prams) 417
{418
///打开数据库连接419
Open();420
421
///设置Command422
SqlCommand cmd = new SqlCommand(procName, myConnection);423
cmd.CommandType = CommandType.StoredProcedure;424
425
///添加把存储过程的参数426
if (prams != null) 427
{428
foreach (SqlParameter parameter in prams)429
{430
cmd.Parameters.Add(parameter);431
}432
}433
434
///添加返回参数ReturnValue435
cmd.Parameters.Add(436
new SqlParameter(RETURNVALUE, SqlDbType.Int,4,ParameterDirection.ReturnValue,437
false,0,0,string.Empty, DataRowVersion.Default,null));438
439
///返回创建的SqlCommand对象440
return cmd;441
}442
443
/// <summary>444
/// 创建一个SqlCommand对象以此来执行存储过程445
/// </summary>446
/// <param name="cmdText">SQL语句</param>447
/// <param name="prams">SQL语句所需参数</param>448
/// <returns>返回SqlCommand对象</returns>449
private SqlCommand CreateSQLCommand(string cmdText, SqlParameter[] prams) 450
{451
///打开数据库连接452
Open();453
454
///设置Command455
SqlCommand cmd = new SqlCommand(cmdText,myConnection); 456
457
///添加把存储过程的参数458
if (prams != null) 459
{460
foreach (SqlParameter parameter in prams)461
{462
cmd.Parameters.Add(parameter);463
}464
}465
466
///添加返回参数ReturnValue467
cmd.Parameters.Add(468
new SqlParameter(RETURNVALUE, SqlDbType.Int,4,ParameterDirection.ReturnValue,469
false,0,0,string.Empty, DataRowVersion.Default,null));470
471
///返回创建的SqlCommand对象472
return cmd;473
}474
475
/// <summary>476
/// 创建一个SqlDataAdapter对象，用此来执行存储过程477
/// </summary>478
/// <param name="procName">存储过程的名称</param>479
/// <param name="prams">存储过程所需参数</param>480
/// <returns>返回SqlDataAdapter对象</returns>481
private SqlDataAdapter CreateProcDataAdapter(string procName,SqlParameter[] prams)482
{483
///打开数据库连接484
Open();485
486
///设置SqlDataAdapter对象487
SqlDataAdapter da = new SqlDataAdapter(procName,myConnection);488
da.SelectCommand.CommandType = CommandType.StoredProcedure; 489
490
///添加把存储过程的参数491
if (prams != null) 492
{493
foreach (SqlParameter parameter in prams)494
{495
da.SelectCommand.Parameters.Add(parameter);496
}497
}498
499
///添加返回参数ReturnValue500
da.SelectCommand.Parameters.Add(501
new SqlParameter(RETURNVALUE, SqlDbType.Int,4,ParameterDirection.ReturnValue,502
false,0,0,string.Empty, DataRowVersion.Default,null));503
504
///返回创建的SqlDataAdapter对象505
return da;506
}507
508
/// <summary>509
/// 创建一个SqlDataAdapter对象，用此来执行SQL语句510
/// </summary>511
/// <param name="cmdText">SQL语句</param>512
/// <param name="prams">SQL语句所需参数</param>513
/// <returns>返回SqlDataAdapter对象</returns>514
private SqlDataAdapter CreateSQLDataAdapter(string cmdText,SqlParameter[] prams)515
{516
///打开数据库连接517
Open();518
519
///设置SqlDataAdapter对象520
SqlDataAdapter da = new SqlDataAdapter(cmdText,myConnection); 521
522
///添加把存储过程的参数523
if (prams != null) 524
{525
foreach (SqlParameter parameter in prams)526
{527
da.SelectCommand.Parameters.Add(parameter);528
}529
}530
531
///添加返回参数ReturnValue532
da.SelectCommand.Parameters.Add(533
new SqlParameter(RETURNVALUE, SqlDbType.Int,4,ParameterDirection.ReturnValue,534
false,0,0,string.Empty, DataRowVersion.Default,null));535
536
///返回创建的SqlDataAdapter对象537
return da;538
}539
540
/// <summary>541
/// 生成存储过程参数542
/// </summary>543
/// <param name="ParamName">存储过程名称</param>544
/// <param name="DbType">参数类型</param>545
/// <param name="Size">参数大小</param>546
/// <param name="Direction">参数方向</param>547
/// <param name="Value">参数值</param>548
/// <returns>新的 parameter 对象</returns>549
public SqlParameter CreateParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value) 550
{551
SqlParameter param;552
553
///当参数大小为0时，不使用该参数大小值554
if(Size > 0)555
{556
param = new SqlParameter(ParamName, DbType, Size);557
}558
else559
{560
///当参数大小为0时，不使用该参数大小值561
param = new SqlParameter(ParamName, DbType);562
}563
564
///创建输出类型的参数565
param.Direction = Direction;566
if (!(Direction == ParameterDirection.Output && Value == null))567
{568
param.Value = Value;569
}570
571
///返回创建的参数572
return param;573
}574
575
/// <summary>576
/// 传入输入参数577
/// </summary>578
/// <param name="ParamName">存储过程名称</param>579
/// <param name="DbType">参数类型</param></param>580
/// <param name="Size">参数大小</param>581
/// <param name="Value">参数值</param>582
/// <returns>新的parameter 对象</returns>583
public SqlParameter CreateInParam(string ParamName, SqlDbType DbType, int Size, object Value) 584
{585
return CreateParam(ParamName, DbType, Size, ParameterDirection.Input, Value);586
} 587
588
/// <summary>589
/// 传入返回值参数590
/// </summary>591
/// <param name="ParamName">存储过程名称</param>592
/// <param name="DbType">参数类型</param>593
/// <param name="Size">参数大小</param>594
/// <returns>新的 parameter 对象</returns>595
public SqlParameter CreateOutParam(string ParamName, SqlDbType DbType, int Size) 596
{597
return CreateParam(ParamName, DbType, Size, ParameterDirection.Output, null);598
} 599
600
/// <summary>601
/// 传入返回值参数602
/// </summary>603
/// <param name="ParamName">存储过程名称</param>604
/// <param name="DbType">参数类型</param>605
/// <param name="Size">参数大小</param>606
/// <returns>新的 parameter 对象</returns>607
public SqlParameter CreateReturnParam(string ParamName, SqlDbType DbType, int Size) 608
{609
return CreateParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null);610
} 611
}612
}613
附带SystemError
1using System;
2using System.IO;
3
4namespace SQLHelper
5{
6 /// <summary>
7 /// 错误处理函数，用于记录错误日志
8 /// </summary>
9 public class SystemError
10 {
11 //记录错误日志位置
12 private static string m_fileName = "c:\\Systemlog.txt";
13
14 public static String FileName
15 {
16 get
17 {
18 return(m_fileName);
19 }
20 set
21 {
22 if(value != null || value != "")
23 {
24 m_fileName = value;
25 }
26 }
27 }
28
29 /// <summary>
30 /// 记录日志至文本文件
31 /// </summary>
32 /// <param name="message">记录的内容</param>
33 public static void CreateErrorLog(string message)
34 {
35// if(File.Exists(m_fileName))
36// {
37// ///如果日志文件已经存在，则直接写入日志文件
38// StreamWriter sr = File.AppendText(FileName);
39// sr.WriteLine ("\n");
40// sr.WriteLine (DateTime.Now.ToString()+message);
41// sr.Close();
42// }
43// else
44// {
45// ///创建日志文件
46// StreamWriter sr = File.CreateText(FileName);
47// sr.Close();
48// }
49 }
50 }
51}
52
using System;2
using System.IO;3
4
namespace SQLHelper5
{6
/// <summary>7
/// 错误处理函数，用于记录错误日志8
/// </summary>9
public class SystemError 10
{11
//记录错误日志位置12
private static string m_fileName = "c:\\Systemlog.txt";13
14
public static String FileName15
{16
get17
{18
return(m_fileName);19
}20
set21
{22
if(value != null || value != "")23
{24
m_fileName = value;25
}26
}27
}28
29
/// <summary>30
/// 记录日志至文本文件31
/// </summary>32
/// <param name="message">记录的内容</param>33
public static void CreateErrorLog(string message) 34
{35
// if(File.Exists(m_fileName))36
// {37
// ///如果日志文件已经存在，则直接写入日志文件38
// StreamWriter sr = File.AppendText(FileName);39
// sr.WriteLine ("\n");40
// sr.WriteLine (DateTime.Now.ToString()+message);41
// sr.Close();42
// }43
// else44
// {45
// ///创建日志文件46
// StreamWriter sr = File.CreateText(FileName);47
// sr.Close();48
// } 49
}50
}51
}52
再附带SystemTools.cs
1using System;
2using System.Data;
3using System.Data.SqlClient;
4
5namespace SQLHelper
6{
7 /// <summary>
8 /// 自定义Exception
9 /// </summary>
10 public class SystemException:Exception
11 {
12 /// <summary>
13 /// 包含系统Excepton
14 /// </summary>
15 public SystemException(string source,string message,Exception inner):base(message,inner)
16 {
17 base.Source=source;
18 }
19
20 /// <summary>
21 /// 不包含系统Excepton
22 /// </summary>
23 public SystemException(string source,string message):base(message)
24 {
25 base.Source=source;
26 }
27 }
28
29 /// <summary>
30 /// 处理网页中的HTML代码，并消除危险字符
31 /// </summary>
32 public class SystemHTML
33 {
34 private static string HTMLEncode(string fString)
35 {
36 if(fString!=string.Empty)
37 {
38 ///替换尖括号
39 fString.Replace("<","<");
40 fString.Replace(">","&rt;");
41 ///替换引号
42 fString.Replace(((char)34).ToString(), """);
43 fString.Replace(((char)39).ToString(), "'");
44 ///替换空格
45 fString.Replace(((char)13).ToString(), "");
46 ///替换换行符
47 fString.Replace(((char)10).ToString(), "<BR> ");
48 }
49 return(fString);
50 }
51 }
52
53 /// <summary>
54 /// SystemTools 的摘要说明。
55 /// </summary>
56 public class SystemTools
57 {
58 /// <summary>
59 /// 将DataReader转为DataTable
60 /// </summary>
61 /// <param name="DataReader">DataReader</param>
62 public static DataTable ConvertDataReaderToDataTable(SqlDataReader dataReader)
63 {
64 ///定义DataTable
65 DataTable datatable = new DataTable();
66
67 try
68 { ///动态添加表的数据列
69 for(int i = 0; i < dataReader.FieldCount; i++)
70 {
71 DataColumn myDataColumn = new DataColumn();
72 myDataColumn.DataType = dataReader.GetFieldType(i);
73 myDataColumn.ColumnName = dataReader.GetName(i);
74 datatable.Columns.Add(myDataColumn);
75 }
76
77 ///添加表的数据
78 while (dataReader.Read())
79 {
80 DataRow myDataRow = datatable.NewRow();
81 for (int i = 0; i < dataReader.FieldCount; i++)
82 {
83 myDataRow[i] = dataReader[i].ToString();
84 }
85 datatable.Rows.Add(myDataRow);
86 myDataRow = null;
87 }
88 ///关闭数据读取器
89 dataReader.Close();
90 return datatable;
91 }
92 catch(Exception ex)
93 {
94 ///抛出类型转换错误
95 SystemError.CreateErrorLog(ex.Message);
96 throw new Exception(ex.Message,ex);
97 }
98 }
99 }
100}
101
using System;2
using System.Data;3
using System.Data.SqlClient;4
5
namespace SQLHelper6
{7
/// <summary>8
/// 自定义Exception9
/// </summary>10
public class SystemException:Exception11
{12
/// <summary>13
/// 包含系统Excepton14
/// </summary>15
public SystemException(string source,string message,Exception inner):base(message,inner)16
{17
base.Source=source;18
}19
20
/// <summary>21
/// 不包含系统Excepton22
/// </summary> 23
public SystemException(string source,string message):base(message)24
{25
base.Source=source;26
}27
}28
29
/// <summary>30
/// 处理网页中的HTML代码，并消除危险字符31
/// </summary>32
public class SystemHTML33
{34
private static string HTMLEncode(string fString)35
{36
if(fString!=string.Empty)37
{38
///替换尖括号39
fString.Replace("<","<");40
fString.Replace(">","&rt;");41
///替换引号42
fString.Replace(((char)34).ToString(), """);43
fString.Replace(((char)39).ToString(), "'");44
///替换空格45
fString.Replace(((char)13).ToString(), "");46
///替换换行符47
fString.Replace(((char)10).ToString(), "<BR> ");48
}49
return(fString);50
}51
}52
53
/// <summary>54
/// SystemTools 的摘要说明。55
/// </summary>56
public class SystemTools57
{ 58
/// <summary>59
/// 将DataReader转为DataTable60
/// </summary>61
/// <param name="DataReader">DataReader</param>62
public static DataTable ConvertDataReaderToDataTable(SqlDataReader dataReader)63
{64
///定义DataTable65
DataTable datatable = new DataTable(); 66
67
try68
{ ///动态添加表的数据列69
for(int i = 0; i < dataReader.FieldCount; i++)70
{71
DataColumn myDataColumn = new DataColumn();72
myDataColumn.DataType = dataReader.GetFieldType(i);73
myDataColumn.ColumnName = dataReader.GetName(i);74
datatable.Columns.Add(myDataColumn);75
} 76
77
///添加表的数据78
while (dataReader.Read())79
{80
DataRow myDataRow = datatable.NewRow();81
for (int i = 0; i < dataReader.FieldCount; i++)82
{83
myDataRow[i] = dataReader[i].ToString();84
}85
datatable.Rows.Add(myDataRow);86
myDataRow = null;87
} 88
///关闭数据读取器89
dataReader.Close();90
return datatable;91
}92
catch(Exception ex)93
{ 94
///抛出类型转换错误95
SystemError.CreateErrorLog(ex.Message);96
throw new Exception(ex.Message,ex); 97
} 98
}99
}100
}101
浙公网安备 33010602011771号