Benutzer-Werkzeuge

Webseiten-Werkzeuge


mysql:net:reflection:mysql_class

Inhaltsverzeichnis

MySQL Reflection

        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;
        }
    }

Benutzung

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();
mysql/net/reflection/mysql_class.txt · Zuletzt geändert: 2015/08/03 13:46 von raiser