[Release] Fixed MySql Wrapper

02/08/2012 22:23 Spirited#1
Hey everyone.

This was a request from a close friend of mine who's having issues with his mysql wrapper from Impulse's source.

MySql tables, columns, and values can have spaces... so here's a wrapper that fixes any issues that you might have with spaces (note: the structure was coded by Impulse, all I did was fix it):

Code:
public class MySqlCommand
    {
        private MySqlCommandType _type;
        public MySqlCommandType Type
        {
            get { return _type; }
            set { _type = value; }
        }
        protected string _command;
        public string Command
        {
            get { return _command; }
            set { _command = value; }
        }
        private bool firstPart = true;
        private Dictionary<byte, string> Fields;
        private Dictionary<byte, long> longValues;
        private Dictionary<byte, ulong> ulongValues;
        private Dictionary<byte, bool> boolValues;
        private Dictionary<byte, string> stringValues;
        private byte lastpair;
        public MySqlCommand(MySqlCommandType Type)
        {
            this.Type = Type;
            switch (Type)
            {
                case MySqlCommandType.SELECT:
                    {
                        _command = "SELECT * FROM <R>";
                        break;
                    }
                case MySqlCommandType.UPDATE:
                    {
                        _command = "UPDATE <R> SET ";
                        break;
                    }
                case MySqlCommandType.INSERT:
                    {
                        Fields = new Dictionary<byte, string>();
                        longValues = new Dictionary<byte, long>();
                        ulongValues = new Dictionary<byte, ulong>();
                        boolValues = new Dictionary<byte, bool>();
                        stringValues = new Dictionary<byte, string>();
                        lastpair = 0;
                        _command = "INSERT INTO <R> (<F>) VALUES (<V>)";
                        break;
                    }
                case MySqlCommandType.DELETE:
                    {
                        _command = "DELETE FROM <R> WHERE <C> = <V>";
                        break;
                    }
                case MySqlCommandType.COUNT:
                    {
                        _command = "SELECT count(<V>) FROM <R>";
                        break;
                    }
            }
        }

        #region Select
        public MySqlCommand Select(string table)
        {
            _command = _command.Replace("<R>", "`" + table + "`");
            return this;
        }
        #endregion

        #region Count
        public MySqlCommand Count(string table)
        {
            _command = _command.Replace("<R>", "`" + table + "`");
            return this;
        }
        #endregion

        #region Delete
        public MySqlCommand Delete(string table, string column, string value)
        {
            _command = _command.Replace("<R>", "`" + table + "`");
            _command = _command.Replace("<C>", "`" + column + "`");
            _command = _command.Replace("<V>", "'" + value + "'");
            return this;
        }
        public MySqlCommand Delete(string table, string column, long value)
        {
            _command = _command.Replace("<R>", "`" + table + "`");
            _command = _command.Replace("<C>", "`" + column + "`");
            _command = _command.Replace("<V>", value.ToString());
            return this;
        }
        public MySqlCommand Delete(string table, string column, ulong value)
        {
            _command = _command.Replace("<R>", "`" + table + "`");
            _command = _command.Replace("<C>", "`" + column + "`");
            _command = _command.Replace("<V>", value.ToString());
            return this;
        }
        public MySqlCommand Delete(string table, string column, bool value)
        {
            _command = _command.Replace("<R>", "`" + table + "`");
            _command = _command.Replace("<C>", "`" + column + "`");
            _command = _command.Replace("<V>", (value ? "1" : "0"));
            return this;
        }
        public MySqlCommand COUNT(string table)
        {
            _command = _command.Replace("<R>", "`" + table + "`");
            return this;
        }
        #endregion

        private bool Comma()
        {
            if (firstPart)
            {
                firstPart = false;
                return false;
            }
            if (_command[_command.Length - 1] == ',' || _command[_command.Length - 2] == ',' || _command[_command.Length - 3] == ',')
                return false;
            return true;
        }

        #region Update
        public MySqlCommand Update(string table)
        {
            _command = _command.Replace("<R>", "`" + table + "`");
            return this;
        }
        public MySqlCommand Set(string column, long value)
        {
            if (Type == MySqlCommandType.UPDATE)
            {
                if (Comma())
                    _command = _command + ",`" + column + "` = " + value.ToString() + " ";
                else
                    _command = _command + "`" + column + "` = " + value.ToString() + " ";
            }
            return this;
        }
        public MySqlCommand Set(string column, ulong value)
        {
            if (Type == MySqlCommandType.UPDATE)
            {
                bool comma = false;
                comma = (_command[_command.Length - 1] == ',' || _command[_command.Length - 2] == ',' || _command[_command.Length - 3] == ',') ? false : true;
                if (comma)
                    _command = _command + ",`" + column + "` = " + value.ToString() + " ";
                else
                    _command = _command + "`" + column + "` = " + value.ToString() + " ";
            }
            return this;
        }
        public MySqlCommand Set(string column, string value)
        {
            if (Type == MySqlCommandType.UPDATE)
            {
                if (Comma())
                    _command = _command + ",`" + column + "` = '" + value + "' ";
                else
                    _command = _command + "`" + column + "` = '" + value + "' ";
            }
            return this;
        }
        public MySqlCommand Set(string column, bool value)
        {
            if (Type == MySqlCommandType.UPDATE)
            {
                if (Comma())
                    _command = _command + ",`" + column + "` = " + (value ? "1" : "0") + " ";
                else
                    _command = _command + "`" + column + "` = " + (value ? "1" : "0") + " ";
            }
            return this;
        }
        #endregion

        #region Insert
        public MySqlCommand Insert(string table)
        {
            _command = _command.Replace("<R>", "`" + table + "`");
            return this;
        }
        public MySqlCommand Insert(string field, long value)
        {
            Fields.Add(lastpair, field);
            longValues.Add(lastpair, value);
            lastpair++;
            return this;
        }
        public MySqlCommand Insert(string field, ulong value)
        {
            Fields.Add(lastpair, field);
            ulongValues.Add(lastpair, value);
            lastpair++;
            return this;
        }
        public MySqlCommand Insert(string field, bool value)
        {
            Fields.Add(lastpair, field);
            boolValues.Add(lastpair, value);
            lastpair++;
            return this;
        }
        public MySqlCommand Insert(string field, string value)
        {
            Fields.Add(lastpair, field);
            stringValues.Add(lastpair, value);
            lastpair++;
            return this;
        }
        #endregion

        #region Where
        public MySqlCommand Where(string column, long value)
        {
            _command = _command + "WHERE `" + column + "` = " + value;
            return this;
        }
        public MySqlCommand Where(string column, long value, bool greater)
        {
            if (greater)
                _command = _command + "WHERE `" + column + "` > " + value;
            else
                _command = _command + "WHERE `" + column + "` < " + value;
            return this;
        }
        public MySqlCommand Where(string column, ulong value)
        {
            _command = _command + "WHERE `" + column + "` = " + value;
            return this;
        }
        public MySqlCommand Where(string column, string value)
        {
            _command = _command + "WHERE `" + column + "` = '" + value + "'";
            return this;
        }
        public MySqlCommand Where(string column, bool value)
        {
            _command = _command + "WHERE `" + column + "` = " + (value ? "1" : "0");
            return this;
        }
        #endregion

        #region And
        public MySqlCommand And(string column, long value)
        {
            _command = _command + " AND `" + column + "` = " + value;
            return this;
        }
        public MySqlCommand And(string column, ulong value)
        {
            _command = _command + " AND `" + column + "` = " + value;
            return this;
        }
        public MySqlCommand And(string column, string value)
        {
            _command = _command + " AND `" + column + "` = '" + value + "'";
            return this;
        }
        public MySqlCommand And(string column, bool value)
        {
            _command = _command + " AND `" + column + "` = " + (value ? "1" : "0");
            return this;
        }
        #endregion

        #region Order
        public MySqlCommand Order(string column)
        {
            _command = _command + "ORDER BY " + column + "";
            return this;
        }
        #endregion

        public void Execute()
        {
            if (Type == MySqlCommandType.INSERT)
            {
                string fields = "";
                string values = "";
                byte x;
                for (x = 0; x < lastpair; x++)
                {
                    bool comma = (x + 1) == lastpair ? false : true;
                    #region Fields
                    if (comma)
                        fields += Fields[x] + ",";
                    else
                        fields += Fields[x];
                    #endregion
                    #region Values
                    if (longValues.ContainsKey(x))
                    {
                        if (comma)
                            values += longValues[x].ToString() + ",";
                        else
                            values += longValues[x].ToString();
                    }
                    else if (ulongValues.ContainsKey(x))
                    {
                        if (comma)
                            values += ulongValues[x].ToString()[x] + ",";
                        else
                            values += ulongValues[x].ToString();
                    }
                    else if (boolValues.ContainsKey(x))
                    {
                        if (comma)
                            values += (boolValues[x] ? "1" : "0") + ",";
                        else
                            values += (boolValues[x] ? "1" : "0");
                    }
                    else if (stringValues.ContainsKey(x))
                    {
                        if (comma)
                            values += "'" + stringValues[x] + "'" + ",";
                        else
                            values += "'" + stringValues[x] + "'";
                    }
                    #endregion
                }
                _command = _command.Replace("<F>", fields);
                _command = _command.Replace("<V>", values);
            }
            using (MySqlConnection conn = DataHolder.MySqlConnection)
            {
                conn.Open();
                MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(Command, conn);
                cmd.ExecuteNonQuery();
            }
        }
        public void UpdateInsert()
        {
            if (Type == MySqlCommandType.INSERT)
            {
                string fields = "";
                string values = "";
                byte x;
                for (x = 0; x < lastpair; x++)
                {
                    bool comma = (x + 1) == lastpair ? false : true;
                    #region Fields
                    if (comma)
                        fields += Fields[x] + ",";
                    else
                        fields += Fields[x];
                    #endregion
                    #region Values
                    if (longValues.ContainsKey(x))
                    {
                        if (comma)
                            values += longValues[x].ToString() + ",";
                        else
                            values += longValues[x].ToString();
                    }
                    else if (ulongValues.ContainsKey(x))
                    {
                        if (comma)
                            values += ulongValues[x].ToString()[x] + ",";
                        else
                            values += ulongValues[x].ToString();
                    }
                    else if (boolValues.ContainsKey(x))
                    {
                        if (comma)
                            values += (boolValues[x] ? "1" : "0") + ",";
                        else
                            values += (boolValues[x] ? "1" : "0");
                    }
                    else if (stringValues.ContainsKey(x))
                    {
                        if (comma)
                            values += "'" + stringValues[x] + "'" + ",";
                        else
                            values += "'" + stringValues[x] + "'";
                    }
                    #endregion
                }
                _command = _command.Replace("<F>", fields);
                _command = _command.Replace("<V>", values);
            }
        }
        private string _lasterror = null;
        public string LastError
        {
            get
            {
                return _lasterror;
            }
            set
            {
                _lasterror = value;
            }
        }
    }
    public enum MySqlCommandType
    {
        DELETE, INSERT, SELECT, UPDATE, COUNT
    }
Sincerely,
Fang
02/08/2012 22:27 xBlackPlagu3x#2
Thank you Fang! THAT was the problem with my source! I was using Impulse's MySQL method and my tables had spaces!
02/08/2012 23:15 killersub#3
I am very fond of the source and the coding style he uses, therefore thanks :).

I will be using this.
02/08/2012 23:28 I don't have a username#4
NHibernate :rolleyes:
02/08/2012 23:32 Spirited#5
Quote:
Originally Posted by I don't have a username View Post
NHibernate :rolleyes:
I don't use MySql. It's terrible.
I use NHibernate with Ms Sql.
03/23/2012 23:10 bryce16#6
Random but good-to-know question:
-Does this somehow benefit testers from not being able to login after a sertain time?(I'm currently having that issue so yea =p)

EDIT: I ran across this issue on the ConquerItemTable:"Cannot implicitly convert type 'void' to 'int'."

Quote:
int res = cmd.Update("items").Set("Position", 0).Where("EntityID", EntityID).And("Position", position).Execute();
Quote:
int res = cmd.Update("items").Set("SocketOne", (byte)Item.SocketOne)
.Set("SocketTwo", (byte)Item.SocketTwo).Where("UID", Item.UID).Execute();
03/23/2012 23:25 Spirited#7
Quote:
Originally Posted by bryce16 View Post
Random but good-to-know question:
-Does this somehow benefit testers from not being able to login after a sertain time?(I'm currently having that issue so yea =p)

EDIT: I ran across this issue on the ConquerItemTable:"Cannot implicitly convert type 'void' to 'int'."
Noo, I know what you're thinking though. Here's something I developed to fix connection problems (where the connection maxes out): [Only registered and activated users can see links. Click Here To Register...]

Also, with the conversion thing, let me help you break that down.
So you're starting with this:
Code:
int res = cmd.Update("items").Set("SocketOne", (byte)Item.SocketOne)
.Set("SocketTwo", (byte)Item.SocketTwo).Where("UID", Item.UID).Execute();
If you replace the set and where strings with "X", it looks like this:
Code:
int res = cmd.Update("items").Set("X").Set("X").Where("X").Execute();
And if you simplify that even more and look at the end product of what it wants to do... it's creating a command that'll update the table "items" and execute as so:

Code:
int res = cmd.Execute();
See what's wrong with that yet? Execute doesn't return an int. It returns nothing (not null, but void since it's a void function). Hopefully that makes more sense.
03/23/2012 23:32 bryce16#8
Edit: I got the connection stability part done, so thanks for that, as for the conversion thing, I'm a little stuck with converting those lines of codes.
09/22/2013 14:08 shadowman123#9
well i dont like the way its coded .. and it got a problem which is the connection is still Alive After the Query is Executed and thats completely wrong ....
09/22/2013 17:06 Smallxmac#10
That is why he uses his kill connection
09/22/2013 17:29 shadowman123#11
KillConnection isnt the Correct way of Handling the Situation ... cuz u Run its codes Through Threading which consumes CPU Cycles Raising the Cpu Usage...so thats not Right .. what u should do is Close the connection everytime Query is Excuted .y would it be Opened for no reason ?
09/22/2013 21:52 Spirited#12
I made a post that you bumped explaining exactly why I was wrong in this post. [Only registered and activated users can see links. Click Here To Register...]
Closed, please don't bump multiple threads about the same problem.