Ich denke ich fange mal so an.
Jeder sollte ODBC kennen. Aber jeder sollte eigentlich wissen das bei CrossPlatform dies ein großes hindernis darstellt. Viel besser als ODBC ist dabei die Mysql.Data. Diese eignet sich Perfekt für CrossPlatform und dazu ist sie wesentlich schneller als ODBC!!!
In meinen Arbeiten mit Server Cores hatte ich oft mit ODBC das vergnügen gehabt. Da es die Leute nutzten die die Projekte leiteten. Nun fing ich bei meinen Projekten direkt an die Mysql.Data dll zu nutzen. Da diese wesentlich bessere Performance erbringt.
Das Problem bei Mysql.Data ist für Server ein gravierendes. Man erstellt normalerweise eine Mysql Klasse auf die Zugegriffen wird. Nun erlaubt Mysql.Data nur einen Datareader der gleichzeitig geöffnet ist.
Um dieses Problem zu beheben habe ich meine eigene Klasse geschrieben die so perfekt funktioniert.
C# Klassen
Database.cs (Database Engine)
Database
C#
using System;
using MySql.Data.MySqlClient;
using System.Data.Common;
namespace YourNameSpace
{
public class Database
{
#region Variablen
private static MySqlConnection Connection;
public static string cstring = "";
#endregion
#region Einstiegspunkt
/// <summary>
/// Stellt eine verbindung zum MySql server her.
/// </summary>
/// <param name="server">Serveraddresse</param>
/// <param name="database">Database</param>
/// <param name="user">Username</param>
/// <param name="password">Password</param>
public static void Connect(string user, string password, string database, string server)
{
cstring = "SERVER=" + server + ";" +
"DATABASE=" + database + ";" +
"UID=" + user + ";" +
"PASSWORD=" + password + ";";
Connection = new MySqlConnection(cstring);
TestConnection();
CloseConnection(Connection);
}
#endregion
/// <summary>
/// Open Connection
/// </summary>
/// <returns>On success returns true.</returns>
private static bool TestConnection()
{
try
{
if (Connection.State == System.Data.ConnectionState.Closed)
{
Connection.Open();
Console.WriteLine("Connection to MySqlServer established.");
return true;
}
else
{
Console.WriteLine("MySqlServer already connected!");
return false;
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.Message);
return false;
}
}
public static MySqlConnection CreateConnection()
{
return new MySqlConnection(cstring);
}
public static bool OpenConnection(MySqlConnection connect)
{
try
{
if (connect.State == System.Data.ConnectionState.Closed)
{
connect.Open();
//Log.Write(Log.MessageType.info, "Connection to MySqlServer established.");
return true;
}
else
{
//Log.Write(Log.MessageType.error, "MySqlServer already connected!");
return false;
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.Message);
return false;
}
}
/// <summary>
/// Close Connection
/// </summary>
/// <returns>On success returns true.</returns>
public static bool CloseConnection(MySqlConnection connect)
{
try
{
connect.Close();
return true;
}
catch (MySqlException ex)
{
Console.WriteLine(ex.Message);
return false;
}
}
/// <summary>
/// Executes a query with no results. Mostly used with INSERT INTO, UPDATE and DELETE queries.
/// </summary>
/// <param name="Query">The query string to execute.</param>
public static void Execute(string Query, params object[] Arguments)
{
try
{
//open connection
OpenConnection(Connection);
//create command and assign the query and connection from the constructor
MySqlCommand exec = new MySqlCommand(string.Format(Query, Arguments), Connection);
//Execute command
exec.ExecuteNonQuery();
//dispose object
exec.Dispose();
exec = null;
//close connection
CloseConnection(Connection);
}
catch (Exception e)
{
Console.Write("Could not execute nonquery!\r\nQuery: {0}\r\nError:{1}", Query, e.Message);
}
}
#region Engine
public static object QReadObject(MySqlDataReader QResource, string cname)
{
try
{
return QResource.GetValue(QResource.GetOrdinal(cname));
}
catch (Exception e)
{
Console.WriteLine("Unable to get field {0}: {1}", cname, e.Message);
return 0;
}
}
public static MySqlDataReader QResource(string Query, MySqlConnection connect, params object[] Arguments)
{
try
{
return new MySqlCommand(string.Format(Query, Arguments), connect).ExecuteReader();
}
catch (Exception e)
{
Console.Write("Could not execute query!\r\nQuery: {0}\r\nError:{1}", Query, e.Message);
return null;
}
}
public static DateTime QReadDT(MySqlDataReader QResource, string cname)
{
try
{
return QResource.GetDateTime(QResource.GetOrdinal(cname));
}
catch (Exception e)
{
try
{
return (DateTime)QResource.GetValue(QResource.GetOrdinal(cname));
}
catch (Exception ee)
{
Console.Write("Field {0} is not DT-based field. Database.QReadDT failed.\r\nError 1: {1}\r\nError 2: {2}", cname, e.Message, ee.Message);
return Convert.ToDateTime(0);
}
}
}
public static long QReadlong(MySqlDataReader QResource, string ColumnName)
{
try
{
return QResource.GetInt64(QResource.GetOrdinal(ColumnName));
}
catch (Exception e)
{
try
{
return Convert.ToInt64(QResource.GetValue(QResource.GetOrdinal(ColumnName)));
}
catch (Exception ee)
{
Console.Write("Field {0} is not long-based field. Database.QReadlong failed.\r\nError 1: {1}\r\nError 2: {2}", ColumnName, e.Message, ee.Message);
return 0;
}
}
}
public static int QReadint(MySqlDataReader QResource, string ColumnName)
{
try
{
return QResource.GetInt32(QResource.GetOrdinal(ColumnName));
}
catch (Exception e)
{
try
{
return int.Parse(QResource.GetString(QResource.GetOrdinal(ColumnName)));
}
catch (Exception ee)
{
Console.Write("Field {0} is not integer-based field. Database.QReadint failed.\r\nError 1: {1}\r\nError 2: {2}", ColumnName, e.Message, ee.Message);
return 0;
}
}
}
public static uint QReadint(MySqlDataReader QResource, string ColumnName, bool unsigned)
{
try
{
return Convert.ToUInt32(QResource.GetValue(QResource.GetOrdinal(ColumnName)));
}
catch (Exception e)
{
Console.Write("Field {0} is not uinteger-based field. Database.QReadint failed.\r\n{1}", ColumnName, e.Message);
return 0;
}
}
public static string QReadstring(MySqlDataReader QResource, string ColumnName)
{
try
{
return QResource.GetString(QResource.GetOrdinal(ColumnName));
}
catch (Exception e)
{
Console.Write("Field {0} is not string-based field. Database.QReadstring failed.\r\n{1}", ColumnName, e.Message);
return null;
}
}
public static float QReadfloat(MySqlDataReader QResource, string ColumnName)
{
try
{
return QResource.GetFloat(QResource.GetOrdinal(ColumnName));
}
catch (Exception e)
{
try
{
return Convert.ToSingle(QResource.GetValue(QResource.GetOrdinal(ColumnName)));
}
catch (Exception ee)
{
Console.Write("Field {0} is not float-based field. Database.QReadfloat failed.\r\nError 1: {1}\r\nError 2: {2}", ColumnName, e.Message, ee.Message);
return 0;
}
}
}
#endregion
public static string Escape(string Source)
{
return Source.Replace("'", "\\'");
}
}
public class Execute
{
private static MySqlConnection con;
public Execute(string Query, params object[] Arguments)
{
try
{
con = new MySqlConnection(Database.cstring);
Database.OpenConnection(con);
//open connection
//create command and assign the query and connection from the constructor
MySqlCommand exec = new MySqlCommand(string.Format(Query, Arguments), con);
//Execute command
exec.ExecuteNonQuery();
//dispose object
exec.Dispose();
exec = null;
//close connection
Database.CloseConnection(con);
}
catch (Exception e)
{
Console.Write("Could not execute nonquery!\r\nQuery: {0}\r\nError:{1}", Query, e.Message);
}
}
}
}ResultSet.cs
ResultSet
C#
using System;
using MySql.Data.MySqlClient;
namespace YourNameSpace
{
class ResultSet
{
private static MySqlConnection con;
/// <summary>
/// Gets an object from the resultset.
/// </summary>
/// <param name="Column">The column name.</param>
/// <returns></returns>
public object this[string Column]
{
get
{
return Database.QReadObject(QResource, Column);
}
}
private MySqlDataReader QResource;
private bool Active = true;
public ResultSet(string Query, params object[] Arguments)
{
con = new MySqlConnection(Database.cstring);
Database.OpenConnection(con);
QResource = Database.QResource(Query, con, Arguments);
if (QResource == null)
Active = false;
}
public bool Readbool(string col)
{
if (!Active)
return false;
return Readint(col) != 0;
}
public int Readint(string col)
{
if (!Active)
return 0;
return Database.QReadint(QResource, col);
}
public uint Readint(string col, bool unsigned)
{
if (!Active)
return 0;
return Database.QReadint(QResource, col, unsigned);
}
public string Readstring(string col)
{
if (!Active)
return "";
return Database.QReadstring(QResource, col);
}
public DateTime Readtime(string col)
{
if (!Active)
return Convert.ToDateTime(0);
return Database.QReadDT(QResource, col);
}
public long Readlong(string col)
{
if (!Active)
return 0;
return Database.QReadlong(QResource, col);
}
public ulong Readulong(string col)
{
if (!Active)
return 0;
return (ulong)Database.QReadlong(QResource, col);
}
public float Readfloat(string col)
{
if (!Active)
return 0;
return Database.QReadfloat(QResource, col);
}
public bool Advance()
{
if (!Active)
{
Database.CloseConnection(con);
return false;
}
return QResource.Read();
}
public void Free()
{
if (Active)
{
QResource.Close();
Database.CloseConnection(con);
QResource = null;
}
Active = false;
}
}
}AnwendungsBeispiel für Abruf aus der Datenbank per RS:
Code
ResultSet set;
set = new ResultSet("SELECT * FROM accounts WHERE accountname='{0}'", Database.Escape(strUsername));
if (!set.Advance())
{
set.Free();
SendLoginFailure(ERROR_INVALID_USERNAME);
Destruct("Login failed (Wrong ID)");
return;
}
set.Free();Anwendungsbeispiel zum Auführen einer Query:
Code
//Executing non Query
Execute ex = new Execute("UPDATE accounts SET lastlogin=NOW(),lastip='{0}'linestatus='0' WHERE userid='{1}'", c_socket.RemoteEndPoint.ToString(), dwMyUserID);Aufruf und Configurierung
Das erste mal Aufrufen müsst ihr die Klasse ganz am Anfang. Danach benutzt ihr einfach ResultSet und Execute. Wichtig ist das ihr beim Programmstart vor allen kommenden ResultSets und Executes das bereits aufgerufen habt:
Code
Database.Connect(
"username",
"password",
"database",
"hostadress");
Dependencies
http://dev.mysql.com/downloads/connector/net/
Viel Spaß damit
Hoffe wird einigen irgendwann helfen