mysql 数据库dbhelper_关于MySql的DBHelper类以及数据分页

1 usingSystem;2 usingSystem.Collections;3 usingSystem.Collections.Specialized;4 usingSystem.Data;5 usingMySql.Data.MySqlClient;6 usingSystem.Configuration;7 usingSystem.Data.Common;8 usingSystem.Collections.Generic;9 namespaceZHUAO.DBUtility10{11 ///

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

13 ///Copyright (C) Maticsoft

14 ///

15 public abstract classDbHelperMySQL16{17 //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.

18 public static string connectionString =PubConstant.ConnectionString;19 publicDbHelperMySQL()20{21}22

23 #region 公用方法

24 ///

25 ///得到最大值

26 ///

27 ///

28 ///

29 ///

30 public static int GetMaxID(string FieldName, stringTableName)31{32 string strsql = "select max(" + FieldName + ")+1 from" +TableName;33 object obj =GetSingle(strsql);34 if (obj == null)35{36 return 1;37}38 else

39{40 return int.Parse(obj.ToString());41}42}43 ///

44 ///是否存在

45 ///

46 ///

47 ///

48 public static bool Exists(stringstrSql)49{50 object obj =GetSingle(strSql);51 intcmdresult;52 if ((Object.Equals(obj, null)) ||(Object.Equals(obj, System.DBNull.Value)))53{54 cmdresult = 0;55}56 else

57{58 cmdresult = int.Parse(obj.ToString());59}60 if (cmdresult == 0)61{62 return false;63}64 else

65{66 return true;67}68}69 ///

70 ///是否存在(基于MySqlParameter)

71 ///

72 ///

73 ///

74 ///

75 public static bool Exists(string strSql, paramsMySqlParameter[] cmdParms)76{77 object obj =GetSingle(strSql, cmdParms);78 intcmdresult;79 if ((Object.Equals(obj, null)) ||(Object.Equals(obj, System.DBNull.Value)))80{81 cmdresult = 0;82}83 else

84{85 cmdresult = int.Parse(obj.ToString());86}87 if (cmdresult == 0)88{89 return false;90}91 else

92{93 return true;94}95}96 #endregion

97

98 #region 执行简单SQL语句

99

100 ///

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

102 ///

103 /// SQL语句

104 /// 影响的记录数

105 public static int ExecuteSql(stringSQLString)106{107 using (MySqlConnection connection = newMySqlConnection(connectionString))108{109 using (MySqlCommand cmd = newMySqlCommand(SQLString, connection))110{111 try

112{113connection.Open();114 int rows =cmd.ExecuteNonQuery();115 returnrows;116}117 catch(MySql.Data.MySqlClient.MySqlException e)118{119connection.Close();120 throwe;121}122}123}124}125

126 public static int ExecuteSqlByTime(string SQLString, intTimes)127{128 using (MySqlConnection connection = newMySqlConnection(connectionString))129{130 using (MySqlCommand cmd = newMySqlCommand(SQLString, connection))131{132 try

133{134connection.Open();135 cmd.CommandTimeout =Times;136 int rows =cmd.ExecuteNonQuery();137 returnrows;138}139 catch(MySql.Data.MySqlClient.MySqlException e)140{141connection.Close();142 throwe;143}144}145}146}147

148 ///

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

150 ///

151 /// 多条SQL语句

152 public static int ExecuteSqlTran(ListSQLStringList)153{154 using (MySqlConnection conn = newMySqlConnection(connectionString))155{156conn.Open();157 MySqlCommand cmd = newMySqlCommand();158 cmd.Connection =conn;159 MySqlTransaction tx =conn.BeginTransaction();160 cmd.Transaction =tx;161 try

162{163 int count = 0;164 for (int n = 0; n < SQLStringList.Count; n++)165{166 string strsql =SQLStringList[n];167 if (strsql.Trim().Length > 1)168{169 cmd.CommandText =strsql;170 count +=cmd.ExecuteNonQuery();171}172}173tx.Commit();174 returncount;175}176 catch

177{178tx.Rollback();179 return 0;180}181}182}183 ///

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

185 ///

186 /// SQL语句

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

188 /// 影响的记录数

189 public static int ExecuteSql(string SQLString, stringcontent)190{191 using (MySqlConnection connection = newMySqlConnection(connectionString))192{193 MySqlCommand cmd = newMySqlCommand(SQLString, connection);194 MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);195 myParameter.Value =content;196cmd.Parameters.Add(myParameter);197 try

198{199connection.Open();200 int rows =cmd.ExecuteNonQuery();201 returnrows;202}203 catch(MySql.Data.MySqlClient.MySqlException e)204{205 throwe;206}207 finally

208{209cmd.Dispose();210connection.Close();211}212}213}214 ///

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

216 ///

217 /// SQL语句

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

219 /// 影响的记录数

220 public static object ExecuteSqlGet(string SQLString, stringcontent)221{222 using (MySqlConnection connection = newMySqlConnection(connectionString))223{224 MySqlCommand cmd = newMySqlCommand(SQLString, connection);225 MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);226 myParameter.Value =content;227cmd.Parameters.Add(myParameter);228 try

229{230connection.Open();231 object obj =cmd.ExecuteScalar();232 if ((Object.Equals(obj, null)) ||(Object.Equals(obj, System.DBNull.Value)))233{234 return null;235}236 else

237{238 returnobj;239}240}241 catch(MySql.Data.MySqlClient.MySqlException e)242{243 throwe;244}245 finally

246{247cmd.Dispose();248connection.Close();249}250}251}252 ///

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

254 ///

255 /// SQL语句

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

257 /// 影响的记录数

258 public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)259{260 using (MySqlConnection connection = newMySqlConnection(connectionString))261{262 MySqlCommand cmd = newMySqlCommand(strSQL, connection);263 MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@fs", SqlDbType.Image);264 myParameter.Value =fs;265cmd.Parameters.Add(myParameter);266 try

267{268connection.Open();269 int rows =cmd.ExecuteNonQuery();270 returnrows;271}272 catch(MySql.Data.MySqlClient.MySqlException e)273{274 throwe;275}276 finally

277{278cmd.Dispose();279connection.Close();280}281}282}283

284 ///

285 ///执行一条计算查询结果语句，返回查询结果(object)。

286 ///

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

288 /// 查询结果(object)

289 public static object GetSingle(stringSQLString)290{291 using (MySqlConnection connection = newMySqlConnection(connectionString))292{293 using (MySqlCommand cmd = newMySqlCommand(SQLString, connection))294{295 try

296{297connection.Open();298 object obj =cmd.ExecuteScalar();299 if ((Object.Equals(obj, null)) ||(Object.Equals(obj, System.DBNull.Value)))300{301 return null;302}303 else

304{305 returnobj;306}307}308 catch(MySql.Data.MySqlClient.MySqlException e)309{310connection.Close();311 throwe;312}313}314}315}316 public static object GetSingle(string SQLString, intTimes)317{318 using (MySqlConnection connection = newMySqlConnection(connectionString))319{320 using (MySqlCommand cmd = newMySqlCommand(SQLString, connection))321{322 try

323{324connection.Open();325 cmd.CommandTimeout =Times;326 object obj =cmd.ExecuteScalar();327 if ((Object.Equals(obj, null)) ||(Object.Equals(obj, System.DBNull.Value)))328{329 return null;330}331 else

332{333 returnobj;334}335}336 catch(MySql.Data.MySqlClient.MySqlException e)337{338connection.Close();339 throwe;340}341}342}343}344 ///

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

346 ///

347 /// 查询语句

348 /// MySqlDataReader

349 public static MySqlDataReader ExecuteReader(stringstrSQL)350{351 MySqlConnection connection = newMySqlConnection(connectionString);352 MySqlCommand cmd = newMySqlCommand(strSQL, connection);353 try

354{355connection.Open();356 MySqlDataReader myReader =cmd.ExecuteReader(CommandBehavior.CloseConnection);357 returnmyReader;358}359 catch(MySql.Data.MySqlClient.MySqlException e)360{361 throwe;362}363

364}365 ///

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

367 ///

368 /// 查询语句

369 /// DataSet

370 public static DataSet Query(stringSQLString)371{372 using (MySqlConnection connection = newMySqlConnection(connectionString))373{374 DataSet ds = newDataSet();375 try

376{377connection.Open();378 MySqlDataAdapter command = newMySqlDataAdapter(SQLString, connection);379 command.Fill(ds, "ds");380}381 catch(MySql.Data.MySqlClient.MySqlException ex)382{383 throw newException(ex.Message);384}385 returnds;386}387}388 public static DataSet Query(string SQLString, intTimes)389{390 using (MySqlConnection connection = newMySqlConnection(connectionString))391{392 DataSet ds = newDataSet();393 try

394{395connection.Open();396 MySqlDataAdapter command = newMySqlDataAdapter(SQLString, connection);397 command.SelectCommand.CommandTimeout =Times;398 command.Fill(ds, "ds");399}400 catch(MySql.Data.MySqlClient.MySqlException ex)401{402 throw newException(ex.Message);403}404 returnds;405}406}407

408

409

410 #endregion

411

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

413

414 ///

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

416 ///

417 /// SQL语句

418 /// 影响的记录数

419 public static int ExecuteSql(string SQLString, paramsMySqlParameter[] cmdParms)420{421 using (MySqlConnection connection = newMySqlConnection(connectionString))422{423 using (MySqlCommand cmd = newMySqlCommand())424{425 try

426{427 PrepareCommand(cmd, connection, null, SQLString, cmdParms);428 int rows =cmd.ExecuteNonQuery();429cmd.Parameters.Clear();430 returnrows;431}432 catch(MySql.Data.MySqlClient.MySqlException e)433{434 throwe;435}436}437}438}439

440

441 ///

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

443 ///

444 /// SQL语句的哈希表(key为sql语句，value是该语句的MySqlParameter[])

445 public static voidExecuteSqlTran(Hashtable SQLStringList)446{447 using (MySqlConnection conn = newMySqlConnection(connectionString))448{449conn.Open();450 using (MySqlTransaction trans =conn.BeginTransaction())451{452 MySqlCommand cmd = newMySqlCommand();453 try

454{455 //循环

456 foreach (DictionaryEntry myDE inSQLStringList)457{458 string cmdText =myDE.Key.ToString();459 MySqlParameter[] cmdParms =(MySqlParameter[])myDE.Value;460PrepareCommand(cmd, conn, trans, cmdText, cmdParms);461 int val =cmd.ExecuteNonQuery();462cmd.Parameters.Clear();463}464trans.Commit();465}466 catch

467{468trans.Rollback();469 throw;470}471}472}473}474 ///

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

476 ///

477 /// SQL语句的哈希表(key为sql语句，value是该语句的MySqlParameter[])

478 public static int ExecuteSqlTran(System.Collections.Generic.ListcmdList)479{480 using (MySqlConnection conn = newMySqlConnection(connectionString))481{482conn.Open();483 using (MySqlTransaction trans =conn.BeginTransaction())484{485 MySqlCommand cmd = newMySqlCommand();486 try

487 { int count = 0;488 //循环

489 foreach (CommandInfo myDE incmdList)490{491 string cmdText =myDE.CommandText;492 MySqlParameter[] cmdParms =(MySqlParameter[])myDE.Parameters;493PrepareCommand(cmd, conn, trans, cmdText, cmdParms);494

495 if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType ==EffentNextType.WhenNoHaveContine)496{497 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)498{499trans.Rollback();500 return 0;501}502

503 object obj =cmd.ExecuteScalar();504 bool isHave = false;505 if (obj == null && obj ==DBNull.Value)506{507 isHave = false;508}509 isHave = Convert.ToInt32(obj) > 0;510

511 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)512{513trans.Rollback();514 return 0;515}516 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine &&isHave)517{518trans.Rollback();519 return 0;520}521 continue;522}523 int val =cmd.ExecuteNonQuery();524 count +=val;525 if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)526{527trans.Rollback();528 return 0;529}530cmd.Parameters.Clear();531}532trans.Commit();533 returncount;534}535 catch

536{537trans.Rollback();538 throw;539}540}541}542}543 ///

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

545 ///

546 /// SQL语句的哈希表(key为sql语句，value是该语句的MySqlParameter[])

547 public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.ListSQLStringList)548{549 using (MySqlConnection conn = newMySqlConnection(connectionString))550{551conn.Open();552 using (MySqlTransaction trans =conn.BeginTransaction())553{554 MySqlCommand cmd = newMySqlCommand();555 try

556{557 int indentity = 0;558 //循环

559 foreach (CommandInfo myDE inSQLStringList)560{561 string cmdText =myDE.CommandText;562 MySqlParameter[] cmdParms =(MySqlParameter[])myDE.Parameters;563 foreach (MySqlParameter q incmdParms)564{565 if (q.Direction ==ParameterDirection.InputOutput)566{567 q.Value =indentity;568}569}570PrepareCommand(cmd, conn, trans, cmdText, cmdParms);571 int val =cmd.ExecuteNonQuery();572 foreach (MySqlParameter q incmdParms)573{574 if (q.Direction ==ParameterDirection.Output)575{576 indentity =Convert.ToInt32(q.Value);577}578}579cmd.Parameters.Clear();580}581trans.Commit();582}583 catch

584{585trans.Rollback();586 throw;587}588}589}590}591 ///

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

593 ///

594 /// SQL语句的哈希表(key为sql语句，value是该语句的MySqlParameter[])

595 public static voidExecuteSqlTranWithIndentity(Hashtable SQLStringList)596{597 using (MySqlConnection conn = newMySqlConnection(connectionString))598{599conn.Open();600 using (MySqlTransaction trans =conn.BeginTransaction())601{602 MySqlCommand cmd = newMySqlCommand();603 try

604{605 int indentity = 0;606 //循环

607 foreach (DictionaryEntry myDE inSQLStringList)608{609 string cmdText =myDE.Key.ToString();610 MySqlParameter[] cmdParms =(MySqlParameter[])myDE.Value;611 foreach (MySqlParameter q incmdParms)612{613 if (q.Direction ==ParameterDirection.InputOutput)614{615 q.Value =indentity;616}617}618PrepareCommand(cmd, conn, trans, cmdText, cmdParms);619 int val =cmd.ExecuteNonQuery();620 foreach (MySqlParameter q incmdParms)621{622 if (q.Direction ==ParameterDirection.Output)623{624 indentity =Convert.ToInt32(q.Value);625}626}627cmd.Parameters.Clear();628}629trans.Commit();630}631 catch

632{633trans.Rollback();634 throw;635}636}637}638}639 ///

640 ///执行一条计算查询结果语句，返回查询结果(object)。

641 ///

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

643 /// 查询结果(object)

644 public static object GetSingle(string SQLString, paramsMySqlParameter[] cmdParms)645{646 using (MySqlConnection connection = newMySqlConnection(connectionString))647{648 using (MySqlCommand cmd = newMySqlCommand())649{650 try

651{652 PrepareCommand(cmd, connection, null, SQLString, cmdParms);653 object obj =cmd.ExecuteScalar();654cmd.Parameters.Clear();655 if ((Object.Equals(obj, null)) ||(Object.Equals(obj, System.DBNull.Value)))656{657 return null;658}659 else

660{661 returnobj;662}663}664 catch(MySql.Data.MySqlClient.MySqlException e)665{666 throwe;667}668}669}670}671

672 ///

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

674 ///

675 /// 查询语句

676 /// MySqlDataReader

677 public static MySqlDataReader ExecuteReader(string SQLString, paramsMySqlParameter[] cmdParms)678{679 MySqlConnection connection = newMySqlConnection(connectionString);680 MySqlCommand cmd = newMySqlCommand();681 try

682{683 PrepareCommand(cmd, connection, null, SQLString, cmdParms);684 MySqlDataReader myReader =cmd.ExecuteReader(CommandBehavior.CloseConnection);685cmd.Parameters.Clear();686 returnmyReader;687}688 catch(MySql.Data.MySqlClient.MySqlException e)689{690 throwe;691}692 //finally

693 //{

694 //cmd.Dispose();

695 //connection.Close();

696 //}

697

698}699

700 ///

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

702 ///

703 /// 查询语句

704 /// DataSet

705 public static DataSet Query(string SQLString, paramsMySqlParameter[] cmdParms)706{707 using (MySqlConnection connection = newMySqlConnection(connectionString))708{709 MySqlCommand cmd = newMySqlCommand();710 PrepareCommand(cmd, connection, null, SQLString, cmdParms);711 using (MySqlDataAdapter da = newMySqlDataAdapter(cmd))712{713 DataSet ds = newDataSet();714 try

715{716 da.Fill(ds, "ds");717cmd.Parameters.Clear();718}719 catch(MySql.Data.MySqlClient.MySqlException ex)720{721 throw newException(ex.Message);722}723 returnds;724}725}726}727

728

729 private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, stringcmdText, MySqlParameter[] cmdParms)730{731 if (conn.State !=ConnectionState.Open)732conn.Open();733 cmd.Connection =conn;734 cmd.CommandText =cmdText;735 if (trans != null)736 cmd.Transaction =trans;737 cmd.CommandType = CommandType.Text;//cmdType;

738 if (cmdParms != null)739{740

741

742 foreach (MySqlParameter parameter incmdParms)743{744 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&

745 (parameter.Value == null))746{747 parameter.Value =DBNull.Value;748}749cmd.Parameters.Add(parameter);750}751}752}753

754 #endregion

755

756

757

758}759

760}