MySqlHelper c#访问MySql的工具类
2012-06-15 00:18 Mike.Jiang 阅读(10256) 评论(4) 编辑 收藏 举报最近一段时间一直在总结过去一年中的经验教训，突然发现还没毕业那会做的项目：一平台，语言是c#，数据库用MYSQL，所以需要写一个类似于SQLHelper的类，虽然不再使用了，拿出来晒晒，说不定哪天会有人需要，也可以参考下。
View Code
1 /*---------------------------------------------------------------- 2 // File Name：MYSQLHelper.cs 3 // File Description： 4 * DataBase Deal Layer 5 * 6 // Create Mark： 7 * Create Date: 2011-04-14 8 * Create By: Mike.Jiang 9 // Modify Mark： 10 * Modify Date 11 * Modify By 12 //----------------------------------------------------------------*/ 13 using System; 14 using System.Collections.Generic; 15 using System.Text; 16 using System.Data; 17 using System.Collections; 18 using MySql.Data.MySqlClient; 19 using System.Configuration; 20 using System.IO; 21 22 23 namespace PLA.DBUtility 24 { 25 /// <summary> 26 /// when use mysql database application this class 27 /// Generic database access code 28 /// this class that is a abstract,which does not allow instantiation, the application can directly call it 29 /// </summary> 30 public abstract class MYSQLHelper 31 { 32 //Get the database connectionstring, which are static variables and readonly, all project documents can be used directly, but can not modify it 33 //the database connectionString 34 //public static readonly string connectionStringManager = ConfigurationManager.ConnectionStrings["MYSQLConnectionString"].ConnectionString; 35 public static string ConnectionStringManager { 36 get{return connectionStringManager; } 37 } 38 39 40 41 //This connectionString for the local test 42 public static readonly string connectionStringManager = ConfigurationManager.ConnectionStrings["LocalMYSQLConnectionString"].ConnectionString; 43 44 //hashtable to store the parameter information, the hash table can store any type of argument 45 //Here the hashtable is static types of static variables, since it is static, that is a definition of global use. 46 //All parameters are using this hash table, how to ensure that others in the change does not affect their time to read it 47 //Before ,the method can use the lock method to lock the table, does not allow others to modify.when it has readed then unlocked table. 48 //Now .NET provides a HashTable's Synchronized methods to achieve the same function, no need to manually lock, completed directly by the system framework 49 private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); 50 51 /// <summary> 52 /// Execute a SqlCommand command that does not return value, by appointed and specified connectionstring 53 /// The parameter list using parameters that in array forms 54 /// </summary> 55 /// <remarks> 56 /// Usage example: 57 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, 58 /// "PublishOrders", new MySqlParameter("@prodid", 24)); 59 /// </remarks> 60 /// <param name="connectionString">a valid database connectionstring</param> 61 /// <param name="cmdType">MySqlCommand command type (stored procedures, T-SQL statement, and so on.) </param> 62 /// <param name="cmdText">stored procedure name or T-SQL statement</param> 63 /// <param name="commandParameters">MySqlCommand to provide an array of parameters used in the list</param> 64 /// <returns>Returns a value that means number of rows affected/returns> 65 public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) 66 { 67 MySqlCommand cmd = new MySqlCommand(); 68 69 using (MySqlConnection conn = new MySqlConnection(connectionString)) 70 { 71 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); 72 int val = cmd.ExecuteNonQuery(); 73 cmd.Parameters.Clear(); 74 return val; 75 } 76 } 77 78 /// <summary> 79 /// Execute a SqlCommand command that does not return value, by appointed and specified connectionstring 80 /// The parameter list using parameters that in array forms 81 /// </summary> 82 /// <remarks> 83 /// Usage example: 84 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, 85 /// "PublishOrders", new MySqlParameter("@prodid", 24)); 86 /// </remarks> 87 /// <param name="cmdType">MySqlCommand command type (stored procedures, T-SQL statement, and so on.) </param> 88 /// <param name="connectionString">a valid database connectionstring</param> 89 /// <param name="cmdText">stored procedure name or T-SQL statement</param> 90 /// <param name="commandParameters">MySqlCommand to provide an array of parameters used in the list</param> 91 /// <returns>Returns true or false </returns> 92 public static bool ExecuteNonQuery(CommandType cmdType, string connectionString, string cmdText, params MySqlParameter[] commandParameters) 93 { 94 MySqlCommand cmd = new MySqlCommand(); 95 96 using (MySqlConnection conn = new MySqlConnection(connectionString)) 97 { 98 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); 99 try 100 { 101 int val = cmd.ExecuteNonQuery(); 102 return true; 103 } 104 catch 105 { 106 return false; 107 } 108 finally 109 { 110 cmd.Parameters.Clear(); 111 } 112 } 113 } 114 /// <summary> 115 /// Execute a SqlCommand command that does not return value, by appointed and specified connectionstring 116 /// Array of form parameters using the parameter list 117 /// </summary> 118 /// <param name="conn">connection</param> 119 /// <param name="cmdType">MySqlCommand command type (stored procedures, T-SQL statement, and so on.)</param> 120 /// <param name="cmdText">stored procedure name or T-SQL statement</param> 121 /// <param name="commandParameters">MySqlCommand to provide an array of parameters used in the list</param> 122 /// <returns>Returns a value that means number of rows affected</returns> 123 public static int ExecuteNonQuery(MySqlConnection conn, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) 124 { 125 MySqlCommand cmd = new MySqlCommand(); 126 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); 127 int val = cmd.ExecuteNonQuery(); 128 cmd.Parameters.Clear(); 129 return val; 130 } 131 132 /// <summary> 133 /// Execute a SqlCommand command that does not return value, by appointed and specified connectionstring 134 /// Array of form parameters using the parameter list 135 /// </summary> 136 /// <param name="conn">sql Connection that has transaction</param> 137 /// <param name="cmdType">SqlCommand command type (stored procedures, T-SQL statement, and so on.)</param> 138 /// <param name="cmdText">stored procedure name or T-SQL statement</param> 139 /// <param name="commandParameters">MySqlCommand to provide an array of parameters used in the list</param> 140 /// <returns>Returns a value that means number of rows affected </returns> 141 public static int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) 142 { 143 MySqlCommand cmd = new MySqlCommand(); 144 PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters); 145 int val = cmd.ExecuteNonQuery(); 146 cmd.Parameters.Clear(); 147 return val; 148 } 149 150 /// <summary> 151 /// Call method of sqldatareader to read data 152 /// </summary> 153 /// <param name="connectionString">connectionstring</param> 154 /// <param name="cmdType">command type, such as using stored procedures: CommandType.StoredProcedure</param> 155 /// <param name="cmdText">stored procedure name or T-SQL statement</param> 156 /// <param name="commandParameters">parameters</param> 157 /// <returns>SqlDataReader type of data collection</returns> 158 public static MySqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) 159 { 160 MySqlCommand cmd = new MySqlCommand(); 161 MySqlConnection conn = new MySqlConnection(connectionString); 162 163 // we use a try/catch here because if the method throws an exception we want to 164 // close the connection throw code, because no datareader will exist, hence the 165 // commandBehaviour.CloseConnection will not work 166 try 167 { 168 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); 169 MySqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); 170 cmd.Parameters.Clear(); 171 return rdr; 172 } 173 catch 174 { 175 conn.Close(); 176 throw; 177 } 178 } 179 180 /// <summary> 181 /// use the ExectueScalar to read a single result 182 /// </summary> 183 /// <param name="connectionString">connectionstring</param> 184 /// <param name="cmdType">command type, such as using stored procedures: CommandType.StoredProcedure</param> 185 /// <param name="cmdText">stored procedure name or T-SQL statement</param> 186 /// <param name="commandParameters">parameters</param> 187 /// <returns>a value in object type</returns> 188 public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) 189 { 190 MySqlCommand cmd = new MySqlCommand(); 191 192 using (MySqlConnection connection = new MySqlConnection(connectionString)) 193 { 194 PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); 195 object val = cmd.ExecuteScalar(); 196 cmd.Parameters.Clear(); 197 return val; 198 } 199 } 200 201 public static DataSet GetDataSet(string connectionString, string cmdText, params MySqlParameter[] commandParameters) 202 { 203 DataSet retSet = new DataSet(); 204 using (MySqlDataAdapter msda = new MySqlDataAdapter(cmdText, connectionString)) 205 { 206 msda.Fill(retSet); 207 } 208 return retSet; 209 } 210 211 /// <summary> 212 /// cache the parameters in the HashTable 213 /// </summary> 214 /// <param name="cacheKey">hashtable key name</param> 215 /// <param name="commandParameters">the parameters that need to cached</param> 216 public static void CacheParameters(string cacheKey, params MySqlParameter[] commandParameters) 217 { 218 parmCache[cacheKey] = commandParameters; 219 } 220 221 /// <summary> 222 /// get parameters in hashtable by cacheKey 223 /// </summary> 224 /// <param name="cacheKey">hashtable key name</param> 225 /// <returns>the parameters</returns> 226 public static MySqlParameter[] GetCachedParameters(string cacheKey) 227 { 228 MySqlParameter[] cachedParms = (MySqlParameter[])parmCache[cacheKey]; 229 230 if (cachedParms == null) 231 return null; 232 233 MySqlParameter[] clonedParms = new MySqlParameter[cachedParms.Length]; 234 235 for (int i = 0, j = cachedParms.Length; i < j; i++) 236 clonedParms[i] = (MySqlParameter)((ICloneable)cachedParms[i]).Clone(); 237 238 return clonedParms; 239 } 240 241 /// <summary> 242 ///Prepare parameters for the implementation of the command 243 /// </summary> 244 /// <param name="cmd">mySqlCommand command</param> 245 /// <param name="conn">database connection that is existing</param> 246 /// <param name="trans">database transaction processing </param> 247 /// <param name="cmdType">SqlCommand command type (stored procedures, T-SQL statement, and so on.) </param> 248 /// <param name="cmdText">Command text, T-SQL statements such as Select * from Products</param> 249 /// <param name="cmdParms">return the command that has parameters</param> 250 private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms) 251 { 252 if (conn.State != ConnectionState.Open) 253 conn.Open(); 254 255 cmd.Connection = conn; 256 cmd.CommandText = cmdText; 257 258 if (trans != null) 259 cmd.Transaction = trans; 260 261 cmd.CommandType = cmdType; 262 263 if (cmdParms != null) 264 foreach (MySqlParameter parm in cmdParms) 265 cmd.Parameters.Add(parm); 266 } 267 #region parameters 268 /// <summary> 269 /// Set parameters 270 /// </summary> 271 /// <param name="ParamName">parameter name</param> 272 /// <param name="DbType">data type</param> 273 /// <param name="Size">type size</param> 274 /// <param name="Direction">input or output</param> 275 /// <param name="Value">set the value</param> 276 /// <returns>Return parameters that has been assigned</returns> 277 public static MySqlParameter CreateParam(string ParamName, MySqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value) 278 { 279 MySqlParameter param; 280 281 282 if (Size > 0) 283 { 284 param = new MySqlParameter(ParamName, DbType, Size); 285 } 286 else 287 { 288 289 param = new MySqlParameter(ParamName, DbType); 290 } 291 292 293 param.Direction = Direction; 294 if (!(Direction == ParameterDirection.Output && Value == null)) 295 { 296 param.Value = Value; 297 } 298 299 300 return param; 301 } 302 303 /// <summary> 304 /// set Input parameters 305 /// </summary> 306 /// <param name="ParamName">parameter names, such as:@ id </param> 307 /// <param name="DbType">parameter types, such as: MySqlDbType.Int</param> 308 /// <param name="Size">size parameters, such as: the length of character type for the 100</param> 309 /// <param name="Value">parameter value to be assigned</param> 310 /// <returns>Parameters</returns> 311 public static MySqlParameter CreateInParam(string ParamName, MySqlDbType DbType, int Size, object Value) 312 { 313 return CreateParam(ParamName, DbType, Size, ParameterDirection.Input, Value); 314 } 315 316 /// <summary> 317 /// Output parameters 318 /// </summary> 319 /// <param name="ParamName">parameter names, such as:@ id</param> 320 /// <param name="DbType">parameter types, such as: MySqlDbType.Int</param> 321 /// <param name="Size">size parameters, such as: the length of character type for the 100</param> 322 /// <param name="Value">parameter value to be assigned</param> 323 /// <returns>Parameters</returns> 324 public static MySqlParameter CreateOutParam(string ParamName, MySqlDbType DbType, int Size) 325 { 326 return CreateParam(ParamName, DbType, Size, ParameterDirection.Output, null); 327 } 328 329 /// <summary> 330 /// Set return parameter value 331 /// </summary> 332 /// <param name="ParamName">parameter names, such as:@ id</param> 333 /// <param name="DbType">parameter types, such as: MySqlDbType.Int</param> 334 /// <param name="Size">size parameters, such as: the length of character type for the 100</param> 335 /// <param name="Value">parameter value to be assigned<</param> 336 /// <returns>Parameters</returns> 337 public static MySqlParameter CreateReturnParam(string ParamName, MySqlDbType DbType, int Size) 338 { 339 return CreateParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null); 340 } 341 342 /// <summary> 343 /// Generate paging storedProcedure parameters 344 /// </summary> 345 /// <param name="CurrentIndex">CurrentPageIndex</param> 346 /// <param name="PageSize">pageSize</param> 347 /// <param name="WhereSql">query Condition</param> 348 /// <param name="TableName">tableName</param> 349 /// <param name="Columns">columns to query</param> 350 /// <param name="Sort">sort</param> 351 /// <returns>MySqlParameter collection</returns> 352 public static MySqlParameter[] GetPageParm(int CurrentIndex, int PageSize, string WhereSql, string TableName, string Columns, Hashtable Sort) 353 { 354 MySqlParameter[] parm = { 355 MYSQLHelper.CreateInParam("@CurrentIndex", MySqlDbType.Int32, 4, CurrentIndex ), 356 MYSQLHelper.CreateInParam("@PageSize", MySqlDbType.Int32, 4, PageSize ), 357 MYSQLHelper.CreateInParam("@WhereSql", MySqlDbType.VarChar, 2500, WhereSql ), 358 MYSQLHelper.CreateInParam("@TableName", MySqlDbType.VarChar, 20, TableName ), 359 MYSQLHelper.CreateInParam("@Column", MySqlDbType.VarChar, 2500, Columns ), 360 MYSQLHelper.CreateInParam("@Sort", MySqlDbType.VarChar, 50, GetSort(Sort) ), 361 MYSQLHelper.CreateOutParam("@RecordCount", MySqlDbType.Int32, 4 ) 362 }; 363 return parm; 364 } 365 /// <summary> 366 /// Statistics data that in table 367 /// </summary> 368 /// <param name="TableName">table name</param> 369 /// <param name="Columns">Statistics column</param> 370 /// <param name="WhereSql">conditions</param> 371 /// <returns>Set of parameters</returns> 372 public static MySqlParameter[] GetCountParm(string TableName, string Columns, string WhereSql) 373 { 374 MySqlParameter[] parm = { 375 MYSQLHelper.CreateInParam("@TableName", MySqlDbType.VarChar, 20, TableName ), 376 MYSQLHelper.CreateInParam("@CountColumn", MySqlDbType.VarChar, 20, Columns ), 377 MYSQLHelper.CreateInParam("@WhereSql", MySqlDbType.VarChar, 250, WhereSql ), 378 MYSQLHelper.CreateOutParam("@RecordCount", MySqlDbType.Int32, 4 ) 379 }; 380 return parm; 381 } 382 /// <summary> 383 /// Get the sql that is Sorted 384 /// </summary> 385 /// <param name="sort"> sort column and values</param> 386 /// <returns>SQL sort string</returns> 387 private static string GetSort(Hashtable sort) 388 { 389 string str = ""; 390 int i = 0; 391 if (sort != null && sort.Count > 0) 392 { 393 foreach (DictionaryEntry de in sort) 394 { 395 i++; 396 str += de.Key + " " + de.Value; 397 if (i != sort.Count) 398 { 399 str += ","; 400 } 401 } 402 } 403 return str; 404 } 405 406 /// <summary> 407 /// execute a trascation include one or more sql sentence(author:donne yin) 408 /// </summary> 409 /// <param name="connectionString"></param> 410 /// <param name="cmdType"></param> 411 /// <param name="cmdTexts"></param> 412 /// <param name="commandParameters"></param> 413 /// <returns>execute trascation result(success: true | fail: false)</returns> 414 public static bool ExecuteTransaction(string connectionString, CommandType cmdType, string[] cmdTexts, params MySqlParameter[][] commandParameters) 415 { 416 MySqlConnection myConnection = new MySqlConnection(connectionString); //get the connection object 417 myConnection.Open(); //open the connection 418 MySqlTransaction myTrans = myConnection.BeginTransaction(); //begin a trascation 419 MySqlCommand cmd = new MySqlCommand(); 420 cmd.Connection = myConnection; 421 cmd.Transaction = myTrans; 422 423 try 424 { 425 for (int i = 0;i<cmdTexts.Length; i++) 426 { 427 PrepareCommand(cmd, myConnection, null, cmdType, cmdTexts[i], commandParameters[i]); 428 cmd.ExecuteNonQuery(); 429 cmd.Parameters.Clear(); 430 } 431 myTrans.Commit(); 432 } 433 catch 434 { 435 myTrans.Rollback(); 436 return false; 437 } 438 finally 439 { 440 myConnection.Close(); 441 } 442 return true; 443 } 444 #endregion 445 } 446 }
