C#工具类MySqlHelper，基于MySql.Data.MySqlClient封装
源码：
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Threading.Tasks; 6 using System.Data; 7 using MySql.Data.MySqlClient; 8 9 10 namespace Fly.Util.DataBase 11 { 12 /// <summary> 13 /// MySql数据库操作类 14 /// </summary> 15 public static class MySqlHelper 16 { 17 /// <summary> 18 /// 执行数据库非查询操作,返回受影响的行数 19 /// </summary> 20 /// <param name="connectionString">数据库连接字符串</param> 21 /// <param name="cmdType">命令的类型</param> 22 /// <param name="cmdText">MySql存储过程名称或PL/SQL命令</param> 23 /// <param name="cmdParms">命令参数集合</param> 24 /// <returns>当前操作影响的数据行数</returns> 25 public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] cmdParms) 26 { 27 MySqlCommand cmd = new MySqlCommand(); 28 using (MySqlConnection conn = new MySqlConnection(connectionString)) 29 { 30 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 31 int val = cmd.ExecuteNonQuery(); 32 cmd.Parameters.Clear(); 33 return val; 34 } 35 } 36 37 /// <summary> 38 /// 执行数据库事务非查询操作,返回受影响的行数 39 /// </summary> 40 /// <param name="transaction">数据库事务对象</param> 41 /// <param name="cmdType">Command类型</param> 42 /// <param name="cmdText">MySql存储过程名称或PL/SQL命令</param> 43 /// <param name="cmdParms">命令参数集合</param> 44 /// <returns>当前事务操作影响的数据行数</returns> 45 public static int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] cmdParms) 46 { 47 MySqlCommand cmd = new MySqlCommand(); 48 PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms); 49 int val = cmd.ExecuteNonQuery(); 50 cmd.Parameters.Clear(); 51 return val; 52 } 53 54 /// <summary> 55 /// 执行数据库非查询操作,返回受影响的行数 56 /// </summary> 57 /// <param name="connection">MySql数据库连接对象</param> 58 /// <param name="cmdType">Command类型</param> 59 /// <param name="cmdText">MySql存储过程名称或PL/SQL命令</param> 60 /// <param name="cmdParms">命令参数集合</param> 61 /// <returns>当前操作影响的数据行数</returns> 62 public static int ExecuteNonQuery(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] cmdParms) 63 { 64 if (connection == null) 65 throw new ArgumentNullException("当前数据库连接不存在"); 66 MySqlCommand cmd = new MySqlCommand(); 67 PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParms); 68 int val = cmd.ExecuteNonQuery(); 69 cmd.Parameters.Clear(); 70 return val; 71 } 72 73 /// <summary> 74 /// 执行数据库查询操作,返回MySqlDataReader类型的内存结果集 75 /// </summary> 76 /// <param name="connectionString">数据库连接字符串</param> 77 /// <param name="cmdType">命令的类型</param> 78 /// <param name="cmdText">MySql存储过程名称或PL/SQL命令</param> 79 /// <param name="cmdParms">命令参数集合</param> 80 /// <returns>当前查询操作返回的MySqlDataReader类型的内存结果集</returns> 81 public static MySqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] cmdParms) 82 { 83 MySqlCommand cmd = new MySqlCommand(); 84 MySqlConnection conn = new MySqlConnection(connectionString); 85 try 86 { 87 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 88 MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 89 cmd.Parameters.Clear(); 90 return reader; 91 } 92 catch 93 { 94 cmd.Dispose(); 95 conn.Close(); 96 throw; 97 } 98 } 99 100 /// <summary> 101 /// 执行数据库查询操作,返回DataSet类型的结果集 102 /// </summary> 103 /// <param name="connectionString">数据库连接字符串</param> 104 /// <param name="cmdType">命令的类型</param> 105 /// <param name="cmdText">MySql存储过程名称或PL/SQL命令</param> 106 /// <param name="cmdParms">命令参数集合</param> 107 /// <returns>当前查询操作返回的DataSet类型的结果集</returns> 108 public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] cmdParms) 109 { 110 MySqlCommand cmd = new MySqlCommand(); 111 MySqlConnection conn = new MySqlConnection(connectionString); 112 DataSet ds = null; 113 try 114 { 115 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 116 MySqlDataAdapter adapter = new MySqlDataAdapter(); 117 adapter.SelectCommand = cmd; 118 ds = new DataSet(); 119 adapter.Fill(ds); 120 cmd.Parameters.Clear(); 121 } 122 catch 123 { 124 throw; 125 } 126 finally 127 { 128 cmd.Dispose(); 129 conn.Close(); 130 conn.Dispose(); 131 } 132 133 return ds; 134 } 135 136 /// <summary> 137 /// 执行数据库查询操作,返回DataTable类型的结果集 138 /// </summary> 139 /// <param name="connectionString">数据库连接字符串</param> 140 /// <param name="cmdType">命令的类型</param> 141 /// <param name="cmdText">MySql存储过程名称或PL/SQL命令</param> 142 /// <param name="cmdParms">命令参数集合</param> 143 /// <returns>当前查询操作返回的DataTable类型的结果集</returns> 144 public static DataTable ExecuteDataTable(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] cmdParms) 145 { 146 MySqlCommand cmd = new MySqlCommand(); 147 MySqlConnection conn = new MySqlConnection(connectionString); 148 DataTable dt = null; 149 150 try 151 { 152 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 153 MySqlDataAdapter adapter = new MySqlDataAdapter(); 154 adapter.SelectCommand = cmd; 155 dt = new DataTable(); 156 adapter.Fill(dt); 157 cmd.Parameters.Clear(); 158 } 159 catch 160 { 161 throw; 162 } 163 finally 164 { 165 cmd.Dispose(); 166 conn.Close(); 167 conn.Dispose(); 168 } 169 170 return dt; 171 } 172 173 /// <summary> 174 /// 执行数据库查询操作,返回结果集中位于第一行第一列的Object类型的值 175 /// </summary> 176 /// <param name="connectionString">数据库连接字符串</param> 177 /// <param name="cmdType">命令的类型</param> 178 /// <param name="cmdText">MySql存储过程名称或PL/SQL命令</param> 179 /// <param name="cmdParms">命令参数集合</param> 180 /// <returns>当前查询操作返回的结果集中位于第一行第一列的Object类型的值</returns> 181 public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] cmdParms) 182 { 183 MySqlCommand cmd = new MySqlCommand(); 184 MySqlConnection conn = new MySqlConnection(connectionString); 185 object result = null; 186 try 187 { 188 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 189 result = cmd.ExecuteScalar(); 190 cmd.Parameters.Clear(); 191 } 192 catch 193 { 194 throw; 195 } 196 finally 197 { 198 cmd.Dispose(); 199 conn.Close(); 200 conn.Dispose(); 201 } 202 203 return result; 204 } 205 206 /// <summary> 207 /// 执行数据库事务查询操作,返回结果集中位于第一行第一列的Object类型的值 208 /// </summary> 209 /// <param name="trans">一个已存在的数据库事务对象</param> 210 /// <param name="commandType">命令类型</param> 211 /// <param name="commandText">MySql存储过程名称或PL/SQL命令</param> 212 /// <param name="cmdParms">命令参数集合</param> 213 /// <returns>当前事务查询操作返回的结果集中位于第一行第一列的Object类型的值</returns> 214 public static object ExecuteScalar(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] cmdParms) 215 { 216 if (trans == null) 217 throw new ArgumentNullException("当前数据库事务不存在"); 218 MySqlConnection conn = trans.Connection; 219 if (conn == null) 220 throw new ArgumentException("当前事务所在的数据库连接不存在"); 221 222 MySqlCommand cmd = new MySqlCommand(); 223 object result = null; 224 225 try 226 { 227 PrepareCommand(cmd, conn, trans, cmdType, cmdText, cmdParms); 228 result = cmd.ExecuteScalar(); 229 cmd.Parameters.Clear(); 230 } 231 catch 232 { 233 throw; 234 } 235 finally 236 { 237 trans.Dispose(); 238 cmd.Dispose(); 239 conn.Close(); 240 conn.Dispose(); 241 } 242 243 return result; 244 } 245 246 /// <summary> 247 /// 执行数据库查询操作,返回结果集中位于第一行第一列的Object类型的值 248 /// </summary> 249 /// <param name="conn">数据库连接对象</param> 250 /// <param name="cmdType">Command类型</param> 251 /// <param name="cmdText">MySql存储过程名称或PL/SQL命令</param> 252 /// <param name="cmdParms">命令参数集合</param> 253 /// <returns>当前查询操作返回的结果集中位于第一行第一列的Object类型的值</returns> 254 public static object ExecuteScalar(MySqlConnection conn, CommandType cmdType, string cmdText, params MySqlParameter[] cmdParms) 255 { 256 if (conn == null) throw new ArgumentException("当前数据库连接不存在"); 257 MySqlCommand cmd = new MySqlCommand(); 258 object result = null; 259 260 try 261 { 262 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 263 result = cmd.ExecuteScalar(); 264 cmd.Parameters.Clear(); 265 } 266 catch 267 { 268 throw; 269 } 270 finally 271 { 272 cmd.Dispose(); 273 conn.Close(); 274 conn.Dispose(); 275 } 276 277 return result; 278 } 279 280 /// <summary> 281 /// 执行存储过程 282 /// </summary> 283 /// <param name="connection">MySql数据库连接对象</param> 284 /// <param name="storedProcName">存储过程名</param> 285 /// <param name="parameters">存储过程参数</param> 286 /// <returns>SqlDataReader对象</returns> 287 public static MySqlDataReader RunStoredProcedure(MySqlConnection connection, string storedProcName, IDataParameter[] parameters) 288 { 289 MySqlDataReader returnReader = null; 290 connection.Open(); 291 MySqlCommand command = BuildSqlCommand(connection, storedProcName, parameters); 292 returnReader = command.ExecuteReader(CommandBehavior.CloseConnection); 293 return returnReader; 294 } 295 296 /// <summary> 297 /// 执行数据库命令前的准备工作 298 /// </summary> 299 /// <param name="cmd">Command对象</param> 300 /// <param name="conn">数据库连接对象</param> 301 /// <param name="trans">事务对象</param> 302 /// <param name="cmdType">Command类型</param> 303 /// <param name="cmdText">MySql存储过程名称或PL/SQL命令</param> 304 /// <param name="cmdParms">命令参数集合</param> 305 private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms) 306 { 307 if (conn.State != ConnectionState.Open) 308 conn.Open(); 309 310 cmd.Connection = conn; 311 cmd.CommandText = cmdText; 312 313 if (trans != null) 314 cmd.Transaction = trans; 315 316 cmd.CommandType = cmdType; 317 318 if (cmdParms != null) 319 { 320 foreach (MySqlParameter parm in cmdParms) 321 cmd.Parameters.Add(parm); 322 } 323 } 324 325 /// <summary> 326 /// 构建SqlCommand对象 327 /// </summary> 328 /// <param name="connection">数据库连接</param> 329 /// <param name="storedProcName">存储过程名</param> 330 /// <param name="parameters">存储过程参数</param> 331 /// <returns>SqlCommand</returns> 332 private static MySqlCommand BuildSqlCommand(MySqlConnection connection, string storedProcName, IDataParameter[] parameters) 333 { 334 MySqlCommand command = new MySqlCommand(storedProcName, connection); 335 command.CommandType = CommandType.StoredProcedure; 336 foreach (MySqlParameter parameter in parameters) 337 { 338 command.Parameters.Add(parameter); 339 } 340 return command; 341 } 342 } 343 }
