How to Correctly Get data from MySQL Table C#

Question

I have a little problem with my codes. I'm trying to make my program faster, because it have too big delay, when i'm getting data from mysql and need to make my code faster. Can you help me about this code, is correctly to select * in table and is it good idea? Thank you! It's my code!

       public bool GettingPlayers(ref clsConnection c)
    {
        try 
        {
            MySqlConnection connect = new MySqlConnection(connectionMysql);
            connect.Open();
            MySqlCommand query = new MySqlCommand("SELECT * FROM Users Where Username='" + Escape(c.Username) + "'", connect);
            query.Prepare();
            MySqlDataReader dr = query.ExecuteReader();
            if (dr.Read())
            {
                c.Username = dr[1].ToString();
                c.Cash = double.Parse(dr[2].ToString());
                c.TotalDistance = double.Parse(dr[3].ToString());
                c.TotalHealth = double.Parse(dr[4].ToString());
                c.Password = dr[5].ToString();
                c.Status = int.Parse(dr[6].ToString());
                c.IpAdress = dr[7].ToString();
                c.TotalJobsDone = int.Parse(dr[8].ToString());

            }
            else
            {
                dr.Close();
                connect.Close();
                return false;
            }
            dr.Close();
            connect.Close();
            return true;
        }
        catch (Exception ex)
        {
            Form1 frm1 = new Form1();
            frm1.LogTextToFile("sqlError", "GettingPlayers - " + ex);
            return false;
        }
    }

Also I have a public string Escape(string str) => str.Replace("'", "\'").Replace(""", "\"");

`'" + Escape(c.Username) + "'",` Do **not** do this. https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection . Also, I'd strongly suggest reading up on Dapper - it will reduce the size of the code block by around 70% I suspect. And be easier to read and understand. — mjwills, Jul 25 '20 at 13:04
Entity is a faster interface MySqlConnection, Use in VS new DataSource and then select your database. Entity will create classes in c# to match the database tables. The low level interface is faster in Entity. — jdweng, Jul 25 '20 at 13:05
Do you mean Entity Framework @jdweng? Or https://dev.mysql.com/doc/dev/connector-net/6.10/html/N_MySql_Data_Entity.htm? Or something else? — mjwills, Jul 25 '20 at 13:06
I'd suggest using `using` blocks to make sure that everything (including `query`) is disposed correctly (and to allow you to remove your explicit `Close` calls). — mjwills, Jul 25 '20 at 13:07
How many rows are being returned? How long is it taking? Please show us the script to create the table (and its indexes). — mjwills, Jul 25 '20 at 13:08
`c.Cash = double.Parse(dr[2].ToString());` This is quite odd - taking what I presume is a number (from the database), converting it to a string then _back_ to a number again. — mjwills, Jul 25 '20 at 13:11
@jdweng - I don't know Entity and don't know how to write and make my code on Entity... — Djani_Bulgaria, Jul 25 '20 at 13:12
@mjwills - Can you give me example with using blocks how to make my code? Thank you! Aslo the table is created ones myself without code. Example if in program are working 10 users delay i 30sec-1min if only me delay is 1sec... So `c.Cash = double.Parse(dr[2].ToString());` how to make it? yes its number, but how to get it from table in other way? — Djani_Bulgaria, Jul 25 '20 at 13:13
The answer below shows how to use `using`. If you show us the scripts for the table we can show you how to avoid the need for `double.Parse`. If performance is scaling like that, I'd say there is a good chance you are missing appropriate indexes. I'd also suggest commenting out `query.Prepare();`. — mjwills, Jul 25 '20 at 13:19
@mjwills Thank you for your support! `public bool GetttingPlayers(ref clsConnection c, string Username) { if (Username != "") { return SQL.GettingPlayers(ref c); } else { return true; } }` — Djani_Bulgaria, Jul 25 '20 at 13:24
See https://docs.microsoft.com/en-us/ef/ Same as your link just more info. — jdweng, Jul 25 '20 at 14:15

score 0 · Answer 1 · answered Jul 25 '20 at 14:13

thanks to you guys at the moment i have no problem with the program. I Have last question is correctly wriited my updating class? I made it with using how you tell me.

 public void UpdateUser(clsConnection u)
    {
        using (MySqlConnection cn = new MySqlConnection(connectionMysql))
        {

            if (u.Username != "")
            {
                cn.Open();
                MySqlCommand query = new MySqlCommand(@"UPDATE users SET User_Name=@User_Name,User_Cash=@User_Cash,User_TotalHealth=@User_TotalHealth,User_Password=@User_Password,User_Status=@User_Status,User_IpAdress=@User_IpAdress" WHERE Username='" + Escape(u.Username) + "';", cn);
                if (query != null)
                {
                    query.Parameters.AddWithValue("@User_Name", Escape(u.Name));
                    query.Parameters.AddWithValue("@User_Cash", u.Cash);
                    query.Parameters.AddWithValue("@User_TotalHealth", u.TotalHealth);
                    query.Parameters.AddWithValue("@User_Password", Escape(u.Password));
                    query.Parameters.AddWithValue("@User_Status", u.Online);                   
 query.Parameters.AddWithValue("@User_IpAdress",Escape(u.IpAdress));
                    query.ExecuteNonQuery();
                    return;
                }
                else
                {
                    return;
                }
            }
        }

    }

Suggested reading: [AddWithValue is Evil](https://www.dbdelta.com/addwithvalue-is-evil/) — Ňɏssa Pøngjǣrdenlarp, Jul 25 '20 at 15:54
Don't use escape. Read my earlier links. Pass it in as a parameter, like you are for the other parameters. — mjwills, Jul 26 '20 at 01:37
@mjwills Hello, I made it `using (MySqlConnection baglan = new MySqlConnection(connectionMysql))` and opening connection after this `MySqlCommand query = new MySqlCommand(@"UPDATE users SET Username=@Username.....WHERE Username='" + Escape(u.Username) + "';", connect);` and in while `query.Parameters.Add("@Username", MySqlDbType.VarChar, 30).Value = u.PlyName;` and in while i add `query.ExecuteNonQuery(); return;` — Djani_Bulgaria, Jul 26 '20 at 05:28
`Escape(u.Username)` Don't do that. It must be something like `WHERE Username = @Username`. — mjwills, Jul 26 '20 at 05:36

vivek nuna · Accepted Answer · 2020-07-25T13:58:25.513

-2

You should avoid using the SELECT * in the query instead use the columns. I would suggest you use the stored procedure.

You are closing the connection twice it's unnecessary.

You should use the while loop in place of if statement.

YOu can refer to the below code, I have not tested it but it will give you a good idea.

using (MySqlConnection connect = new MySqlConnection(connectionMysql))
{
    connect.Open();
    using (MySqlCommand query = new MySqlCommand("SELECT * FROM Users Where Username='" + Escape(c.Username) + "'", connect))
    {
        using (MySqlDataReader dr = query.ExecuteReader())
        {
            while (dr.Read())
            {
                c.Username = dr.GetString(1);
            }
        }
    }                                 
}

edited Jul 25 '20 at 13:58

answered Jul 25 '20 at 13:13

vivek nuna

16,885
12
74
152

Please do not encourage SQL string concatenation. – mjwills Jul 25 '20 at 14:08
@mjwills I’m not encouraging him, I already told to use stored procedure and paar the parameters – vivek nuna Jul 25 '20 at 14:11

How to Correctly Get data from MySQL Table C#

2 Answers2