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}