[Release] MySQL Insert system

[_Emme_]

Ello!

First of all, this is NOT sourcedependent, which means you can use this on any source without modifying the code.

This is an replacement for all of you who still uses normal MySQL queries. Use this code to clean up, make it easier and more orginazed.

Here's how it looks:

Example: (Original (LOTF for example))

public static void AddNPC(int UID, int Type, string Name, int Flags, int Direction, int X, int Y, int Map, int SobType)
{
MySqlCommand Command = new MySqlCommand("INSERT INTO npcs (UID,Type,Name,Flags,Direction,X,Y,Map,SobType) VALUES ("
+ UID + ",'" + Type + ",'" + Name + ",'" + Flags + ",'" + Direction + ",'" + X + ",'" + Y + ",'" + Map + ",'" + SobType + ")", Connection);
}

Example: (My way)

public static void AddNPC(int UID, int Type, string Name, int Flags, int Direction, int X, int Y, int Map, int SobType)
{
object[] npc = new object[9];
npc[0] = UID;
npc[1] = Type;
npc[2] = 10;
npc[3] = Flags;
npc[4] = Direction;
npc[5] = X;
npc[6] = Y;
npc[7] = Map;
npc[8] = SobType;
Insert("npcs", npc);
}

So basically, you create an object array, and send it to the Insert void. Also, the code finds the columnnames by itself, so you don't need to define them. Here's the insert code:

static void Insert(string table, object[] objects)
{
MySqlDataReader rdr = new MySqlCommand("SELECT * FROM `" + table + "`",connection).ExecuteReader();
if (objects.Length != rdr.FieldCount)
{
if (objects.Length > rdr.FieldCount)
Console.WriteLine("INSERT:FAIL : Too many values");
else
Console.WriteLine("INSERT:FAIL : Need more values");
}

string query = "INSERT INTO " + table + " (";
if(rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; i++)
{
query += rdr.GetName(i);
if (i != rdr.FieldCount - 1)
query += ",";
else
query += ")";
}
query += " VALUES (";
for (int x = 0; x < objects.Length; x++)
{
query += objects[x];
if (x != objects.Length - 1)
query += ",";
else
query += ")";
}
}
rdr.Close();
new MySqlCommand(query,connection).ExecuteNonQuery();
Console.WriteLine("Successfully added a row to `" + table + "`");
}

Takes 4-5 milliseconds to execute (using Stopwatch)

Have fun!

[Arcо]

Nice Emil^^
Haven't seen a release from you in a long time man.

[_Emme_]

Originally Posted by .Arco

Nice Emil^^
Haven't seen a release from you in a long time man.

Yer I know, nothing I planned, coded this for a mate and figured I could release it for others to use aswell..

[Arcо]

Originally Posted by EmmeTheCoder

Yer I know, nothing I planned, coded this for a mate and figured I could release it for others to use aswell..

Extremely useful, I may use it for a few things that I want loaded from mysql, like characters.

[_Emme_]

Yuki just tried with guilds, worked successfully

Original code:

public static bool NewGuild(ushort GuildID, string GuildName, Character Creator)
{
try
{

public static bool NewGuild(ushort GuildID, string GuildName, Character Creator)
{
try
{

MySqlCommand Command = new MySqlCommand("INSERT INTO guilds (GuildID,GuildName,Fund,GuildLeader,MembersCount,D Ls,NormalMembers,Allies,Enemies,GWWins,HoldingPole ,Bulletin) VALUES (" + GuildID + ",'" + GuildName + "',1000000,'" + Creator.Name + ":" + Creator.UID.ToString() + ":" + Creator.Level.ToString() + ":1000000',1,'','','','',0,0,'New guild')", Connection);
Command.ExecuteNonQuery();
return true;
}
catch { return false; }
}

This one:

public static bool CreateGuild(ushort GuildID, string GuildName, Character Creator)
{
try
{
object[] guild = new object[12];
guild[0] = GuildID;
guild[1] = GuildName;
guild[2] = 100000;
guild[3] = Creator.Name + ":" + Creator.UID.ToString() + ":" + Creator.Level.ToString() + ":" + "1000000";
guild[4] = 1;
guild[5] = "";
guild[6] = "";
guild[7] = "";
guild[8] = "";
guild[9] = 0;
guild[10] = 0;
guild[11] = "New guild";
Insert("guilds", guild);
return true;
}
catch
{
return false;
}
}

[Basser]

Thanks a lot, look great! I'm okay with my MySQL handler though.

[Korvacs]

Still not using stored procedures?

        public static void NewCharacter(uint AccountID, uint ServerID, string CharacterName, ushort Avatar, ushort ModelID, byte Class)
        {
            SqlCommand Com = new SqlCommand("InsertNewCharacter");
            Com.CommandType = System.Data.CommandType.StoredProcedure;
            Com.Parameters.Add("@AccountID", System.Data.SqlDbType.Int).Value = AccountID;
            Com.Parameters.Add("@ServerID", System.Data.SqlDbType.Int).Value = ServerID;
            Com.Parameters.Add("@Avatar", System.Data.SqlDbType.SmallInt).Value = Avatar;
            Com.Parameters.Add("@ModelID", System.Data.SqlDbType.SmallInt).Value = ModelID;
            Com.Parameters.Add("@Class", System.Data.SqlDbType.SmallInt).Value = Class;
            Com.Parameters.Add("@CharacterName", System.Data.SqlDbType.VarChar).Value = CharacterName;
            Database.QueueCommand(Com);
        }

Also you have the packet processing thread do your database submissions for you? If theres latency while submitting to the database your going to be holding back that thread for second after second while waiting for the connection to go through or for the connection to time out. You should really have a seperate thread running processing all of your database submissions so that if there is a problem connecting to the database you dont cause the player experience latency.

I realise that this is for Inserting, however there are many client initiated processes which result in insert queries.

[_Emme_]

Originally Posted by Korvacs

Still not using stored procedures?

I personally (or well, back when I was doing CO programming) use Subsonic as it's much easier and faster in many ways, but this was if people just wanted an easier way to do their queries, although I do not recommend using normal queries and friends who ask me about help over MSN knows that, I try to lead them to Subsonic, but oh well =p

What pretty much makes this so much easier is the way it's organized, plus that it uses DataReader.GetName(), so you won't need to fill in the column name.

And yes, this is for insert only, but could easily be modified to be able to update, delete etc.

[Korvacs]

What makes it easy also makes it ineffcient, your making the server do more work than is neccassery just to save yourself the trouble of writing out the command. Also you have to create an array of objects which is not needed, to save yourself the time of writing out the default values once.

If you used stored procedures for example, when you create the procedure on the server you can do this (this is the other half of the procedure i posted earlier):

ALTER PROCEDURE dbo.InsertNewCharacter
	@AccountID int,
	@ServerID int,
	@ModelID smallint,
	@Class smallint,
	@Avatar smallint,
	@CharacterName nchar(16)	
AS
	INSERT INTO "Character" VALUES(@AccountID, @ServerID, @CharacterName, 'None', 0, 0, 0, @ModelID, @Avatar, 1, @Class, 310, 61, 109, 1010, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
	RETURN

So you need only send the data which is dynamic, and infact you can create dynamic data durring this procedure at the database side instead of having the server do it.

All in all stored procedures has this beat all over the place >.< and even standard insert sql commands would be faster than what you have released today.

Edit: Just did a stopwatch test on the stored procedure, 1ms.

[tanelipe]

Nice, perhaps use params object[] Values instead of object[] and also how is the string entering working, didn't work for me without putting extra ' around it.


This way it simplfies to:

PHP Code:

public static bool AddNPC(uint UID, int Type, string Name, int Flags, int Direction, int X, int Y, int Map, int SobType)
{
    Insert("Npcs", UID, Type, 10, Flags, Direction, X, Y, Map, SobType);
} 


[furkan141415]

Emme #Thanks For ReLease

[_Emme_]

@Korvacs
Alright, you made your point, although I personally prefer Subsonic

@Tanelipe
Thanks! I forgot to correct that.

Replace query += objects[x] with:

if (objects[x].GetType() == typeof(string))
query += "'" + objects[x] + "'";
else
query += objects[x];

Thanks for positive feedback

[Luiz01]

Oh my ***!!! how you found out? that is what i was looking for!

[_Emme_]

Originally Posted by Luiz01

Oh my ***!!! how you found out? that is what i was looking for!

How do you mean with found out? How I got to think of this? Just to make it easier for a friend who was struggeling with 'normal' MySQL queries, I figured this system would make it lots easier ^^

[~Yuki~]

Originally Posted by EmmeTheCoder

How do you mean with found out? How I got to think of this? Just to make it easier for a friend who was struggeling with 'normal' MySQL queries, I figured this system would make it lots easier ^^

You defo helped that "friend"