C# Mysql Delay while updating/add/Get users in my program

Question

Im writing a code which is saving users from a program in sql tables but when 15 users in one time are saving or updating im getting 1 min delay and program not responding... Can you help me. Its my code.

public bool GetUser(ref clsConnection c)
    {
        try
        {
            MySqlConnection connect = new MySqlConnection(connectionMysql);
            connect.Open();
            MySqlCommand query = new MySqlCommand("SELECT * FROM Users WHERE User_Name='" + Escape(c.Username) + "'", connect);
            query.Prepare();
            MySqlDataReader dr = query.ExecuteReader();
            if (dr.Read())
            {
                c.Username = dr[1].ToString();
                c.NoColPlyName = dr[2].ToString();
                c.Cash = double.Parse(dr[3].ToString());
                c.Password = dr[4].ToString();
            }
            else
            {
                dr.Close();
                connect.Close();
                return false;
            }
            dr.Close();
            connect.Close();
            return true;
        }
    }
    public void UpdateUser(clsConnection u)
    {
        MySqlConnection cn = new MySqlConnection(connectionMysql);
        try
        {
            if (u.Username != "")
            {
                cn.Open();
                MySqlCommand query = new MySqlCommand(@"UPDATE Users SET User_Name=@User_Name,User_PlyName=@User_PlyName,User_Cash=@User_Cash,User_Passowrd=@User_Password WHERE User_Name='" + Escape(u.Username) + "';", cn);
                if (query != null)
                {
                    query.Parameters.AddWithValue("@User_Name", Escape(u.Username));
                    query.Parameters.AddWithValue("@User_PlyName", Escape(u.NoColPlyName));
                    query.Parameters.AddWithValue("@User_Cash", u.Cash);
                    query.Parameters.AddWithValue("@User_Passowrd", u.Password);
                    cn.Close();
                    return;
                }
                else
                {
                    return;
                }
            }
        }
    }
    public void AddUser(clsConnection c)
    {
        try
        {
            if (c.Username != "")
            {
                Query(@"INSERT INTO Users (User_Name,User_PlayerName,User_Cash,User_Passowrd) VALUES ('" +
                    Escape(c.Username) + "', '" +
                    Escape(c.NoColPlyName) + "', '" +
                    c.Cash + "', '" +
                    Espace(c.Passoword) + "');");
            }
        }
    }

//when 15 users try to connect to program program not responding and delay is very big. When <10 users connected to program, program works good,but +10 delay is big...

You should use `using` block to create/open/close the database connection safely. — Chetan, Jul 20 '20 at 06:05
You don't need to Escape() the values in your UPDATE - they're parameterized so escaping them will damage them. You should follow the same notions of parameterizing them in the SELECT/INSERT.. and then you should throw Escape() away because you won't need it any more — Caius Jard, Jul 20 '20 at 06:19
It's probably nothing to do with the number of users per se, just that increasing numbers of users increases contention for locked resources. Switch to using Dappe or Entity Framework; this data access code is fairly terrible and the program would benefit from handing the job off to a library that will make a good job of parameterizing and managing simultaneous access. We don't need to write this stuff manually any more, haven't done for years — Caius Jard, Jul 20 '20 at 06:24
Thank you, Caius. Si i need Escape(c.Username) make only c.Username and every where remove escape? :) — Djani_Bulgaria, Jul 20 '20 at 06:25
I mean extend the maximum connection of database will help you. — Arphile, Jul 20 '20 at 23:36
@Djani_Bulgaria it's actually you need to do in database with permission.https://stackoverflow.com/questions/22297773/how-to-increase-mysql-connectionsmax-connections see here. — Arphile, Jul 21 '20 at 23:38
Enable auto-commit. it must be required if you want to use multi connection. — Arphile, Jul 21 '20 at 23:39
On AddUser function i sugest you to use SQL paramenters in order to prevent SQL injection — integer, Jul 23 '20 at 12:52

Madeline Kallis · Answer 1 · 2020-09-18T09:50:21.803

You should put your query in a using statement like this:

using (MySqlConnection con = new MySqlConnection(connectionMysql))
{
   con.Open();

   using (MySqlCommand com = con.CreateCommand())
   {
      com.CommandText = "SELECT * FROM Users WHERE User_Name='" + Escape(c.Username) + "'";

      using (MySqlDataReader dr = com.ExecuteReader())
      {
         if (dr.Read())
         {
            c.Username = dr[1].ToString();
            c.NoColPlyName = dr[2].ToString();
            c.Cash = double.Parse(dr[3].ToString());
            c.Password = dr[4].ToString();
         }
         else
         {
            dr.Close();
            connect.Close();
            return false;
         }
         return true;
      }
   }
}

And then you can implement the same method to your UPDATE and INSERT queries

C# Mysql Delay while updating/add/Get users in my program

1 Answers1