winfrom连接mysql帮助类_C#访问MySQL数据库帮助类

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.ToInt3