c# mysql 工具类_MySqlHelper c#访问MySql的工具类

1 /*----------------------------------------------------------------2 // File Name：MYSQLHelper.cs3 // File Description：4 * DataBase Deal Layer5 *6 // Create Mark：7 * Create Date: 2011-04-148 * Create By: Mike.Jiang9 // Modify Mark：10 * Modify Date11 * Modify By12 //----------------------------------------------------------------*/

13 usingSystem;14 usingSystem.Collections.Generic;15 usingSystem.Text;16 usingSystem.Data;17 usingSystem.Collections;18 usingMySql.Data.MySqlClient;19 usingSystem.Configuration;20 usingSystem.IO;21

22

23 namespacePLA.DBUtility24 {25 ///

26 ///when use mysql database application this class27 ///Generic database access code28 ///this class that is a abstract,which does not allow instantiation, the application can directly call it29 ///

30 public abstract classMYSQLHelper31 {32 //Get the database connectionstring, which are static variables and readonly, all project documents can be used directly, but can not modify it33 //the database connectionString34 //public static readonly string connectionStringManager = ConfigurationManager.ConnectionStrings["MYSQLConnectionString"].ConnectionString;

35 public static stringConnectionStringManager {36 get{returnconnectionStringManager; }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 argument45 //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 it47 //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(newHashtable());50

51 ///

52 ///Execute a SqlCommand command that does not return value, by appointed and specified connectionstring53 ///The parameter list using parameters that in array forms54 ///

55 ///

56 ///Usage example:57 ///int result = ExecuteNonQuery(connString, CommandType.StoredProcedure,58 ///"PublishOrders", new MySqlParameter("@prodid", 24));59 ///

60 /// a valid database connectionstring

61 /// MySqlCommand command type (stored procedures, T-SQL statement, and so on.)

62 /// stored procedure name or T-SQL statement

63 /// MySqlCommand to provide an array of parameters used in the list

64 /// Returns a value that means number of rows affected/returns>

65 public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, paramsMySqlParameter[] commandParameters)66 {67 MySqlCommand cmd = newMySqlCommand();68

69 using (MySqlConnection conn = newMySqlConnection(connectionString))70 {71 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);72 int val =cmd.ExecuteNonQuery();73 cmd.Parameters.Clear();74 returnval;75 }76 }77

78 ///

79 ///Execute a SqlCommand command that does not return value, by appointed and specified connectionstring80 ///The parameter list using parameters that in array forms81 ///

82 ///

83 ///Usage example:84 ///int result = ExecuteNonQuery(connString, CommandType.StoredProcedure,85 ///"PublishOrders", new MySqlParameter("@prodid", 24));86 ///

87 /// MySqlCommand command type (stored procedures, T-SQL statement, and so on.)

88 /// a valid database connectionstring

89 /// stored procedure name or T-SQL statement

90 /// MySqlCommand to provide an array of parameters used in the list

91 /// Returns true or false

92 public static bool ExecuteNonQuery(CommandType cmdType, string connectionString, string cmdText, paramsMySqlParameter[] commandParameters)93 {94 MySqlCommand cmd = newMySqlCommand();95

96 using (MySqlConnection conn = newMySqlConnection(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 ///

115 ///Execute a SqlCommand command that does not return value, by appointed and specified connectionstring116 ///Array of form parameters using the parameter list117 ///

118 /// connection

119 /// MySqlCommand command type (stored procedures, T-SQL statement, and so on.)

120 /// stored procedure name or T-SQL statement

121 /// MySqlCommand to provide an array of parameters used in the list

122 /// Returns a value that means number of rows affected

123 public static int ExecuteNonQuery(MySqlConnection conn, CommandType cmdType, string cmdText, paramsMySqlParameter[] commandParameters)124 {125 MySqlCommand cmd = newMySqlCommand();126 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);127 int val =cmd.ExecuteNonQuery();128 cmd.Parameters.Clear();129 returnval;130 }131

132 ///

133 ///Execute a SqlCommand command that does not return value, by appointed and specified connectionstring134 ///Array of form parameters using the parameter list135 ///

136 /// sql Connection that has transaction

137 /// SqlCommand command type (stored procedures, T-SQL statement, and so on.)

138 /// stored procedure name or T-SQL statement

139 /// MySqlCommand to provide an array of parameters used in the list

140 /// Returns a value that means number of rows affected

141 public static int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, paramsMySqlParameter[] commandParameters)142 {143 MySqlCommand cmd = newMySqlCommand();144 PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);145 int val =cmd.ExecuteNonQuery();146 cmd.Parameters.Clear();147 returnval;148 }149

150 ///

151 ///Call method of sqldatareader to read data152 ///

153 /// connectionstring

154 /// command type, such as using stored procedures: CommandType.StoredProcedure

155 /// stored procedure name or T-SQL statement

156 /// parameters

157 /// SqlDataReader type of data collection

158 public static MySqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, paramsMySqlParameter[] commandParameters)159 {160 MySqlCommand cmd = newMySqlCommand();161 MySqlConnection conn = newMySqlConnection(connectionString);162

163 //we use a try/catch here because if the method throws an exception we want to164 //close the connection throw code, because no datareader will exist, hence the165 //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 returnrdr;172 }173 catch

174 {175 conn.Close();176 throw;177 }178 }179

180 ///

181 ///use the ExectueScalar to read a single result182 ///

183 /// connectionstring

184 /// command type, such as using stored procedures: CommandType.StoredProcedure

185 /// stored procedure name or T-SQL statement

186 /// parameters

187 /// a value in object type

188 public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, paramsMySqlParameter[] commandParameters)189 {190 MySqlCommand cmd = newMySqlCommand();191

192 using (MySqlConnection connection = newMySqlConnection(connectionString))193 {194 PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);195 object val =cmd.ExecuteScalar();196 cmd.Parameters.Clear();197 returnval;198 }199 }200

201 public static DataSet GetDataSet(string connectionString, string cmdText, paramsMySqlParameter[] commandParameters)202 {203 DataSet retSet = newDataSet();204 using (MySqlDataAdapter msda = newMySqlDataAdapter(cmdText, connectionString))205 {206 msda.Fill(retSet);207 }208 returnretSet;209 }210

211 ///

212 ///cache the parameters in the HashTable213 ///

214 /// hashtable key name

215 /// the parameters that need to cached

216 public static void CacheParameters(string cacheKey, paramsMySqlParameter[] commandParameters)217 {218 parmCache[cacheKey] =commandParameters;219 }220

221 ///

222 ///get parameters in hashtable by cacheKey223 ///

224 /// hashtable key name

225 /// the parameters

226 public static MySqlParameter[] GetCachedParameters(stringcacheKey)227 {228 MySqlParameter[] cachedParms =(MySqlParameter[])parmCache[cacheKey];229

230 if (cachedParms == null)231 return null;232

233 MySqlParameter[] clonedParms = newMySqlParameter[cachedParms.Length];234

235 for (int i = 0, j = cachedParms.Length; i < j; i++)236 clonedParms[i] =(MySqlParameter)((ICloneable)cachedParms[i]).Clone();237

238 returnclonedParms;239 }240

241 ///

242 ///Prepare parameters for the implementation of the command243 ///

244 /// mySqlCommand command

245 /// database connection that is existing

246 /// database transaction processing

247 /// SqlCommand command type (stored procedures, T-SQL statement, and so on.)

248 /// Command text, T-SQL statements such as Select * from Products

249 /// return the command that has parameters

250 private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, stringcmdText, 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 incmdParms)265 cmd.Parameters.Add(parm);266 }267 #region parameters

268 ///

269 ///Set parameters270 ///

271 /// parameter name

272 /// data type

273 /// type size

274 /// input or output

275 /// set the value

276 /// Return parameters that has been assigned

277 public static MySqlParameter CreateParam(string ParamName, MySqlDbType DbType, Int32 Size, ParameterDirection Direction, objectValue)278 {279 MySqlParameter param;280

281

282 if (Size > 0)283 {284 param = newMySqlParameter(ParamName, DbType, Size);285 }286 else

287 {288

289 param = newMySqlParameter(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 returnparam;301 }302

303 ///

304 ///set Input parameters305 ///

306 /// parameter names, such as:@ id

307 /// parameter types, such as: MySqlDbType.Int

308 /// size parameters, such as: the length of character type for the 100

309 /// parameter value to be assigned

310 /// Parameters

311 public static MySqlParameter CreateInParam(string ParamName, MySqlDbType DbType, int Size, objectValue)312 {313 returnCreateParam(ParamName, DbType, Size, ParameterDirection.Input, Value);314 }315

316 ///

317 ///Output parameters318 ///

319 /// parameter names, such as:@ id

320 /// parameter types, such as: MySqlDbType.Int

321 /// size parameters, such as: the length of character type for the 100

322 /// parameter value to be assigned

323 /// Parameters

324 public static MySqlParameter CreateOutParam(string ParamName, MySqlDbType DbType, intSize)325 {326 return CreateParam(ParamName, DbType, Size, ParameterDirection.Output, null);327 }328

329 ///

330 ///Set return parameter value331 ///

332 /// parameter names, such as:@ id

333 /// parameter types, such as: MySqlDbType.Int

334 /// size parameters, such as: the length of character type for the 100

335 /// parameter value to be assigned<

336 /// Parameters

337 public static MySqlParameter CreateReturnParam(string ParamName, MySqlDbType DbType, intSize)338 {339 return CreateParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null);340 }341

342 ///

343 ///Generate paging storedProcedure parameters344 ///

345 /// CurrentPageIndex

346 /// pageSize

347 /// query Condition

348 /// tableName

349 /// columns to query

350 /// sort

351 /// MySqlParameter collection

352 public static MySqlParameter[] GetPageParm(int CurrentIndex, int PageSize, string WhereSql, string TableName, stringColumns, 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 returnparm;364 }365 ///

366 ///Statistics data that in table367 ///

368 /// table name

369 /// Statistics column

370 /// conditions

371 /// Set of parameters

372 public static MySqlParameter[] GetCountParm(string TableName, string Columns, stringWhereSql)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 returnparm;381 }382 ///

383 ///Get the sql that is Sorted384 ///

385 /// sort column and values

386 /// SQL sort string

387 private static stringGetSort(Hashtable sort)388 {389 string str = "";390 int i = 0;391 if (sort != null && sort.Count > 0)392 {393 foreach (DictionaryEntry de insort)394 {395 i++;396 str += de.Key + " " +de.Value;397 if (i !=sort.Count)398 {399 str += ",";400 }401 }402 }403 returnstr;404 }405

406 ///

407 ///execute a trascation include one or more sql sentence(author:donne yin)408 ///

409 ///

410 ///

411 ///

412 ///

413 /// execute trascation result(success: true | fail: false)

414 public static bool ExecuteTransaction(string connectionString, CommandType cmdType, string[] cmdTexts, paramsMySqlParameter[][] 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 = newMySqlCommand();420 cmd.Connection =myConnection;421 cmd.Transaction =myTrans;422

423 try

424 {425 for (int i = 0;i

434 {435 myTrans.Rollback();436 return false;437 }438 finally

439 {440 myConnection.Close();441 }442 return true;443 }444 #endregion

445 }446 }