====== 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 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 tab = new List();
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 res = m.query("SELECT * FROM irgendwas");
if( res != null ){
foreach( List 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();