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 }