public class MySql { //@"c:\Program Files (x86)\MySQL\MySQL Connector Net 6.9.6\Assemblies\v4.0\MySql.Data.dll" const string MySqlDll = @"f:\Programme\MySQL\MySQL Connector Net 6.3.6\v2\mysql.data.dll"; /** * #01 Initialisierungsfehler bei MySqlData-Dll * #02 Instance MySqlConnection konnte nicht erzeugt werden * #03 Allgemeiner Fehler beim Öffnen der Verbindung * #04 Verbinden zur Datenbank fehlgeschlagen * #05 Püfen des Verbindungsstatus ist fehlgeschlagen * #06 Instance MySqlCommand konnte nicht erzeugt werden * #07 Query konnte nicht gesetzt werden CommandText, Connection * #08 Fehler beim Ausführen des Commands ExecuteCommand() * #09 Fehler beim Ausführen des Commands ExecuteNonQuery() * #10 Fehler beim Ausführen des Commands ExecuteCommand() Dispose() * #11 Fehler beim Schliessen der Verbindung * */ #region "DEC" private string _user; private string _password; private string _host; private string _database; private int _port; private bool _isDisposed = false; private bool _silent; Assembly _assMySqlData; Type _typeMySqlConnection; Type _typeMySqlCommand; Type _typeMySqlDataReader; #region "MySqlConnection_Methods" MethodInfo _methodMysqlConnection_State; MethodInfo _methodMysqlConnection_Open; MethodInfo _methodMysqlConnection_ConnectionString; MethodInfo _methodMysqlConnection_Close; MethodInfo _methodMysqlConnection_Dispose; #endregion #region "MySqlCommand_Methods" MethodInfo _methodMysqlCommand_ExecuteReader; MethodInfo _methodMysqlCommand_ExecuteNonQuery; MethodInfo _methodMysqlCommand_Connection; MethodInfo _methodMysqlCommand_CommandText; MethodInfo _methodMysqlCommand_Dispose; #endregion #region "MySqlDataReader_Methods" MethodInfo _methodMysqlDataReader_Read; MethodInfo _methodMysqlDataReader_HasRows; MethodInfo _methodMysqlDataReader_FieldCount; MethodInfo _methodMysqlDataReader_GetValue; MethodInfo _methodMysqlDataReader_GetName; MethodInfo _methodMysqlDataReader_Close; MethodInfo _methodMysqlDataReader_Dispose; #endregion object _oMySqlConnection; object _oMysqlCommand; bool _state = false; #endregion public MySql(string user, string password, string host, string database, int port) { _user = user; _password = password; _host = host; _database = database; _port = port; _initMethodsAndAssembly(); } public bool state() { return _state; } public bool open() { try { _oMySqlConnection = Activator.CreateInstance(_typeMySqlConnection); if (_oMySqlConnection == null) { if (!_silent) { MessageBox.Show("Init MySqlConnection object methods failed!\n--\n", "Fehler #02", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { System.Diagnostics.Trace.WriteLine("[Fehler #02]Init MySqlConnection object methods failed!"); } return false; } object[] arg = new object[] { (string)"Server=" + _host + ";Port=" + _port.ToString() + ";Database=" + _database + ";Uid=" + _user + ";Pwd=" + _password +";" }; try { //connectin durchführen _methodMysqlConnection_ConnectionString.Invoke(_oMySqlConnection, arg); _methodMysqlConnection_Open.Invoke(_oMySqlConnection, null); } catch (Exception ex) { if (!_silent) { MessageBox.Show("Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace, "Fehler #04", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { System.Diagnostics.Trace.WriteLine("[Fehler #04]Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace); } return false; } try { //verbindung testen object mysqlState = _methodMysqlConnection_State.Invoke(_oMySqlConnection, null); if (mysqlState.ToString().ToLower() == "open") { _state = true; return true; } return false; } catch (Exception ex) { if (!_silent) { MessageBox.Show("Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace, "Fehler #05", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { System.Diagnostics.Trace.WriteLine("[Fehler #05]Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace); } return false; } } catch (Exception ex) { if (!_silent) { MessageBox.Show("Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace, "Fehler #03", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { System.Diagnostics.Trace.WriteLine("[Fehler #03]Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace); } return false; } } public void close() { if (!_state) { return; } try { _methodMysqlConnection_Dispose.Invoke(_oMySqlConnection, null); _oMySqlConnection = null; } catch (Exception ex) { if (!_silent) { MessageBox.Show("Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace, "Fehler #11", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { System.Diagnostics.Trace.WriteLine("[Fehler #11]Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace); } } } public List<System.Collections.Hashtable> query(string query) { if (!_initQuery(query)) { return null; } object oMysqlDataReader; object retRows; object fieldCount; try { oMysqlDataReader = _methodMysqlCommand_ExecuteReader.Invoke(_oMysqlCommand, null); retRows = _methodMysqlDataReader_HasRows.Invoke(oMysqlDataReader, null); fieldCount = _methodMysqlDataReader_FieldCount.Invoke(oMysqlDataReader, null); } catch (Exception ex) { if (!_silent) { MessageBox.Show("Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace, "Fehler #08", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { System.Diagnostics.Trace.WriteLine("[Fehler #08]Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace); } _cleanupOpenReader(); return null; } if (!(bool)retRows) { _cleanupOpenReader(ref oMysqlDataReader); return null; } List<System.Collections.Hashtable> tab = new List<System.Collections.Hashtable>(); while (((bool)_methodMysqlDataReader_Read.Invoke(oMysqlDataReader, null))) { System.Collections.Hashtable row = new System.Collections.Hashtable(); for (int i = 0; i < ((int)fieldCount); i++) { object name; object value; name = _methodMysqlDataReader_GetName.Invoke(oMysqlDataReader, new object[] { i }); try { value = _methodMysqlDataReader_GetValue.Invoke(oMysqlDataReader, new object[] { i }); } catch (Exception) //exception wird abgefangen, wenn DBNull kommt: Wert wird auf "" Leerstring gesetzt. { value = ""; } row.Add((string)name, value); } tab.Add(row); } _cleanupOpenReader(ref oMysqlDataReader); return tab; } public void query(string query, ref int rowsAffected) { if (!_initQuery(query)) { rowsAffected = -1; return; } try { rowsAffected = (int)_methodMysqlCommand_ExecuteNonQuery.Invoke(_oMysqlCommand, null); } catch (Exception ex) { if (!_silent) { MessageBox.Show("Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace, "Fehler #09", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { System.Diagnostics.Trace.WriteLine("[Fehler #09]Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace); } rowsAffected = -1; return; } finally { _cleanupOpenReader(); } } private bool _initMethodsAndAssembly() { try { //dll laden //_assMySqlData = Assembly.LoadFrom(MySqlDll); _assMySqlData = Assembly.LoadFrom(MySqlDll); //typen lesen _typeMySqlConnection = _assMySqlData.GetType("MySql.Data.MySqlClient.MySqlConnection"); _typeMySqlCommand = _assMySqlData.GetType("MySql.Data.MySqlClient.MySqlCommand"); _typeMySqlDataReader = _assMySqlData.GetType("MySql.Data.MySqlClient.MySqlDataReader"); //methodenzugriffe erstellen _methodMysqlConnection_State = _typeMySqlConnection.GetMethod("get_State"); _methodMysqlConnection_Open = _typeMySqlConnection.GetMethod("Open"); _methodMysqlConnection_ConnectionString = _typeMySqlConnection.GetMethod("set_ConnectionString"); _methodMysqlConnection_Close = _typeMySqlConnection.GetMethod("Close"); _methodMysqlConnection_Dispose = _typeMySqlConnection.GetMethod("Dispose"); _methodMysqlCommand_ExecuteReader = _typeMySqlCommand.GetMethod("ExecuteReader", new Type[] { }); _methodMysqlCommand_ExecuteNonQuery = _typeMySqlCommand.GetMethod("ExecuteNonQuery"); _methodMysqlCommand_Connection = _typeMySqlCommand.GetMethod("set_Connection", new Type[] { _typeMySqlConnection }); _methodMysqlCommand_CommandText = _typeMySqlCommand.GetMethod("set_CommandText"); _methodMysqlCommand_Dispose = _typeMySqlCommand.GetMethod("Dispose"); _methodMysqlDataReader_Read = _typeMySqlDataReader.GetMethod("Read"); _methodMysqlDataReader_HasRows = _typeMySqlDataReader.GetMethod("get_HasRows"); _methodMysqlDataReader_FieldCount = _typeMySqlDataReader.GetMethod("get_FieldCount"); _methodMysqlDataReader_GetValue = _typeMySqlDataReader.GetMethod("GetValue"); _methodMysqlDataReader_GetName = _typeMySqlDataReader.GetMethod("GetName"); _methodMysqlDataReader_Close = _typeMySqlDataReader.GetMethod("Close"); _methodMysqlDataReader_Dispose = _typeMySqlDataReader.GetMethod("Dispose", new Type[] { }); } catch (Exception ex) { if (!_silent) { MessageBox.Show("Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace, "Fehler #01", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { System.Diagnostics.Trace.WriteLine("[Fehler #01]Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace); } return false; } return true; } private bool _initQuery(string query) { if (!_state) { return false; } try { _oMysqlCommand = Activator.CreateInstance(_typeMySqlCommand); } catch (Exception ex) { if (!_silent) { MessageBox.Show("Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace, "Fehler #06", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { System.Diagnostics.Trace.WriteLine("[Fehler #06]Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace); } return false; } if (_oMysqlCommand == null) { return false; } object[] paramCommandConnection = new object[] { _oMySqlConnection }; object[] paramCommandText = new object[] { query }; try { _methodMysqlCommand_CommandText.Invoke(_oMysqlCommand, paramCommandText); _methodMysqlCommand_Connection.Invoke(_oMysqlCommand, paramCommandConnection); } catch (Exception ex) { if (!_silent) { MessageBox.Show("Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace, "Fehler #07", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { System.Diagnostics.Trace.WriteLine("[Fehler #07]Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace); } return false; } return true; } private void _cleanupOpenReader( ref object oMysqlDataReader) { try { _methodMysqlCommand_Dispose.Invoke(_oMysqlCommand, null); _methodMysqlDataReader_Dispose.Invoke(oMysqlDataReader, null); _oMysqlCommand = null; oMysqlDataReader = null; } catch (Exception ex) { if (!_silent) { MessageBox.Show("Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace, "Fehler #10", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { System.Diagnostics.Trace.WriteLine("[Fehler #10]Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace); } return; } } private void _cleanupOpenReader() { try { _methodMysqlCommand_Dispose.Invoke(_oMysqlCommand, null); _oMysqlCommand = null; } catch (Exception ex) { if (!_silent) { MessageBox.Show("Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace, "Fehler #10", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { System.Diagnostics.Trace.WriteLine("[Fehler #10]Init MySql methods failed!\n--\n" + Helpers.ExTrace(ex) + "\n\n--\nTrace:\n" + ex.StackTrace); } return; } } public void Dispose() { if (!_isDisposed) { close(); _user = null; _password = null; _host = null; _database = null; _port = -1; _assMySqlData = null; _typeMySqlConnection = null; _typeMySqlCommand = null; _typeMySqlDataReader = null; _methodMysqlConnection_State = null; _methodMysqlConnection_Open = null; _methodMysqlConnection_ConnectionString = null; _methodMysqlConnection_Close = null; _methodMysqlConnection_Dispose = null; _methodMysqlCommand_ExecuteReader = null; _methodMysqlCommand_ExecuteNonQuery = null; _methodMysqlCommand_Connection = null; _methodMysqlCommand_CommandText = null; _methodMysqlCommand_Dispose = null; _methodMysqlDataReader_Read = null; _methodMysqlDataReader_HasRows = null; _methodMysqlDataReader_FieldCount = null; _methodMysqlDataReader_GetValue = null; _methodMysqlDataReader_GetName = null; _methodMysqlDataReader_Close = null; _methodMysqlDataReader_Dispose = null; _oMySqlConnection = null; _oMysqlCommand = null; _state = false; _isDisposed = true; } } public void setSilent(bool silent) { _silent = silent; } } public static class Helpers { public static string ExTrace(Exception ex) { string ret = ex.Message; if (ex.InnerException != null) { ret += "\n\n--\n\n" + ExTrace(ex.InnerException); } return ret; } }
MySql m = new MySql( "ich", "geheim", "rechnername", "meine_datenbank", 3306 ); if( !m.open() ){ return; } //oder: später testen - ob eine Verbindung offen ist if( m.state() ){ Trace.WriteLine ( "Verbindung ist offen" ); } List<System.Collections.Hashtable> res = m.query("SELECT * FROM irgendwas"); if( res != null ){ foreach( List<System.Collections.Hashtable> item in res ){ Trace.WriteLine( (string)item["spalten_name"] ); } } int rE = 0; m.query( "INSERT INTO irgendwo (spaltenname) VALUES ('test')", ref rE ); if( rE = -1 ){ Trace.WriteLine( "Fehler bei Query" ); } if( rE > -1 ){ Trace.WriteLine( "Tupel wurde angelegt" ); Trace.WriteLine( "Betroffene Zeilen: " + rE.toString() ); } //oder eben auch rE = 0; m.query( "DELETE FROM tabelle" ); if( rE = -1 ){ Trace.WriteLine( "Fehler bei Query" ); } if( rE > -1 ){ Trace.WriteLine( "Betroffene Zeilen: " + rE.toString() ); } m.Dispose();