C# 数据库通用操作类DBHelper

企业信息系统开发主要是围绕数据库进行CRUD ，收藏一个通用的DBHELP操作类

SQLHelper、DBHelper是ADO.NET的改造版

1 using System;

2 using System.Collections;

3 using System.Collections.Specialized;

4 using System.Data;

5 using System.Data.SqlClient;

6 using System.Configuration;

7 using System.Data.Common;

8 using System.Collections.Generic;

9 namespace lrdbhelpSQL

10 {

11 ///

12 /// 数据访问抽象基础类

13 /// Copyright (C) Maticsoft

14 ///

15 public abstract class DbHelperSQL

16 {

17 ////数据库连接字符串(web.config来配置)，多数据库可使用DbHelperSQLP来实现.

18 //public static string connectionString = GetConfigInfo();

19

20 //public static string GetConfigInfo()

21 //{

22 // string configInfo = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

23 // return configInfo;

24 //}

25

26 public static string connectionString = AppConst.DBConnstring;

27

28 public DbHelperSQL()

29 {

30 }

31

32

33 #region 公用方法

34 ///

35 /// 判断是否存在某表的某个字段

36 ///

37 /// 表名称

38 /// 列名称

39 /// 是否存在

40 public static bool ColumnExists(string tableName, string columnName)

41 {

42 string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";

43 object res = GetSingle(sql);

44 if (res == null)

45 {

46 return false;

47 }

48 return Convert.ToInt32(res) > 0;

49 }

50 public static int GetMaxID(string FieldName, string TableName)

51 {

52 string strsql = "select max(" + FieldName + ")+1 from " + TableName;

53 object obj = GetSingle(strsql);

54 if (obj == null)

55 {

56 return 1;

57 }

58 else

59 {

60 return int.Parse(obj.ToString());

61 }

62 }

63 public static bool Exists(string strSql)

64 {

65 object obj = GetSingle(strSql);

66 int cmdresult;

67 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

68 {

69 cmdresult = 0;

70 }

71 else

72 {

73 cmdresult = int.Parse(obj.ToString()); //也可能=0

74 }

75 if (cmdresult == 0)

76 {

77 return false;

78 }

79 else

80 {

81 return true;

82 }

83 }

84 ///

85 /// 表是否存在

86 ///

87 ///

88 ///

89 public static bool TabExists(string TableName)

90 {

91 string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";

92 //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";

93 object obj = GetSingle(strsql);

94 int cmdresult;

95 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

96 {

97 cmdresult = 0;

98 }

99 else

100 {

101 cmdresult = int.Parse(obj.ToString());

102 }

103 if (cmdresult == 0)

104 {

105 return false;

106 }

107 else

108 {

109 return true;

110 }

111 }

112 public static bool Exists(string strSql, params SqlParameter[] cmdParms)

113 {

114 object obj = GetSingle(strSql, cmdParms);

115 int cmdresult;

116 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

117 {

118 cmdresult = 0;

119 }

120 else

121 {

122 cmdresult = int.Parse(obj.ToString());

123 }

124 if (cmdresult == 0)

125 {

126 return false;

127 }

128 else

129 {

130 return true;

131 }

132 }

133 #endregion

134

135

136 #region 执行简单SQL语句

137

138

139 ///

140 /// 执行SQL语句，返回影响的记录数

141 ///

142 /// SQL语句

143 /// 影响的记录数

144 public static int ExecuteSql(string SQLString)

145 {

146 using (SqlConnection connection = new SqlConnection(connectionString))

147 {

148 using (SqlCommand cmd = new SqlCommand(SQLString, connection))

149 {

150 try

151 {

152 connection.Open();

153 int rows = cmd.ExecuteNonQuery();

154 return rows;

155 }

156 catch (System.Data.SqlClient.SqlException e)

157 {

158 connection.Close();

159 //throw e;

160 }

161 return 0;

162 }

163 }

164 }

165

166

167

168 public static int ExecuteSqlByTime(string SQLString, int Times)

169 {

170 using (SqlConnection connection = new SqlConnection(connectionString))

171 {

172 using (SqlCommand cmd = new SqlCommand(SQLString, connection))

173 {

174 try

175 {

176 connection.Open();

177 cmd.CommandTimeout = Times;

178 int rows = cmd.ExecuteNonQuery();

179 return rows;

180 }

181 catch (System.Data.SqlClient.SqlException e)

182 {

183 connection.Close();

184 throw e;

185 }

186 }

187 }

188 }

189

190

191 ///

192 /// 执行多条SQL语句，实现数据库事务。

193 ///

194 /// 多条SQL语句

195 public static int ExecuteSqlTran(List SQLStringList)

196 {

197 using (SqlConnection conn = new SqlConnection(connectionString))

198 {

199 conn.Open();

200 SqlCommand cmd = new SqlCommand();

201 cmd.Connection = conn;

202 SqlTransaction tx = conn.BeginTransaction();

203 cmd.Transaction = tx;

204 try

205 {

206 int count = 0;

207 for (int n = 0; n < SQLStringList.Count; n++)

208 {

209 string strsql = SQLStringList[n];

210 if (strsql.Trim().Length > 1)

211 {

212 cmd.CommandText = strsql;

213 count += cmd.ExecuteNonQuery();

214 }

215 }

216 tx.Commit();

217 return count;

218 }

219 catch

220 {

221 tx.Rollback();

222 return 0;

223 }

224 }

225 }

226 ///

227 /// 执行带一个存储过程参数的的SQL语句。

228 ///

229 /// SQL语句

230 /// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加

231 /// 影响的记录数

232 public static int ExecuteSql(string SQLString, string content)

233 {

234 using (SqlConnection connection = new SqlConnection(content))

235 {

236 SqlCommand cmd = new SqlCommand(SQLString, connection);

237 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);

238 myParameter.Value = content;

239 cmd.Parameters.Add(myParameter);

240 try

241 {

242 connection.Open();

243 int rows = cmd.ExecuteNonQuery();

244 return rows;

245 }

246 catch (System.Data.SqlClient.SqlException e)

247 {

248 //throw e;

249 }

250 finally

251 {

252 cmd.Dispose();

253 connection.Close();

254 }

255 return 0;

256 }

257 }

258 ///

259 /// 执行带一个存储过程参数的的SQL语句。

260 ///

261 /// SQL语句

262 /// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加

263 /// 影响的记录数

264 public static object ExecuteSqlGet(string SQLString, string content)

265 {

266 using (SqlConnection connection = new SqlConnection(connectionString))

267 {

268 SqlCommand cmd = new SqlCommand(SQLString, connection);

269 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);

270 myParameter.Value = content;

271 cmd.Parameters.Add(myParameter);

272 try

273 {

274 connection.Open();

275 object obj = cmd.ExecuteScalar();

276 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

277 {

278 return null;

279 }

280 else

281 {

282 return obj;

283 }

284 }

285 catch (System.Data.SqlClient.SqlException e)

286 {

287 throw e;

288 }

289 finally

290 {

291 cmd.Dispose();

292 connection.Close();

293 }

294 }

295 }

296 ///

297 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)

298 ///

299 /// SQL语句

300 /// 图像字节,数据库的字段类型为image的情况

301 /// 影响的记录数

302 public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)

303 {

304 using (SqlConnection connection = new SqlConnection(connectionString))

305 {

306 SqlCommand cmd = new SqlCommand(strSQL, connection);

307 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);

308 myParameter.Value = fs;

309 cmd.Parameters.Add(myParameter);

310 try

311 {

312 connection.Open();

313 int rows = cmd.ExecuteNonQuery();

314 return rows;

315 }

316 catch (System.Data.SqlClient.SqlException e)

317 {

318 throw e;

319 }

320 finally

321 {

322 cmd.Dispose();

323 connection.Close();

324 }

325 }

326 }

327

328

329 ///

330 /// 执行一条计算查询结果语句，返回查询结果（object）。

331 ///

332 /// 计算查询结果语句

333 /// 查询结果（object）

334 public static object GetSingle(string SQLString)

335 {

336 using (SqlConnection connection = new SqlConnection(connectionString))

337 {

338 using (SqlCommand cmd = new SqlCommand(SQLString, connection))

339 {

340 try

341 {

342 connection.Open();

343 object obj = cmd.ExecuteScalar();

344 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

345 {

346 return null;

347 }

348 else

349 {

350 return obj;

351 }

352 }

353 catch (System.Data.SqlClient.SqlException e)

354 {

355 connection.Close();

356 throw e;

357 }

358 }

359 }

360 }

361 public static object GetSingle(string SQLString, int Times)

362 {

363 using (SqlConnection connection = new SqlConnection(connectionString))

364 {

365 using (SqlCommand cmd = new SqlCommand(SQLString, connection))

366 {

367 try

368 {

369 connection.Open();

370 cmd.CommandTimeout = Times;

371 object obj = cmd.ExecuteScalar();

372 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

373 {

374 return null;

375 }

376 else

377 {

378 return obj;

379 }

380 }

381 catch (System.Data.SqlClient.SqlException e)

382 {

383 connection.Close();

384 throw e;

385 }

386 }

387 }

388 }

389 ///

390 /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )

391 ///

392 /// 查询语句

393 /// SqlDataReader

394 public static SqlDataReader ExecuteReader(string strSQL)

395 {

396 SqlConnection connection = new SqlConnection(connectionString);

397 SqlCommand cmd = new SqlCommand(strSQL, connection);

398 try

399 {

400 connection.Open();

401 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

402 return myReader;

403 }

404 catch (System.Data.SqlClient.SqlException e)

405 {

406 throw e;

407 }

408

409

410 }

411 ///

412 /// 执行查询语句，返回DataSet

413 ///

414 /// 查询语句

415 /// DataSet

416 public static DataSet Query(string SQLString)

417 {

418 using (SqlConnection connection = new SqlConnection(connectionString))

419 {

420 DataSet ds = new DataSet();

421 try

422 {

423 connection.Open();

424 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);

425 command.Fill(ds, "ds");

426 }

427 catch (System.Data.SqlClient.SqlException ex)

428 {

429 //throw new Exception(ex.Message);

430 }

431 return ds;

432 }

433 }

434 public static DataSet Query(string SQLString, int Times)

435 {

436 using (SqlConnection connection = new SqlConnection(connectionString))

437 {

438 DataSet ds = new DataSet();

439 try

440 {

441 connection.Open();

442 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);

443 command.SelectCommand.CommandTimeout = Times;

444 command.Fill(ds, "ds");

445 }

446 catch (System.Data.SqlClient.SqlException ex)

447 {

448 throw new Exception(ex.Message);

449 }

450 return ds;

451 }

452 }

453

454

455

456

457

458

459 #endregion

460

461

462 #region 执行带参数的SQL语句

463

464

465 ///

466 /// 执行SQL语句，返回影响的记录数

467 ///

468 /// SQL语句

469 /// 影响的记录数

470 public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)

471 {

472 using (SqlConnection connection = new SqlConnection(connectionString))

473 {

474 using (SqlCommand cmd = new SqlCommand())

475 {

476 try

477 {

478 PrepareCommand(cmd, connection, null, SQLString, cmdParms);

479 int rows = cmd.ExecuteNonQuery();

480 cmd.Parameters.Clear();

481 return rows;

482 }

483 catch (System.Data.SqlClient.SqlException e)

484 {

485 throw e;

486 }

487 }

488 }

489 }

490

491

492

493

494 ///

495 /// 执行多条SQL语句，实现数据库事务。

496 ///

497 /// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）

498 public static void ExecuteSqlTran(Hashtable SQLStringList)

499 {

500 using (SqlConnection conn = new SqlConnection(connectionString))

501 {

502 conn.Open();

503 using (SqlTransaction trans = conn.BeginTransaction())

504 {

505 SqlCommand cmd = new SqlCommand();

506 try

507 {

508 //循环

509 foreach (DictionaryEntry myDE in SQLStringList)

510 {

511 string cmdText = myDE.Key.ToString();

512 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;

513 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

514 int val = cmd.ExecuteNonQuery();

515 cmd.Parameters.Clear();

516 }

517 trans.Commit();

518 }

519 catch

520 {

521 trans.Rollback();

522 throw;

523 }

524 }

525 }

526 }

527

528

529 ///

530 /// 执行多条SQL语句，实现数据库事务。

531 ///

532 /// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）

533 public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)

534 {

535 using (SqlConnection conn = new SqlConnection(connectionString))

536 {

537 conn.Open();

538 using (SqlTransaction trans = conn.BeginTransaction())

539 {

540 SqlCommand cmd = new SqlCommand();

541 try

542 {

543 int indentity = 0;

544 //循环

545 foreach (DictionaryEntry myDE in SQLStringList)

546 {

547 string cmdText = myDE.Key.ToString();

548 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;

549 foreach (SqlParameter q in cmdParms)

550 {

551 if (q.Direction == ParameterDirection.InputOutput)

552 {

553 q.Value = indentity;

554 }

555 }

556 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

557 int val = cmd.ExecuteNonQuery();

558 foreach (SqlParameter q in cmdParms)

559 {

560 if (q.Direction == ParameterDirection.Output)

561 {

562 indentity = Convert.ToInt32(q.Value);

563 }

564 }

565 cmd.Parameters.Clear();

566 }

567 trans.Commit();

568 }

569 catch

570 {

571 trans.Rollback();

572 throw;

573 }

574 }

575 }

576 }

577 ///

578 /// 执行一条计算查询结果语句，返回查询结果（object）。

579 ///

580 /// 计算查询结果语句

581 /// 查询结果（object）

582 public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)

583 {

584 using (SqlConnection connection = new SqlConnection(connectionString))

585 {

586 using (SqlCommand cmd = new SqlCommand())

587 {

588 try

589 {

590 PrepareCommand(cmd, connection, null, SQLString, cmdParms);

591 object obj = cmd.ExecuteScalar();

592 cmd.Parameters.Clear();

593 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

594 {

595 return null;

596 }

597 else

598 {

599 return obj;

600 }

601 }

602 catch (System.Data.SqlClient.SqlException e)

603 {

604 throw e;

605 }

606 }

607 }

608 }

609

610

611 ///

612 /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )

613 ///

614 /// 查询语句

615 /// SqlDataReader

616 public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)

617 {

618 SqlConnection connection = new SqlConnection(connectionString);

619 SqlCommand cmd = new SqlCommand();

620 try

621 {

622 PrepareCommand(cmd, connection, null, SQLString, cmdParms);

623 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

624 cmd.Parameters.Clear();

625 return myReader;

626 }

627 catch (System.Data.SqlClient.SqlException e)

628 {

629 throw e;

630 }

631 // finally

632 // {

633 // cmd.Dispose();

634 // connection.Close();

635 // }

636

637

638 }

639

640

641 ///

642 /// 执行查询语句，返回DataSet

643 ///

644 /// 查询语句

645 /// DataSet

646 public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)

647 {

648 using (SqlConnection connection = new SqlConnection(connectionString))

649 {

650 SqlCommand cmd = new SqlCommand();

651 PrepareCommand(cmd, connection, null, SQLString, cmdParms);

652 using (SqlDataAdapter da = new SqlDataAdapter(cmd))

653 {

654 DataSet ds = new DataSet();

655 try

656 {

657 da.Fill(ds, "ds");

658 cmd.Parameters.Clear();

659 }

660 catch (System.Data.SqlClient.SqlException ex)

661 {

662 throw new Exception(ex.Message);

663 }

664 return ds;

665 }

666 }

667 }

668

669

670

671

672 private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)

673 {

674 if (conn.State != ConnectionState.Open)

675 conn.Open();

676 cmd.Connection = conn;

677 cmd.CommandText = cmdText;

678 if (trans != null)

679 cmd.Transaction = trans;

680 cmd.CommandType = CommandType.Text;//cmdType;

681 if (cmdParms != null)

682 {

683 foreach (SqlParameter parameter in cmdParms)

684 {

685 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&

686 (parameter.Value == null))

687 {

688 parameter.Value = DBNull.Value;

689 }

690 cmd.Parameters.Add(parameter);

691 }

692 }

693 }

694

695

696 #endregion

697

698

699 #region 存储过程操作

700

701

702 ///

703 /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )

704 ///

705 /// 存储过程名

706 /// 存储过程参数

707 /// SqlDataReader

708 public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)

709 {

710 SqlConnection connection = new SqlConnection(connectionString);

711 SqlDataReader returnReader;

712 connection.Open();

713 SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);

714 command.CommandType = CommandType.StoredProcedure;

715 returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);

716 return returnReader;

717

718 }

719

720

721

722

723 ///

724 /// 执行存储过程

725 ///

726 /// 存储过程名

727 /// 存储过程参数

728 /// DataSet结果中的表名

729 /// DataSet

730 public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)

731 {

732 using (SqlConnection connection = new SqlConnection(connectionString))

733 {

734 DataSet dataSet = new DataSet();

735 connection.Open();

736 SqlDataAdapter sqlDA = new SqlDataAdapter();

737 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);

738 sqlDA.Fill(dataSet, tableName);

739 connection.Close();

740 return dataSet;

741 }

742 }

743 public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)

744 {

745 using (SqlConnection connection = new SqlConnection(connectionString))

746 {

747 DataSet dataSet = new DataSet();

748 connection.Open();

749 SqlDataAdapter sqlDA = new SqlDataAdapter();

750 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);

751 sqlDA.SelectCommand.CommandTimeout = Times;

752 sqlDA.Fill(dataSet, tableName);

753 connection.Close();

754 return dataSet;

755 }

756 }

757

758

759

760

761 ///

762 /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)

763 ///

764 /// 数据库连接

765 /// 存储过程名

766 /// 存储过程参数

767 /// SqlCommand

768 private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)

769 {

770 SqlCommand command = new SqlCommand(storedProcName, connection);

771 command.CommandType = CommandType.StoredProcedure;

772 foreach (SqlParameter parameter in parameters)

773 {

774 if (parameter != null)

775 {

776 // 检查未分配值的输出参数,将其分配以DBNull.Value.

777 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&

778 (parameter.Value == null))

779 {

780 parameter.Value = DBNull.Value;

781 }

782 command.Parameters.Add(parameter);

783 }

784 }

785

786

787 return command;

788 }

789

790

791 ///

792 /// 执行存储过程，返回影响的行数

793 ///

794 /// 存储过程名

795 /// 存储过程参数

796 /// 影响的行数

797 ///

798 public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)

799 {

800 using (SqlConnection connection = new SqlConnection(connectionString))

801 {

802 int result;

803 connection.Open();

804 SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);

805 rowsAffected = command.ExecuteNonQuery();

806 result = (int)command.Parameters["ReturnValue"].Value;

807 //Connection.Close();

808 return result;

809 }

810 }

811

812

813 ///

814 /// 创建 SqlCommand 对象实例(用来返回一个整数值)

815 ///

816 /// 存储过程名

817 /// 存储过程参数

818 /// SqlCommand 对象实例

819 private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)

820 {

821 SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);

822 command.Parameters.Add(new SqlParameter("ReturnValue",

823 SqlDbType.Int, 4, ParameterDirection.ReturnValue,

824 false, 0, 0, string.Empty, DataRowVersion.Default, null));

825 return command;

826 }

827 #endregion

828

829

830 }

831

832

833 }