. NET using MySql.Data.dll Dynamic Library Operation MYSQL Help class--mysqlhelper

. NET using MySql.Data.dll Dynamic Library Operation MYSQL Help class--mysqlhelper

Refer to the sample code as follows:

        <summary>///MYSQL Database Operation class///</summary>public class mysqlhelper{#region mysqlconnectionprivate STA Tic MySql.Data.MySqlClient.MySqlConnection _mysqlconnection;///<summary>///mysqlconnection Connection Information///</ Summary>public static MySql.Data.MySqlClient.MySqlConnection mysqlconnection{get{string mysqlconnectionstring = system.configuration.configurationmanager.connectionstrings["Libor_mysql_quotecenter_connectionstring"]. ToString (); if (_mysqlconnection = = null) _mysqlconnection = new MySql.Data.MySqlClient.MySqlConnection ( mysqlconnectionstring); if (_mysqlconnection.state = = System.Data.ConnectionState.Closed) _mysqlconnection.open (); if (_mysqlconnection.state = = System.Data.ConnectionState.Broken) {_mysqlconnection.close (); _mysqlconnection.open ( );} return mysqlhelper._mysqlconnection;}} #endregion #region executes a mysql statement or stored procedure, returns the number of rows affected//<summary>///executes a MySQL statement or stored procedure///</summary>///<param Name = "type" > Command type </param>///<param name= "sqlString" >SQL statement </param>///<param name= "pstmt" > Parameters </param>///<returns> Execution Results </returns>public static int ExecuteNonQuery (CommandType type, String sqlString, mysql.data.mysqlclient.mysqlparameter[] para) {try{ using (MySql.Data.MySqlClient.MySqlCommand com = new MySql.Data.MySqlClient.MySqlCommand ()) {com. Connection = Mysqlconnection;com.commandtext = @sqlString; com.commandtype = TYPE;IF (para! = null) com. Parameters.addrange (para); int val = com. ExecuteNonQuery (); Com. Parameters.clear (); return Val;}} catch (Exception ex) {//logger.error ("Execute MySQL statement or stored procedure, exception!") ", ex); return 0;} Finally{if (mysqlconnection.state! = connectionstate.closed) Mysqlconnection.close ();}} <summary>///executing an SQL statement or stored procedure with a transaction///</summary>///<param name= "trans" > Transaction </param>///< param name= "type" > Command type </param>///<param name= "sqlString" >sql statement </param>///<param name= " Pstmt "> Parameters </param>///<returns> execution result </returns>public static int ExecuteNonQuery (MySql.Data.MySqlClient.MySqlTransaction trans, CommandType type, String sqlString, Mysql.data.mysqlclient.mysqlparameter[] para) {try{using (MySql.Data.MySqlClient.MySqlCommand com = new MySql.Data.MySqlClient.MySqlCommand ()) {com. Connection = Mysqlconnection;com.commandtext = @sqlString; com.commandtype = TYPE;IF (para! = null) com. Parameters.addrange (para); if (trans! = null) com. Transaction = trans;int val = com. ExecuteNonQuery (); Com. Parameters.clear (); return Val;}} catch (Exception ex) {//logger.error ("Execute MySQL statement or stored procedure 2, Exception!") ", ex); return 0;} Finally{if (mysqlconnection.state! = connectionstate.closed) Mysqlconnection.close ();}} #endregion #region Execute SQL statement or stored procedure, return datatable///<summary>///Execute SQL statement or stored procedure, return datatable///</summary>/// <param name= "type" > Command type </param>///<param name= "sqlString" >sql statement </param>///<param name= "Pstmt" > Parameters </param>///<returns> Execution result </returns>public static DataTable executereadertodatatable (CommandType type, String sqlstrING, mysql.data.mysqlclient.mysqlparameter[] para) {datatable dt = new DataTable (); MySql.Data.MySqlClient.MySqlDataReader dr = null;try{using (MySql.Data.MySqlClient.MySqlCommand com = new MySql.Data.MySqlClient.MySqlCommand ()) {com. Connection = Mysqlconnection;com.commandtext = @sqlString; com.commandtype = TYPE;IF (para! = null) com. Parameters.addrange (para); using (dr = Com. ExecuteReader (commandbehavior.closeconnection)) {if (dr! = null) dt. Load (DR); Com. Parameters.clear (); if (mysqlconnection.state! = connectionstate.closed) Mysqlconnection.close ();} return DT;}} catch (Exception ex) {//logger.error ("Execute SQL statement or stored procedure, return DataTable, Exception!") ", ex); return null;} Finally{if (dr! = null &&!dr. IsClosed) Dr. Close (); if (mysqlconnection.state! = connectionstate.closed) Mysqlconnection.close ();}} #endregion}

Special Note:

1, MySql.Data.dll MYSQL official website provides the component, after downloading adds the reference to the current project can use

2. Parametric processing

in SQL Server, the parameterization symbol is "@", Parametric Example such as:

         sqlparameter[] param = {                new SqlParameter ("@TABLEDATA", Tabledata)         };

In MySQL, parametric processing symbols are "?", and parametric examples are:

         Mysql.data.mysqlclient.mysqlparameter[] Paras = {new MySql.Data.MySqlClient.MySqlParameter ("? Libor_name ", NAME),         };

Other reference articles are as follows:

Http://www.jb51.net/article/30342.htm

. NET using MySql.Data.dll Dynamic Library Operation MYSQL Help class--mysqlhelper