tt c mysql t4 bll_C#操做MySQL数据库 简单三层结构设计UI、BLL、DAL、Model实际项目应用例子...

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 namespaceMaticsoft.DBUtility10 {11 ///

12 ///数据访问抽象基础类13 ///Copyright (C) Maticsoft14 ///

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 {113 connection.Open();114 int rows =cmd.ExecuteNonQuery();115 returnrows;116 }117 catch(MySql.Data.MySqlClient.MySqlException e)118 {119 connection.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 {134 connection.Open();135 cmd.CommandTimeout =Times;136 int rows =cmd.ExecuteNonQuery();137 returnrows;138 }139 catch(MySql.Data.MySqlClient.MySqlException e)140 {141 connection.Close();142 throwe;143 }144 }145 }146 }147

148 ///

149 ///执行MySql和Oracle滴混合事务150 ///

151 /// SQL命令行列表

152 /// Oracle命令行列表

153 /// 执行结果 0-因为SQL形成事务失败 -1 因为Oracle形成事务失败 1-总体事务执行成功

154 public static int ExecuteSqlTran(List list, ListoracleCmdSqlList)155 {156 using (MySqlConnection conn = newMySqlConnection(connectionString))157 {158 conn.Open();159 MySqlCommand cmd = newMySqlCommand();160 cmd.Connection =conn;161 MySqlTransaction tx =conn.BeginTransaction();162 cmd.Transaction =tx;163 try

164 {165 foreach (CommandInfo myDE inlist)166 {167 string cmdText =myDE.CommandText;168 MySqlParameter[] cmdParms =(MySqlParameter[])myDE.Parameters;169 PrepareCommand(cmd, conn, tx, cmdText, cmdParms);170 if (myDE.EffentNextType ==EffentNextType.SolicitationEvent)171 {172 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)173 {174 tx.Rollback();175 throw new Exception("违背要求"+myDE.CommandText+"必须符合select count(..的格式");176 //return 0;

177 }178

179 object obj =cmd.ExecuteScalar();180 bool isHave = false;181 if (obj == null && obj ==DBNull.Value)182 {183 isHave = false;184 }185 isHave = Convert.ToInt32(obj) > 0;186 if(isHave)187 {188 //引起事件

189 myDE.OnSolicitationEvent();190 }191 }192 if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType ==EffentNextType.WhenNoHaveContine)193 {194 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)195 {196 tx.Rollback();197 throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");198 //return 0;

199 }200

201 object obj =cmd.ExecuteScalar();202 bool isHave = false;203 if (obj == null && obj ==DBNull.Value)204 {205 isHave = false;206 }207 isHave = Convert.ToInt32(obj) > 0;208

209 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)210 {211 tx.Rollback();212 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");213 //return 0;

214 }215 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine &&isHave)216 {217 tx.Rollback();218 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");219 //return 0;

220 }221 continue;222 }223 int val =cmd.ExecuteNonQuery();224 if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)225 {226 tx.Rollback();227 throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");228 //return 0;

229 }230 cmd.Parameters.Clear();231 }232 string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");233 bool res =OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);234 if (!res)235 {236 tx.Rollback();237 throw new Exception("执行失败");238 //return -1;

239 }240 tx.Commit();241 return 1;242 }243 catch(MySql.Data.MySqlClient.MySqlException e)244 {245 tx.Rollback();246 throwe;247 }248 catch(Exception e)249 {250 tx.Rollback();251 throwe;252 }253 }254 }255 ///

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

258 /// 多条SQL语句

259 public static int ExecuteSqlTran(ListSQLStringList)260 {261 using (MySqlConnection conn = newMySqlConnection(connectionString))262 {263 conn.Open();264 MySqlCommand cmd = newMySqlCommand();265 cmd.Connection =conn;266 MySqlTransaction tx =conn.BeginTransaction();267 cmd.Transaction =tx;268 try

269 {270 int count = 0;271 for (int n = 0; n < SQLStringList.Count; n++)272 {273 string strsql =SQLStringList[n];274 if (strsql.Trim().Length > 1)275 {276 cmd.CommandText =strsql;277 count +=cmd.ExecuteNonQuery();278 }279 }280 tx.Commit();281 returncount;282 }283 catch

284 {285 tx.Rollback();286 return 0;287 }288 }289 }290 ///

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

293 /// SQL语句

294 /// 参数内容,好比一个字段是格式复杂的文章，有特殊符号，能够经过这个方式添加

295 /// 影响的记录数

296 public static int ExecuteSql(string SQLString, stringcontent)297 {298 using (MySqlConnection connection = newMySqlConnection(connectionString))299 {300 MySqlCommand cmd = newMySqlCommand(SQLString, connection);301 MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);302 myParameter.Value =content;303 cmd.Parameters.Add(myParameter);304 try

305 {306 connection.Open();307 int rows =cmd.ExecuteNonQuery();308 returnrows;309 }310 catch(MySql.Data.MySqlClient.MySqlException e)311 {312 throwe;313 }314 finally

315 {316 cmd.Dispose();317 connection.Close();318 }319 }320 }321 ///

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

324 /// SQL语句

325 /// 参数内容,好比一个字段是格式复杂的文章，有特殊符号，能够经过这个方式添加

326 /// 影响的记录数

327 public static object ExecuteSqlGet(string SQLString, stringcontent)328 {329 using (MySqlConnection connection = newMySqlConnection(connectionString))330 {331 MySqlCommand cmd = newMySqlCommand(SQLString, connection);332 MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);333 myParameter.Value =content;334 cmd.Parameters.Add(myParameter);335 try

336 {337 connection.Open();338 object obj =cmd.ExecuteScalar();339 if ((Object.Equals(obj, null)) ||(Object.Equals(obj, System.DBNull.Value)))340 {341 return null;342 }343 else

344 {345 returnobj;346 }347 }348 catch(MySql.Data.MySqlClient.MySqlException e)349 {350 throwe;351 }352 finally

353 {354 cmd.Dispose();355 connection.Close();356 }357 }358 }359 ///

360 ///向数据库里插入图像格式的字段(和上面状况相似的另外一种实例)361 ///

362 /// SQL语句

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

364 /// 影响的记录数

365 public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)366 {367 using (MySqlConnection connection = newMySqlConnection(connectionString))368 {369 MySqlCommand cmd = newMySqlCommand(strSQL, connection);370 MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@fs", SqlDbType.Image);371 myParameter.Value =fs;372 cmd.Parameters.Add(myParameter);373 try

374 {375 connection.Open();376 int rows =cmd.ExecuteNonQuery();377 returnrows;378 }379 catch(MySql.Data.MySqlClient.MySqlException e)380 {381 throwe;382 }383 finally

384 {385 cmd.Dispose();386 connection.Close();387 }388 }389 }390

391 ///

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

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

395 /// 查询结果(object)

396 public static object GetSingle(stringSQLString)397 {398 using (MySqlConnection connection = newMySqlConnection(connectionString))399 {400 using (MySqlCommand cmd = newMySqlCommand(SQLString, connection))401 {402 try

403 {404 connection.Open();405 object obj =cmd.ExecuteScalar();406 if ((Object.Equals(obj, null)) ||(Object.Equals(obj, System.DBNull.Value)))407 {408 return null;409 }410 else

411 {412 returnobj;413 }414 }415 catch(MySql.Data.MySqlClient.MySqlException e)416 {417 connection.Close();418 throwe;419 }420 }421 }422 }423 public static object GetSingle(string SQLString, intTimes)424 {425 using (MySqlConnection connection = newMySqlConnection(connectionString))426 {427 using (MySqlCommand cmd = newMySqlCommand(SQLString, connection))428 {429 try

430 {431 connection.Open();432 cmd.CommandTimeout =Times;433 object obj =cmd.ExecuteScalar();434 if ((Object.Equals(obj, null)) ||(Object.Equals(obj, System.DBNull.Value)))435 {436 return null;437 }438 else

439 {440 returnobj;441 }442 }443 catch(MySql.Data.MySqlClient.MySqlException e)444 {445 connection.Close();446 throwe;447 }448 }449 }450 }451 ///

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

454 /// 查询语句

455 /// MySqlDataReader

456 public static MySqlDataReader ExecuteReader(stringstrSQL)457 {458 MySqlConnection connection = newMySqlConnection(connectionString);459 MySqlCommand cmd = newMySqlCommand(strSQL, connection);460 try

461 {462 connection.Open();463 MySqlDataReader myReader =cmd.ExecuteReader(CommandBehavior.CloseConnection);464 returnmyReader;465 }466 catch(MySql.Data.MySqlClient.MySqlException e)467 {468 throwe;469 }470

471 }472 ///

473 ///执行查询语句，返回DataSet474 ///

475 /// 查询语句

476 /// DataSet

477 public static DataSet Query(stringSQLString)478 {479 using (MySqlConnection connection = newMySqlConnection(connectionString))480 {481 DataSet ds = newDataSet();482 try

483 {484 connection.Open();485 MySqlDataAdapter command = newMySqlDataAdapter(SQLString, connection);486 command.Fill(ds, "ds");487 }488 catch(MySql.Data.MySqlClient.MySqlException ex)489 {490 throw newException(ex.Message);491 }492 returnds;493 }494 }495 public static DataSet Query(string SQLString, intTimes)496 {497 using (MySqlConnection connection = newMySqlConnection(connectionString))498 {499 DataSet ds = newDataSet();500 try

501 {502 connection.Open();503 MySqlDataAdapter command = newMySqlDataAdapter(SQLString, connection);504 command.SelectCommand.CommandTimeout =Times;505 command.Fill(ds, "ds");506 }507 catch(MySql.Data.MySqlClient.MySqlException ex)508 {509 throw newException(ex.Message);510 }511 returnds;512 }513 }514

515

516

517 #endregion

518

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

520

521 ///

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

524 /// SQL语句

525 /// 影响的记录数

526 public static int ExecuteSql(string SQLString, paramsMySqlParameter[] cmdParms)527 {528 using (MySqlConnection connection = newMySqlConnection(connectionString))529 {530 using (MySqlCommand cmd = newMySqlCommand())531 {532 try

533 {534 PrepareCommand(cmd, connection, null, SQLString, cmdParms);535 int rows =cmd.ExecuteNonQuery();536 cmd.Parameters.Clear();537 returnrows;538 }539 catch(MySql.Data.MySqlClient.MySqlException e)540 {541 throwe;542 }543 }544 }545 }546

547

548 ///

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

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

552 public static voidExecuteSqlTran(Hashtable SQLStringList)553 {554 using (MySqlConnection conn = newMySqlConnection(connectionString))555 {556 conn.Open();557 using (MySqlTransaction trans =conn.BeginTransaction())558 {559 MySqlCommand cmd = newMySqlCommand();560 try

561 {562 //循环

563 foreach (DictionaryEntry myDE inSQLStringList)564 {565 string cmdText =myDE.Key.ToString();566 MySqlParameter[] cmdParms =(MySqlParameter[])myDE.Value;567 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);568 int val =cmd.ExecuteNonQuery();569 cmd.Parameters.Clear();570 }571 trans.Commit();572 }573 catch

574 {575 trans.Rollback();576 throw;577 }578 }579 }580 }581 ///

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

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

585 public static int ExecuteSqlTran(System.Collections.Generic.ListcmdList)586 {587 using (MySqlConnection conn = newMySqlConnection(connectionString))588 {589 conn.Open();590 using (MySqlTransaction trans =conn.BeginTransaction())591 {592 MySqlCommand cmd = newMySqlCommand();593 try

594 { int count = 0;595 //循环

596 foreach (CommandInfo myDE incmdList)597 {598 string cmdText =myDE.CommandText;599 MySqlParameter[] cmdParms =(MySqlParameter[])myDE.Parameters;600 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);601

602 if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType ==EffentNextType.WhenNoHaveContine)603 {604 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)605 {606 trans.Rollback();607 return 0;608 }609

610 object obj =cmd.ExecuteScalar();611 bool isHave = false;612 if (obj == null && obj ==DBNull.Value)613 {614 isHave = false;615 }616 isHave = Convert.ToInt32(obj) > 0;617

618 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)619 {620 trans.Rollback();621 return 0;622 }623 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine &&isHave)624 {625 trans.Rollback();626 return 0;627 }628 continue;629 }630 int val =cmd.ExecuteNonQuery();631 count +=val;632 if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)633 {634 trans.Rollback();635 return 0;636 }637 cmd.Parameters.Clear();638 }639 trans.Commit();640 returncount;641 }642 catch

643 {644 trans.Rollback();645 throw;646 }647 }648 }649 }650 ///

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

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

654 public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.ListSQLStringList)655 {656 using (MySqlConnection conn = newMySqlConnection(connectionString))657 {658 conn.Open();659 using (MySqlTransaction trans =conn.BeginTransaction())660 {661 MySqlCommand cmd = newMySqlCommand();662 try

663 {664 int indentity = 0;665 //循环

666 foreach (CommandInfo myDE inSQLStringList)667 {668 string cmdText =myDE.CommandText;669 MySqlParameter[] cmdParms =(MySqlParameter[])myDE.Parameters;670 foreach (MySqlParameter q incmdParms)671 {672 if (q.Direction ==ParameterDirection.InputOutput)673 {674 q.Value =indentity;675 }676 }677 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);678 int val =cmd.ExecuteNonQuery();679 foreach (MySqlParameter q incmdParms)680 {681 if (q.Direction ==ParameterDirection.Output)682 {683 indentity =Convert.ToInt32(q.Value);684 }685 }686 cmd.Parameters.Clear();687 }688 trans.Commit();689 }690 catch

691 {692 trans.Rollback();693 throw;694 }695 }696 }697 }698 ///

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

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

702 public static voidExecuteSqlTranWithIndentity(Hashtable SQLStringList)703 {704 using (MySqlConnection conn = newMySqlConnection(connectionString))705 {706 conn.Open();707 using (MySqlTransaction trans =conn.BeginTransaction())708 {709 MySqlCommand cmd = newMySqlCommand();710 try

711 {712 int indentity = 0;713 //循环

714 foreach (DictionaryEntry myDE inSQLStringList)715 {716 string cmdText =myDE.Key.ToString();717 MySqlParameter[] cmdParms =(MySqlParameter[])myDE.Value;718 foreach (MySqlParameter q incmdParms)719 {720 if (q.Direction ==ParameterDirection.InputOutput)721 {722 q.Value =indentity;723 }724 }725 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);726 int val =cmd.ExecuteNonQuery();727 foreach (MySqlParameter q incmdParms)728 {729 if (q.Direction ==ParameterDirection.Output)730 {731 indentity =Convert.ToInt32(q.Value);732 }733 }734 cmd.Parameters.Clear();735 }736 trans.Commit();737 }738 catch

739 {740 trans.Rollback();741 throw;742 }743 }744 }745 }746 ///

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

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

750 /// 查询结果(object)

751 public static object GetSingle(string SQLString, paramsMySqlParameter[] cmdParms)752 {753 using (MySqlConnection connection = newMySqlConnection(connectionString))754 {755 using (MySqlCommand cmd = newMySqlCommand())756 {757 try

758 {759 PrepareCommand(cmd, connection, null, SQLString, cmdParms);760 object obj =cmd.ExecuteScalar();761 cmd.Parameters.Clear();762 if ((Object.Equals(obj, null)) ||(Object.Equals(obj, System.DBNull.Value)))763 {764 return null;765 }766 else

767 {768 returnobj;769 }770 }771 catch(MySql.Data.MySqlClient.MySqlException e)772 {773 throwe;774 }775 }776 }777 }778

779 ///

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

782 /// 查询语句

783 /// MySqlDataReader

784 public static MySqlDataReader ExecuteReader(string SQLString, paramsMySqlParameter[] cmdParms)785 {786 MySqlConnection connection = newMySqlConnection(connectionString);787 MySqlCommand cmd = newMySqlCommand();788 try

789 {790 PrepareCommand(cmd, connection, null, SQLString, cmdParms);791 MySqlDataReader myReader =cmd.ExecuteReader(CommandBehavior.CloseConnection);792 cmd.Parameters.Clear();793 returnmyReader;794 }795 catch(MySql.Data.MySqlClient.MySqlException e)796 {797 throwe;798 }799 //finally800 //{801 //cmd.Dispose();802 //connection.Close();803 //}

804

805 }806

807 ///

808 ///执行查询语句，返回DataSet809 ///

810 /// 查询语句

811 /// DataSet

812 public static DataSet Query(string SQLString, paramsMySqlParameter[] cmdParms)813 {814 using (MySqlConnection connection = newMySqlConnection(connectionString))815 {816 MySqlCommand cmd = newMySqlCommand();817 PrepareCommand(cmd, connection, null, SQLString, cmdParms);818 using (MySqlDataAdapter da = newMySqlDataAdapter(cmd))819 {820 DataSet ds = newDataSet();821 try

822 {823 da.Fill(ds, "ds");824 cmd.Parameters.Clear();825 }826 catch(MySql.Data.MySqlClient.MySqlException ex)827 {828 throw newException(ex.Message);829 }830 returnds;831 }832 }833 }834

835

836 private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, stringcmdText, MySqlParameter[] cmdParms)837 {838 if (conn.State !=ConnectionState.Open)839 conn.Open();840 cmd.Connection =conn;841 cmd.CommandText =cmdText;842 if (trans != null)843 cmd.Transaction =trans;844 cmd.CommandType = CommandType.Text;//cmdType;

845 if (cmdParms != null)846 {847

848

849 foreach (MySqlParameter parameter incmdParms)850 {851 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&

852 (parameter.Value == null))853 {854 parameter.Value =DBNull.Value;855 }856 cmd.Parameters.Add(parameter);857 }858 }859 }860

861 #endregion

862

863

864

865 }866

867 }