MySql wrapper using parameters

Page 1 of 2

05/09/2013 12:54 U2_Caparzo#1

This a MySql wrapper i coded to make easier to use this type of database
it's working using parameters, so there is no chance of MySql injection (or at least decreased it), but it could be a bit slower.
i'm sorry if this is a totally wrong way of do it, it could be because my inexperience at C# and MySql syntax(this is the first time that i really try learn about MySql).
Also the update methods(Update, Insert and Delete) are being executed by a thread to get the less possible delay when doing one of these types of consults(a thread and a queue, it can have more than 1 thread working, but can cause some bugs when inserting and updating the same row with no pause.

again, srry if this is a bad way of do it, i pm'ed cptsky if he could do a fast check to the code to evade any possible flaming, but looks like he is almost all the time disconnected, so hope i can get constructive criticism :)

Old

Spoiler

DBCore.cs //Connection info, create MySql connection method

Spoiler

Code:

/*****************************************************************************
 * Author: Caparzo/U2_Caparzo.
 * Code written for the project MyConquer_Core.
 * 
 * Date: 09/05/2013
 * Hour: 2:08
 * 
 * Feel free to use or redistribute this class with any project, but please
 * keep this header so credits still goes to the coder of the class.
 *****************************************************************************/
using System.Text;
using MySql.Data.MySqlClient;

namespace MyConquer_Core.MySQL
{
	/// <summary>
	/// Description of DBCore.
	/// </summary>
	public class DBCore
	{
		#region private/protected members
		
        private static string connectionString = string.Empty;
		
		#endregion
		
		#region internal/public members
		
	    /// <summary>
        /// MySQL username, default = root
        /// </summary>
        public static string DB_Username = "root";
        /// <summary>
        /// MySQL Password
        /// </summary>
        public static string DB_Password;
        /// <summary>
        /// Database name
        /// </summary>
        public static string DB_Name;
        /// <summary>
        /// Connection protocol, default = Tcp socket
        /// </summary>
        public static MySQLProtocol Protocol = MySQLProtocol.Tcp;
        /// <summary>
        /// Used only if th Protocol is Tcp, this sets the name for the pipe, defaul = the database name
        /// </summary>
        public static string PipeName = string.Empty;
        /// <summary>
        /// Define if the connection should use the MySQL Pooling, default = true
        /// </summary>
        public static bool UsePooling = true;
        /// <summary>
        /// Used only if UsePooling is true, define the maximum size of the MySQL pool, dafault = 50
        /// </summary>
        public static int MaxPoolSize = 50;
		
		#endregion
		
		#region methods
		
		/// <summary>
        /// Gets a new opened MySqlConnection
        /// </summary>
        /// <returns></returns>
        public static MySqlConnection GetNewConnection()
        {
            if (connectionString == string.Empty)
                CreateConnectionString();
            MySqlConnection conn = new MySqlConnection(connectionString);
            conn.Open();
            return conn;
        }

        private static void CreateConnectionString()
        {
            StringBuilder connString = new StringBuilder();
            connString.Append("Server=127.0.0.1;");

            connString.Append("Database=" + DB_Name + ';');

            connString.Append("Uid=" + DB_Username + ';');

            connString.Append("Pwd=" + DB_Password + ';');

            if (Protocol == MySQLProtocol.Pipe)
            {
                if (PipeName == string.Empty)
                    PipeName = DB_Name;
                connString.Append("Protocol=pipe;PipeName=" + DB_Name + ';');
            }

            if (UsePooling)
            {
                connString.Append("Pooling=True;maximumpoolsize=" + MaxPoolSize.ToString() + ';');
            }

            connString.Append("ConnectionLifeTime=60;");

            connectionString = connString.ToString();
        }
		
		#endregion
	}
}

DBEnums.cs //enums for type of consult and connection protocol(tcp or named pipe)

Spoiler

DBThread.cs //contains the thread that will create the MySqlCommands, add the text created in the custom MySQLCommands, add the parameters, and execute them.

Spoiler

Code:

/*****************************************************************************
* Author: Caparzo/U2_Caparzo.
* Code written for the project MyConquer_Core.
* 
* Date: 09/05/2013
* Hour: 2:27
* 
* Feel free to use or redistribute this class with any project, but please
* keep this header so credits still goes to the coder of the class.
*****************************************************************************/
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;

namespace MyConquer_Core.MySQL
{
	/// <summary>
	/// Thread that will execute all the update/insert MySql commands
	/// </summary>
	public class DBThread
	{
		#region private/protected members
		
		private static int _AmountOfWorkers = 1;
		/// <summary>
		/// Sets the amount of threads working, set this before start the database
		/// </summary>
		
		private static ThreadFactory workers;

		private static ConcurrentQueue<MySQLCommand> commands = new ConcurrentQueue<MySQLCommand>();
		
		#endregion
		
		#region properties
		
		public static int AmountOfWorkers
		{
			get{return _AmountOfWorkers;}
			set{_AmountOfWorkers = value;}
		}
		
		#endregion
		
		#region methods
		
		private static void ExecuteCommands()
		{
			
			MySQLCommand cmd;
			if(commands.TryDequeue(out cmd))
			{
				using(var conn = DBCore.GetNewConnection())
				{
					using(MySqlCommand command = new MySqlCommand())
					{
						command.Connection = conn;
						switch(cmd.type)
						{
							#region insert
							case CommandType.INSERT:
								{
									StringBuilder tempFields = new StringBuilder();
									StringBuilder tempValues = new StringBuilder();
									bool started = false;
									foreach(KeyValuePair<string, object> pair in cmd.InsertValues)
									{
										if(!started)
										{
											tempFields.Append(pair.Key);
											tempValues.Append('?' + pair.Key);
											started = true;
										}
										else
										{
											tempFields.Append("," + pair.Key);
											tempValues.Append(",?" + pair.Key);
										}
										command.Parameters.AddWithValue('?' + pair.Key, pair.Value);
									
									}
									cmd.command.Replace("<F>", tempFields.ToString());
									cmd.command.Replace("<V>", tempValues.ToString());
									command.CommandText = cmd.GetCommand();
									command.ExecuteNonQuery();
									break;
								}
								#endregion
							#region update
							case CommandType.UPDATE:
								{
									bool started = false;
									foreach(KeyValuePair<string, object> pair in cmd.InsertValues)
									{
										if(!started)
										{
											cmd.command.Append(" Set " + pair.Key+"=?" + pair.Key + ' ');
											started = true;
										}
										else
										{
											cmd.command.Append(',' + pair.Key+"=?" + pair.Key + ' ');
										}
										command.Parameters.AddWithValue('?' + pair.Key, pair.Value);
									}
									if(cmd.WhereStatment != null)
									{
										cmd.command.Append("where " + cmd.WhereStatment.Item1 + "=?1" + cmd.WhereStatment.Item1);
										command.Parameters.AddWithValue("?1" + cmd.WhereStatment.Item1, cmd.WhereStatment.Item2);
									}
									command.CommandText = cmd.GetCommand();
									command.ExecuteNonQuery();
									break;
								}
								#endregion
							#region delete
							case CommandType.DELETE:
								{
							// = cmd.GetCommand();
								cmd.command.Replace("<F>", cmd.WhereStatment.Item1);
								cmd.command.Replace("<V>", '?' + cmd.WhereStatment.Item1);
								command.Parameters.AddWithValue('?'+ cmd.WhereStatment.Item1, cmd.WhereStatment.Item2);
								command.CommandText = cmd.GetCommand();
								command.ExecuteNonQuery();
								break;
								}
							#endregion
						}
					}
				}
			}
		}
		
		public static void Start()
		{
			workers = new ThreadFactory(2, AmountOfWorkers);
			workers.invokers += ExecuteCommands;
			workers.Start();
		}
		
		public static void Enqueue(MySQLCommand cmd)
		{
			commands.Enqueue(cmd);
		}
		
		#endregion
	}
}

MySQLCommand//Nothing special, some replace methods, a dictionary for the parameters and a Tuple for the where statment

Spoiler

Code:

 /*****************************************************************************
 * Author: Caparzo/U2_Caparzo.
 * Code written for the project MyConquer_Core.
 * 
 * Date: 09/05/2013
 * Hour: 2:34
 * 
 * Feel free to use or redistribute this class with any project, but please
 * keep this header so credits still goes to the coder of the class.
 *****************************************************************************/
using System;
using System.Text;

namespace MyConquer_Core.MySQL
{
	/// <summary>
	/// Wrapper for the MySqlCommand.
	/// </summary>
	public class MySQLCommand
	{
		#region internal/public members
		
		internal StringBuilder command;
		internal Dictionary<string, object> InsertValues;
		internal Tuple<string, object> WhereStatment;
		public CommandType type;
		
		#endregion
		
		#region constructors
		
		public MySQLCommand(CommandType Type)
		{
			this.type = Type;
			WhereStatment = null;
			InsertValues = new Dictionary<string, object>();
			command = new StringBuilder();
			switch(type)
			{
				case CommandType.INSERT:
					{
						command.Append("INSERT INTO <T> (<F>) VALUES (<V>)");
						break;
					}
				case CommandType.UPDATE:
					{
						command.Append("UPDATE <T>");
						break;
					}
				case CommandType.DELETE:
					{
						command.Append("DELETE FROM <T> where <F> = <V>");
						break;
					}
			}
		}
		
		#endregion
		
		#region methods
		
		public MySQLCommand Insert(string Table)
		{
			command.Replace("<T>", Table);
			return this;
		}
		public MySQLCommand Insert(string field, object value)
		{
			InsertValues.Add(field, value);
			return this;
		}
		public MySQLCommand Update(string Table)
		{
			command.Replace("<T>", Table);
			return this;
		}
		public MySQLCommand Set(string field, object value)
		{
			InsertValues.Add(field, value);
			return this;
		}
		public void Execute()
		{
			DBThread.Enqueue(this);
		}
		public MySQLCommand Where(string field, object value)
		{
			WhereStatment = new Tuple<string, object>(field, value);
			return this;
		}
		public MySQLCommand Limit(int limit)
		{
			command.Append("LIMIT "+ limit.ToString() + ';');
			return this;
			               
		}
		public MySQLCommand Delete(string Table)
		{
			command.Replace("<T>", Table);
			return this;
		}
		public MySQLCommand Delete(string field, object value)
		{
			WhereStatment = new Tuple<string, object>(field, value);
			return this;
		}
		public string GetCommand()
		{
			return command.ToString();
		}
		
		#endregion
	}
}

MySQLReader.cs //obvious(?)

Spoiler

Code:

/*****************************************************************************
 * Author: Caparzo/U2_Caparzo.
 * Code written for the project MyConquer_Core.
 * 
 * Date: 09/05/2013
 * Hour: 2:38
 * 
 * Feel free to use or redistribute this class with any project, but please
 * keep this header so credits still goes to the coder of the class.
 *****************************************************************************/
using System;
using System.Text;
using MySql.Data.MySqlClient;

namespace MyConquer_Core.MySQL
{
	/// <summary>
	/// Wrapper for the MySqlDataReader class.
	/// </summary>
	public class MySQLReader
	{
	
		#region private/protected members
		
		private StringBuilder command;
		private MySqlDataReader reader;
		private MySqlCommand cmd;
		
		#endregion
		
		#region constructors
		
		public MySQLReader()
		{
			command = new StringBuilder("SELECT * FROM <T>");
			cmd = DBCore.GetNewConnection().CreateCommand();
		}
		
		#endregion
		
		#region methods
		
		/// <summary>
		/// Select the table that contains the data that will be read
		/// </summary>
		/// <param name="Table">Table name</param>
		/// <returns></returns>
		public MySQLReader Select(string Table)
		{
			command.Replace("<T>", Table);
			return this;
		}
		/// <summary>
		/// Select the column that contains the needed data
		/// </summary>
		/// <param name="Column">Column name</param>
		/// <returns></returns>
		public MySQLReader SelectColumn(string Column)
		{
			command.Replace("*", Column);
			return this;
		}
		
		public MySQLReader Where(string Column, object value)
		{
			command.Append(" where " + Column + "=?" + Column);
			cmd.Parameters.AddWithValue('?' + Column, value);
			return this;
		}
		
		public void Execute()
		{
			cmd.CommandText = command.ToString();
			reader = cmd.ExecuteReader();
		}
		
		public MySQLReader ReadSByte(string Column, out sbyte value)
		{
			value = default(sbyte);
			if(reader.Read())
			value = reader.GetSByte(Column);
			return this;
		}
		
		public MySQLReader ReadByte(string Column, out byte value)
		{
			value = default(byte);
			if(reader.Read())
			value = reader.GetByte(Column);
			return this;
		}
		
		public MySQLReader ReadInt16(string Column, out Int16 value)
		{
			value = default(short);
			if(reader.Read())
			value = reader.GetInt16(Column);
			return this;
		}
		
		public MySQLReader ReadUInt16(string Column, out UInt16 value)
		{
			value = default(ushort);
			if(reader.Read())
			value = reader.GetUInt16(Column);
			return this;
		}
		
		public MySQLReader ReadInt32(string Column, out Int32 value)
		{
			value = default(int);
			if(reader.Read())
			value = reader.GetInt32(Column);
			return this;
		}
		
		public MySQLReader ReadUInt32(string Column, out UInt32 value)
		{
			value = default(uint);
			if(reader.Read())
			value = reader.GetUInt32(Column);
			return this;
		}
		
		public MySQLReader ReadInt64(string Column, out Int64 value)
		{
			value = default(long);
			if(reader.Read())
			value = reader.GetInt64(Column);
			return this;
		}
		
		public MySQLReader ReadUInt64(string Column, out UInt64 value)
		{
			value = default(ulong);
			if(reader.Read())
			value = reader.GetUInt64(Column);
			return this;
		}
		
		public MySQLReader ReadString(string Column, out string value)
		{
			value = default(string);
			if(reader.Read())
			value = reader.GetString(Column);
			return this;
		}
		
		public MySQLReader ReadBool(string Column, out bool value)
		{
			value = default(bool);
			if(reader.Read())
			value = reader.GetBoolean(Column);
			return this;
		}
		
		public MySQLReader ReadDateTime(string Column, out DateTime value)
		{
			value = default(DateTime);
			if(reader.Read())
			value = reader.GetDateTime(Column);
			return this;	
		}
		/// <summary>
		/// Free the resources used by the MySQLReader
		/// </summary>
		public void Dispose()
		{
			cmd.Dispose();
			cmd.Connection.Dispose();
			reader.Dispose();
		}
		
		#endregion
	}
}

ThreadFactory.cs//Wrapper for the Thread class

Spoiler

First revision

Spoiler

Removed DBThread, renamed some variables, renamed classes, edited the code to prevent errors(maybe this was not done in a good way, u could have used pre-processors but since this was intendeed to be a dll, i didn't )
Added support to execute asynchronously using BeginExecute() instead using the DBThread in the old version, of course it can be executed sinchronously with the Execute method.
Added support for multiple where statments.
The MySqlReader still the same, i guess i will edit it in some days since i got a lot to study, and i have 0 knowledge about DataAdapater, DataSet an DataRow :/

MySqlCore//Old DBCore, now using MySqlConnectionStringBuilder and some checks to the string values

Spoiler

Code:

/*****************************************************************************
 * Author: Caparzo/U2_Caparzo.
 * Code written for the project MySqlWrapper.
 * 
 * Date: 10/05/2013
 * Hour: 1:20
 * 
 * Feel free to use or redistribute this class with any project, but please
 * keep this header so credits still goes to the coder of the class.
 *****************************************************************************/
using System;
using MySql.Data.MySqlClient;

namespace MySqlWrapper
{
	/// <summary>
	/// Core for the MySqlWrapper, contains the configuration and 
    /// methods to get a new MySql connection.
	/// </summary>
	public class MySqlCore
	{
        private static string connectionString;
        public static string UserID = "root";
        public static string Password;
        public static string Database;
        public static MySqlProtocol Protocol = MySqlProtocol.Tcp;
        public static string PipeName = "MyDBPipe";
        public static bool UsePooling = false;
        public static uint MaxPoolSize = 50;
        public static uint MinPoolSize = 0;
        public static bool ThrowExceptions = false;

        public static MySqlConnection GetNewConnection()
        {
            if (string.IsNullOrEmpty(connectionString))
                CreateConnectionString();
            MySqlConnection conn = new MySqlConnection(connectionString);
            conn.Open();
            return conn;
        }

        public static void CreateConnectionString()
        {
            #region sanity checks

            if (string.IsNullOrEmpty(Password))
                throw new NullReferenceException("The password to connect to MySql can not be null or empty");
            else if (string.IsNullOrEmpty(Database))
                throw new NullReferenceException("The Database name can not be null or empty");
            else if (Protocol != MySqlProtocol.Tcp && Protocol != MySqlProtocol.NamedPipe)
                throw new NullReferenceException("The connection Protocol must be Tcp or NamedPipe");
            else if (Protocol == MySqlProtocol.NamedPipe && string.IsNullOrEmpty(PipeName))
                throw new NullReferenceException("the name for the NamedPipe can not be null or empty");
           
            #endregion

            MySqlConnectionStringBuilder connStringBuilder = new MySqlConnectionStringBuilder();
            connStringBuilder.UserID = !string.IsNullOrEmpty(UserID) ? UserID : "root";
            connStringBuilder.Password = Password;
            connStringBuilder.Database = Database;
            if (Protocol == MySqlProtocol.NamedPipe)
                connStringBuilder.PipeName = PipeName;
            if (UsePooling)
            {
                connStringBuilder.Pooling = true;
                connStringBuilder.MaximumPoolSize = MaxPoolSize;
                connStringBuilder.MinimumPoolSize = MinPoolSize;
            }
            connectionString = connStringBuilder.ToString();
        }
	}
}

MySqlWriter//Old MySqlCommand, now it wraps the MySqlCommand, and as stated before, support multiple where statmens for the Update methods, plus some checks for possible wrong commands

Spoiler

Code:

/*****************************************************************************
* Author: Caparzo/U2_Caparzo.
* Code written for the project MySqlWrapper.
* 
* Date: 10/05/2013
* Hour: 1:19
* 
* Feel free to use or redistribute this class with any project, but please
* keep this header so credits still goes to the coder of the class.
*****************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;

namespace MySqlWrapper
{
    struct MySqlWhere
    {
        public string Field;
        public object Value;
        public MySqlWhereConnector Connector;
        public MySqlWhereType Type;
    }

	/// <summary>
	/// Wrapper for the MySqlCommand from the .net connector to MySql.
	/// </summary>
    public class MySqlWriter
    {
        private Dictionary<string, object> insertValues;
        private Dictionary<string, MySqlWhere> whereStatments;
        private StringBuilder command;
        private MySqlCommandType commandType;
        private MySqlWhereConnector nextConnector;//To implement 'And' and 'Or'

        public MySqlWriter(MySqlCommandType type)
        {
            this.commandType = type;
            this.whereStatments = new Dictionary<string,MySqlWhere>();
            this.insertValues = new Dictionary<string, object>();
            this.command = new StringBuilder();
            switch (type)
            {
                case MySqlCommandType.Insert:
                    {
                        this.command.Append("Insert Into <T> (<F>) Values (<V>)");
                        break;
                    }
                case MySqlCommandType.Update:
                    {
                        this.command.Append("Update <T>");
                        break;
                    }
                case MySqlCommandType.Delete:
                    {
                        this.command.Append("Delete From <T> Where <F> = <V>");
                        break;
                    }
                default:
                    {
                        throw new Exception("The type of MySqlCommand must be Insert, Update or Delete!");
                    }
            }
        }

        public bool SelectTable(string table)
        {
            if (this.command.ToString().Contains("<T>"))
            {
                this.command.Replace("<T>", table);
                return true;
            }
            else
            {
                if (MySqlCore.ThrowExceptions)
                    throw AlreadyChosenTableException();
                else
                    return false;
            }
        }
        
        public bool Insert(string field, object value)
        {
            if (this.commandType != MySqlCommandType.Insert)
            {
                if (MySqlCore.ThrowExceptions)
                    throw WrongTypeException("Insert", "Insert");
                else
                    return false;
            }
            else
            {
                if (this.insertValues.ContainsKey(field))
                {
                    if (MySqlCore.ThrowExceptions)
                        throw AlreadyUsedFieldException(field);
                    else
                        return false;
                }
                else
                {
                    this.insertValues.Add(field, value);
                    return true;
                }
            }
        }

        public bool Update(string field, object value)
        {
            if (this.commandType != MySqlCommandType.Update)
            {
                if (MySqlCore.ThrowExceptions)
                    throw WrongTypeException("Update", "Update");
                else
                    return false;
            }
            else
            {
                if (this.insertValues.ContainsKey(field))
                {
                    if (MySqlCore.ThrowExceptions)
                        throw AlreadyUsedFieldException(field);
                    else
                        return false;
                }
                else
                {
                    this.insertValues.Add(field, value);
                    return true;
                }
            }
        }

        public bool Delete(string field, object value)
        {
            if (this.commandType != MySqlCommandType.Delete)
            {
                if (MySqlCore.ThrowExceptions)
                    throw WrongTypeException("Delete", "Delete");
                else
                    return false;
            }
            else if (this.insertValues.Count > 0)
            {
                throw new Exception("Only one delete statment is allowed by the MySqlWriter");
            }
            else
            {
                if (this.insertValues.ContainsKey(field))
                {
                    if (MySqlCore.ThrowExceptions)
                        throw AlreadyUsedFieldException(field);
                    else
                        return false;
                }
                else
                {
                    this.insertValues.Add(field, value);
                    return true;
                }
            }
        }

        public bool WhereMatch(string field, object value)
        {
            if (this.commandType != MySqlCommandType.Update)
            {
                if (MySqlCore.ThrowExceptions)
                    throw WrongTypeException("WhereMatch", "Update");
                else
                    return false;
            }
            else if (this.whereStatments.ContainsKey(field))
            {
                if (MySqlCore.ThrowExceptions)
                    throw AlreadyUsedFieldException(field);
                else
                    return false;
            }
            else
            {
                MySqlWhere whereStatment = new MySqlWhere();
                whereStatment.Field = field;
                whereStatment.Connector = nextConnector;
                whereStatment.Type = MySqlWhereType.Equal;
                whereStatment.Value = value;
                this.whereStatments.Add(field, whereStatment);
                return true;
            }
        }

        public bool WhereDistinct(string field, object value)
        {
            if (this.commandType != MySqlCommandType.Update)
            {
                if (MySqlCore.ThrowExceptions)
                    throw WrongTypeException("WhereMatch", "Update");
                else
                    return false;
            }
            else if (this.whereStatments.ContainsKey(field))
            {
                if (MySqlCore.ThrowExceptions)
                    throw AlreadyUsedFieldException(field);
                else
                    return false;
            }
            else
            {
                MySqlWhere whereStatment = new MySqlWhere();
                whereStatment.Connector = nextConnector;
                whereStatment.Field = field;
                whereStatment.Type = MySqlWhereType.Different;
                whereStatment.Value = value;
                this.whereStatments.Add(field, whereStatment);
                return true;
            }
        }

        public void And()
        {
            this.nextConnector = MySqlWhereConnector.And;
        }

        public void Or()
        {
            this.nextConnector = MySqlWhereConnector.None;
        }

        public void Execute()
        {
            using (MySqlConnection dbConnection = MySqlCore.GetNewConnection())
            {
                using (MySqlCommand cmd = dbConnection.CreateCommand())
                {
                    switch (commandType)
                    {
                        #region Insert
                        case MySqlCommandType.Insert:
                            {
                                StringBuilder tempFields = new StringBuilder();
                                StringBuilder tempValues = new StringBuilder();
                                bool started = false;
                                foreach (KeyValuePair<string, object> pair in this.insertValues)
                                {
                                    if (!started)
                                    {
                                        tempFields.Append(pair.Key);
                                        tempValues.Append('?' + pair.Key);
                                        started = true;
                                    }
                                    else
                                    {
                                        tempFields.Append("," + pair.Key);
                                        tempValues.Append(",?" + pair.Key);
                                    }
                                    cmd.Parameters.AddWithValue('?' + pair.Key, pair.Value);

                                }
                                this.command.Replace("<F>", tempFields.ToString());
                                this.command.Replace("<V>", tempValues.ToString());
                                cmd.CommandText = this.command.ToString();
                                cmd.ExecuteNonQuery();
                                break;
                            }
                        #endregion
                        #region Update
                        case MySqlCommandType.Update:
                            {
                                bool started = false;
                                foreach (KeyValuePair<string, object> pair in insertValues)
                                {
                                    if (!started)
                                    {
                                        this.command.Append(" Set " + pair.Key + "=?" + pair.Key);
                                        started = true;
                                    }
                                    else
                                    {
                                        this.command.Append(',' + pair.Key + "=?" + pair.Key );
                                    }
                                    cmd.Parameters.AddWithValue('?' + pair.Key, pair.Value);
                                }
                                foreach (KeyValuePair<string, MySqlWhere> whereStatment in this.whereStatments)
                                {
                                    if (whereStatment.Value.Connector == MySqlWhereConnector.And)
                                        this.command.Append(" And");
                                    else if (whereStatment.Value.Connector == MySqlWhereConnector.Or)
                                        this.command.Append(" Or");
                                    else
                                        this.command.Append(" Where");

                                    this.command.Append(" " + whereStatment.Key);

                                    if (whereStatment.Value.Type == MySqlWhereType.Equal)
                                        this.command.Append("=?1");
                                    else if (whereStatment.Value.Type == MySqlWhereType.Different)
                                        this.command.Append("<>?1");

                                    this.command.Append(whereStatment.Key);
                                    cmd.Parameters.AddWithValue("?1" + whereStatment.Key, whereStatment.Value.Value);
                                }
                                cmd.CommandText = this.command.ToString();
                                cmd.ExecuteNonQuery();
                                break;
                            }
                        #endregion
                        #region Delete
                        case MySqlCommandType.Delete:
                            {
                                foreach(string field in this.insertValues.Keys)
                                {
                                    this.command.Replace("<F>", field);
                                    this.command.Replace("<V>", "?" + field);
                                    cmd.Parameters.AddWithValue("?" + field, this.insertValues[field]);
                                    break;
                                }
                                cmd.CommandText = this.command.ToString();
                                cmd.ExecuteNonQuery();
                                break;
                            }
                        #endregion
                    }
                    Console.WriteLine(cmd.CommandText);
                }
            }
        }

        public void BeginExecute()
        {
            Action asyncExecute = new Action(Execute);
            asyncExecute.BeginInvoke(EndExecute, null);
        }

        private void EndExecute(IAsyncResult result)
        {

        }

        private Exception WrongTypeException(string invokedMethod, string allowedType)
        {
            return new Exception("The method " + invokedMethod + " is allowed only when the MySqlWriter's type is " + allowedType);
        }

        private Exception AlreadyChosenTableException()
        {
            return new Exception("A table is already chosen for the " + this.commandType.ToString() + " query");
        }

        private Exception AlreadyUsedFieldException(string field)
        {
            return new Exception("The field " + field + " is already in use, check for repeated " + commandType.ToString() + " or where statments");
        }
    }
}

MySqlEnums//same than before, a few enums added

Spoiler

MySqlReader//Same than before for the moment :/

Spoiler

Code:

 /*****************************************************************************
 * Author: Caparzo/U2_Caparzo.
 * Code written for the project MySqlWrapper.
 * 
 * Date: 10/05/2013
 * Hour: 1:20
 * 
 * Feel free to use or redistribute this class with any project, but please
 * keep this header so credits still goes to the coder of the class.
 *****************************************************************************/
using System;
using System.Text;
using MySql.Data.MySqlClient;

namespace MySqlWrapper
{
	/// <summary>
        /// Wrapper for the MySqlDataReader class.
	/// </summary>
	public class MySqlReader
	{
            #region private/protected members

            private StringBuilder command;
            private MySqlDataReader reader;
            private MySqlCommand cmd;

            #endregion

            #region constructors

            public MySqlReader()
            {
                command = new StringBuilder("SELECT * FROM <T>");
                cmd = MySqlCore.GetNewConnection().CreateCommand();
            }

            #endregion

            #region methods

            /// <summary>
            /// Select the table that contains the data that will be read
            /// </summary>
            /// <param name="Table">Table name</param>
            /// <returns></returns>
            public MySqlReader Select(string Table)
            {
                command.Replace("<T>", Table);
                return this;
            }
            /// <summary>
            /// Select the column that contains the needed data
            /// </summary>
            /// <param name="Column">Column name</param>
            /// <returns></returns>
            public MySqlReader SelectColumn(string Column)
            {
                command.Replace("*", Column);
                return this;
            }

            public MySqlReader Where(string Column, object value)
            {
                command.Append(" where " + Column + "=?" + Column);
                cmd.Parameters.AddWithValue('?' + Column, value);
                return this;
            }

            public void Execute()
            {
                cmd.CommandText = command.ToString();
                reader = cmd.ExecuteReader();
            }

            public MySqlReader ReadSByte(string Column, out sbyte value)
            {
                value = default(sbyte);
                if (reader.Read())
                    value = reader.GetSByte(Column);
                return this;
            }

            public MySqlReader ReadByte(string Column, out byte value)
            {
                value = default(byte);
                if (reader.Read())
                    value = reader.GetByte(Column);
                return this;
            }

            public MySqlReader ReadInt16(string Column, out Int16 value)
            {
                value = default(short);
                if (reader.Read())
                    value = reader.GetInt16(Column);
                return this;
            }

            public MySqlReader ReadUInt16(string Column, out UInt16 value)
            {
                value = default(ushort);
                if (reader.Read())
                    value = reader.GetUInt16(Column);
                return this;
            }

            public MySqlReader ReadInt32(string Column, out Int32 value)
            {
                value = default(int);
                if (reader.Read())
                    value = reader.GetInt32(Column);
                return this;
            }

            public MySqlReader ReadUInt32(string Column, out UInt32 value)
            {
                value = default(uint);
                if (reader.Read())
                    value = reader.GetUInt32(Column);
                return this;
            }

            public MySqlReader ReadInt64(string Column, out Int64 value)
            {
                value = default(long);
                if (reader.Read())
                    value = reader.GetInt64(Column);
                return this;
            }

            public MySqlReader ReadUInt64(string Column, out UInt64 value)
            {
                value = default(ulong);
                if (reader.Read())
                    value = reader.GetUInt64(Column);
                return this;
            }

            public MySqlReader ReadString(string Column, out string value)
            {
                value = default(string);
                if (reader.Read())
                    value = reader.GetString(Column);
                return this;
            }

            public MySqlReader ReadBool(string Column, out bool value)
            {
                value = default(bool);
                if (reader.Read())
                    value = reader.GetBoolean(Column);
                return this;
            }

            public MySqlReader ReadDateTime(string Column, out DateTime value)
            {
                value = default(DateTime);
                if (reader.Read())
                    value = reader.GetDateTime(Column);
                return this;
            }
            /// <summary>
            /// Free the resources used by the MySQLReader
            /// </summary>
            public void Dispose()
            {
                cmd.Dispose();
                cmd.Connection.Dispose();
                reader.Dispose();
            }

            #endregion
        }
}

05/09/2013 13:13 _DreadNought_#2

Oh - The code you linked is very specific to CO.

(That will come those sneaky mods from moving it ;))

05/09/2013 13:51 -impulse-#3

This looks good, but I have some suggestions and questions.

First is: Why don't you allow remote servers for the TCP protocol? For pipes it makes sense that the host would be the same machine but some people prefer the mysql server elsewhere (unlikely in CO's case though).

Second: Some times even 60 ms won't be enough so you should leave mysql handle it's own cleaning up. Considering we're not dealing with only a few connections, in time mysql will get some congestion problems and 60 ms wont be enough to execute whatever you want to do. I am reffering to the ConnectionLifetime.

Third: I dislike your reader. While it might be easier to write than that good old DataAdapter, DataSet and DataRow read, yours will keep a mysql driver open a tad more than the latter. When handling sql you want the drivers to be ready to be used by other parts of the server and thus you should dispose of the connection as soon as possible. Right now you only do that only after you finish reading. (I don't mean the ExecuteReader() function).

Last: Do not use something like a thread pool to handle your mysql queries. The mysql server itself is multi threaded so you dont need to bother doing it yourself. Also keep in mind that by doing so, if your threads get overwhelmed with queries your characters and stuff wont save and you'll allow people to create item duplicates or trade stuff relogin and have them back. Keep mysql on the same threads of those you use to handle the client's packet handlers if possible.

05/09/2013 16:44 nTL3fTy#4

I believe there is a MySqlConnectionStringBuilder for the specific purpose of building connection strings...

05/09/2013 18:21 Spirited#5

It looks like everything that could be said regarding criticism has been said, so I'm just going to praise you. Your multithreading looks fine in regards to concurrent command handling. Keep in mind that MySql is already a multithreaded service though. The only thing your server is really using that thread worker for is to handle the time it takes to execute commands. You were smart in keeping it at two threads. You really don't need to allocate more threads for something like this. It might be argued that you don't need this at all if you're using asynchronous/IO completion ports or a non-blocking socket system. Sometimes you also want the processing thread to wait on that command executing to ensure that it was added successfully. Just a few things to think about.

05/09/2013 20:07 U2_Caparzo#6

Quote:

Originally Posted by -impulse-

First is: Why don't you allow remote servers for the TCP protocol? For pipes it makes sense that the host would be the same machine but some people prefer the mysql server elsewhere (unlikely in CO's case though).
I will check that, would be a good idea for a more portable and usable code

Second: Some times even 60 ms won't be enough so you should leave mysql handle it's own cleaning up. Considering we're not dealing with only a few connections, in time mysql will get some congestion problems and 60 ms wont be enough to execute whatever you want to do. I am reffering to the ConnectionLifetime.
I based the connection strings from this site, and it says that the ConnectionLifetime value is in seconds :s [Only registered and activated users can see links. Click Here To Register...]
Third: I dislike your reader. While it might be easier to write than that good old DataAdapter, DataSet and DataRow read, yours will keep a mysql driver open a tad more than the latter. When handling sql you want the drivers to be ready to be used by other parts of the server and thus you should dispose of the connection as soon as possible. Right now you only do that only after you finish reading. (I don't mean the ExecuteReader() function).
I totally understand, i thought that the Pooling would could handle this kind of situations(anyways, i didn't know about this point, it's something that should be coded better), would be the one that u coded for your 5165 source a good example?
Last: Do not use something like a thread pool to handle your mysql queries. The mysql server itself is multi threaded so you dont need to bother doing it yourself. Also keep in mind that by doing so, if your threads get overwhelmed with queries your characters and stuff wont save and you'll allow people to create item duplicates or trade stuff relogin and have them back. Keep mysql on the same threads of those you use to handle the client's packet handlers if possible.
While i was testing before start coding this i noticed that execute a query could take some ms(even more in the first execution) and again i thought that use a thread to execute them could allow the server use those ms to execute the packets handlers, this time i can't understand very much since almost everything(in fact, i think that everything) is handled in the server itself, mi lack of knowledge maybe is making me blind this time because i can't see how it would be 'exploited'

Quote:

Originally Posted by nTL3fTy

I believe there is a MySqlConnectionStringBuilder for the specific purpose of building connection strings...

i surely will check that later, i'm kinda busy for the moment, thanks for the advice

Quote:

Originally Posted by Fаng

It looks like everything that could be said regarding criticism has been said, so I'm just going to praise you. Your multithreading looks fine in regards to concurrent command handling. Keep in mind that MySql is already a multithreaded service though. The only thing your server is really using that thread worker for is to handle the time it takes to execute commands. You were smart in keeping it at two threads. You really don't need to allocate more threads for something like this. It might be argued that you don't need this at all if you're using asynchronous/IO completion ports or a non-blocking socket system. Sometimes you also want the processing thread to wait on that command executing to ensure that it was added successfully. Just a few things to think about.
i have for sure make some testings in real cases, i will try to emulate a server later to check that, ofc just socket, db, and a client

Thanks u all guys, i got really good advices to improve this, maybe this weekend i will have some time update it :)

05/10/2013 00:12 CptSky#7

Quote:

Originally Posted by U2_Caparzo

again, srry if this is a bad way of do it, i pm'ed cptsky if he could do a fast check to the code to evade any possible flaming, but looks like he is almost all the time disconnected, so hope i can get constructive criticism

Sorry. As I know nobody is really working on EoF, and as I don't check the CO2 section on the other forum (I'm active here), I never saw your PM... You have more chance when PM'ing me here.

---

Overall

You often compare the string to string.Empty, but if the string is null it won't enter in the condition, and well, for example, a null connection string won't be valid ! I wouldn't compare to an empty string, but directly call IsNullOrEmpty() on the string. It's cleaner, respect all use cases, and probably more optimized.

When using switches, I always add a default case. It may be important (like in a constructor) to be sure to have a valid object, or to signal incoherent behaviour. It's not an obligation (although some compiler do force the presence of a default case in C++).

MySQL is multi-threaded as already stated. You don't need, and shouldn't, handle it yourself. Plus, no thread-pool. You must execute the queries sequentially for a client. Imagine the case of two commands. One set the money to 200$, the second set the money to 100$ (you bought something else). In a thread-pool, you have chance two executes the second command before the first one... It opens then door to plenty of abuse.

You don't follow one naming convention through the project. Adopt one and stick with it ;)

DBCore.cs

Your connection string doesn't use escape characters to securize (?) the SQL string. I know it should be called with valid params, but always better to protect the SQL string.

ConnectionLifeTime is way too small. It will work on small scale local server.

DBEnums

Stupid, but you seems to use two naming convention for internal members of enums. I would stick with one. It's not a real problem, but I love beautiful code :)

DBThread

What happens if if(commands.TryDequeue(out cmd)) fail ? Well, it's an illogical case, no ? Why the method should be called when the commands queue is empty ? It's a good check, but it shouldn't happens and should be checked probably before ?

What happens if you call Start() several times ? Old threads will continue ?

using(var conn = DBCore.GetNewConnection())
You know the type right ? Don't use var for this case, it's useless.

MySQLCommand

Not convinced by the return this;... After all, you already have the object. I would probably return a boolean instead.

What happens if a parameter is null ? What happens if a parameter is invalid and inject some code in the command ? (SQL injection) Use escape characters and validate the input.

MySQLReader
Same thing than the MySQLCommand almost.

For all the ReadX() why you return the instance instead of returning the value ? It's pretty useless to use an out parameter. Also, you don't use templates, right ? So, the default keyword is useless and you should directly attribute the default value. It's not a generic code, I don't see the point of using default which isn't explicit at all.

ThreadFactory

No way to stop the threads ? Else it's fine.

I think the others have done a fine review.

05/10/2013 04:21 U2_Caparzo#8

Quote:

Originally Posted by CptSky

Sorry. As I know nobody is really working on EoF, and as I don't check the CO2 section on the other forum (I'm active here), I never saw your PM... You have more chance when PM'ing me here.

---

Overall

You often compare the string to string.Empty, but if the string is null it won't enter in the condition, and well, for example, a null connection string won't be valid ! I wouldn't compare to an empty string, but directly call IsNullOrEmpty() on the string. It's cleaner, respect all use cases, and probably more optimized.

Damn, i always forgot those comparissons :s, i'm editing it rigth now so it should have no possible errors(ofc its possible, but should not be a coding error)

When using switches, I always add a default case. It may be important (like in a constructor) to be sure to have a valid object, or to signal incoherent behaviour. It's not an obligation (although some compiler do force the presence of a default case in C++).

I agreed totally, but its like a coder using this wrapper were going to crash it intentionally parsing an invalid input to the enum, i'll gonna work on that though.

MySQL is multi-threaded as already stated. You don't need, and shouldn't, handle it yourself. Plus, no thread-pool. You must execute the queries sequentially for a client. Imagine the case of two commands. One set the money to 200$, the second set the money to 100$ (you bought something else). In a thread-pool, you have chance two executes the second command before the first one... It opens then door to plenty of abuse.

i kinda noticed that possible error, atm i was using 1 single thread to execute the commands, the main reason of using this thread is because i want to use the time required for create/open a connection/execute the query to generate a faster answer(packet send) to the client, something like this 'schema'

Spoiler

[Only registered and activated users can see links. Click Here To Register...]

You don't follow one naming convention through the project. Adopt one and stick with it ;)

Well, this is kinda difficult to me :/, i will have to read some guides of global naming convention(i'm sure there are), i will do my best to make it look better since it will help during any other project.

DBCore.cs

Your connection string doesn't use escape characters to securize (?) the SQL string. I know it should be called with valid params, but always better to protect the SQL string.
ConnectionLifeTime is way too small. It will work on small scale local server.

I just edited that class, it's now using the MySqlConnectionStringBuilder as
recommended by nTL3fTy (and any other coder who knows about this i guess), and checking the strings so now it HAS to be a valid connection string except if someone really want to have an invalid one (wrong db names, wrong user, etc...)
about the ConnectionLifeTime, i'm sure that i have read that it's expressed in seconds, and i thought that 1 minute would be enough

DBEnums

Stupid, but you seems to use two naming convention for internal members of enums. I would stick with one. It's not a real problem, but I love beautiful code :)

just noticed that obvious and idiot style error :o, just changed it. :p

DBThread

What happens if if(commands.TryDequeue(out cmd)) fail ? Well, it's an illogical case, no ? Why the method should be called when the commands queue is empty ? It's a good check, but it shouldn't happens and should be checked probably before ?

Well, this time i got a little confused

What happens if you call Start() several times ? Old threads will continue ?

Going to add a check to that

using(var conn = DBCore.GetNewConnection())
You know the type right ? Don't use var for this case, it's useless.

MySQLCommand

Not convinced by the return this;... After all, you already have the object. I would probably return a boolean instead.

it was just to use Command.Insert(..).Insert(...).Execute();
instead of
Command.Insert(...);
Command.Insert(...);
Command.Execute();

What happens if a parameter is null ? What happens if a parameter is invalid and inject some code in the command ? (SQL injection) Use escape characters and validate the input.

would not Parameters.AddWithValue() do the job?... all my life is a lie :(, please confirm that so i will start doing some sanity checks, i'm searching about it though.

MySQLReader
Same thing than the MySQLCommand almost.

For all the ReadX() why you return the instance instead of returning the value ? It's pretty useless to use an out parameter. Also, you don't use templates, right ? So, the default keyword is useless and you should directly attribute the default value. It's not a generic code, I don't see the point of using default which isn't explicit at all.

I will follow -impulse-'s advice, i'm going to research about it, and then i expect to do something a bit better, i will follow your advice too obviously, will edit a bit the code, as summary, change a bit of less typing for a more functional code

ThreadFactory

No way to stop the threads ? Else it's fine.
could not figure ways to do so except using Abort(), i will look for others methods, i kinda dislike using Thread.Abort().

I think the others have done a fine review.

Thanks you :), i wrote some answers in bold font.

05/10/2013 11:39 InfamousNoone#9

Quote:

using(var conn = DBCore.GetNewConnection())
You know the type right ? Don't use var for this case, it's useless.

I don't see the problem with this.

05/11/2013 13:12 Korvacs#10

Quote:

Originally Posted by InfamousNoone

I don't see the problem with this.

'var' should only be used when working with anonymous types, beyond that your just making your code easier to read while sacrificing clarity. For example using 'var' in that case is easier than the full object name yes, but it isn't clear what 'DBCore.GetNewConnection()' returns except that its some form of new connection.

So in this case the type should be declared explicitly.

05/11/2013 14:38 Smaehtin#11

Who cares, the variable is only used inside the scope of the using statement anyways, no big deal imo

05/11/2013 15:11 Korvacs#12

Quote:

Originally Posted by Smaehtin

Who cares, the variable is only used inside the scope of the using statement anyways, no big deal imo

Its statements like this that help contribute to a large amount of bad practice found in programming all around the world.

05/11/2013 16:16 Smaehtin#13

Quote:

Originally Posted by Korvacs

Its statements like this that help contribute to a large amount of bad practice found in programming all around the world.

Haha, have you ever used ReSharper?

05/11/2013 18:05 _DreadNought_#14

Quote:

Originally Posted by Smaehtin

Haha, have you ever used ReSharper?

Yeap, reSharper would most definitely agree with the OP.

I would too.

@korv
[Only registered and activated users can see links. Click Here To Register...]

It appears this debate is one programmers have often

Also your signature is a load of shit, the host you're doing is a speed test on is local.

05/11/2013 18:55 Smaehtin#15

Quote:

Originally Posted by _DreadNought_

Also your signature is a load of shit, the host you're doing is a speed test on is local.

How do I force speedtest.net to localhost?? :confused:

Page 1 of 2