0

I have a big updated list of strings which must be uploaded with update of each row from 0 to last index by rewriting of exist records and adding of new rows to MySql database on remote server each time user calls function.

The adding of data string by string takes a lot of time even if not hangs by process:

 foreach (string str in myList)
 {
     string Query = "insert into tab(a) values(@a);";
     MySqlConnection conn = new MySqlConnection(connString);
     MySqlCommand conn_ = new MySqlCommand(Query, conn);
     conn.Open();
     conn_.ExecuteNonQuery();
     conn.Close();      
}

My goal is to figure out, what should be most proper way to do this fast. Maybe I should create and update table locally and then somehow upload it to database.

I have a List<string> myList = new List<string>(); which contains about 5000 rows and I have table in database on remote server:

id |  user   | nickname
_____________________
0  |  record | record
1  |  ...    | ...

My desired result is to update all records from 0 to highest index with adding of new records and removing of extra records in case if current upload contains less records then previous each time from 0 index, of course no maters if index will come with gap between removed rows.

  • `Rows.Add(string.Format("('{0}','{1}')", MySqlHelper.EscapeString(str)); ` This line has issues. You need two values on the right side to fill `{0}` and `{1}`. To test, change it to `Rows.Add(string.Format("('{0}','{1}')", MySqlHelper.EscapeString(str),MySqlHelper.EscapeString(str));` to see if your code works. – Allen King Mar 23 '19 at 18:02
  • @Allen King Hello, sorry for this, edited. I've simplified example with single value and forgot add it back, as I've already answered to Christian Gollhardt below, it was missed only in this example. It is 2 columns `user` and `nickname` and it is not the reason of exception, also solution does not contains update method described above. So, I'm not sure about this –  Mar 23 '19 at 22:00
  • You will need to do `sCommand.toString()` to analyze the SQL. Copy and paste the generated SQL in a mySQL editor such as SQLyog and it will highlight where there issue is in SQL. – Allen King Mar 23 '19 at 22:06
  • Are they really dots in between values and in between the two sets of values? – Allen King Mar 24 '19 at 01:53
  • @Allen King Sorry, mistake in description. `sCommand.ToString()` looks like this: INSERT INTO tab (user, nickname) VALUES('tom','xiu787?. tom45. tomtom?. ttt000.'),('sally','sally90. sal90?. sal900. sallysally90?. sallyo90.') this example shows only 2 rows, each one contains user and username(s) data included inside parentheses, separated by apostrophes and comma between user and usernames and separate rows –  Mar 24 '19 at 02:07

1 Answers1

2

You claim:

Adding of data to MySql database on remote server

Which implies, you have multiple clients who know the connection string to the remote database. This is a security desaster! Stop even thinking about it! Also, what happens if the connection string to the database changes? You need to update every client. The only exception would be, if you are in an trusted environment with trusted connections, but I suspect this, since you are using MySQL.

To your actual problem:

Your main problem is, for every item in your loop you create an connection, send something to the server and close your connection. And again, and again. Basicly you want to send on big command to the server, instead of multiple created by your loop (SQL can handle multiple insert statements at one SQL Command).

The better (more secure way):

Create an Application for your server, which accepts myList as JSON for example and save it there. Probably you need to handle authorization here.

Your Client sends a Save Request with a myList to the Application, I have mentioned above.

We have some Technologies for it:

Warning: Also, from a first look, you seem to have a problem with SQL Injections. See what they are, and how you can prevent it.

Christian Gollhardt
  • 16,510
  • 17
  • 74
  • 111
  • Hello, please check my post, I have edited question with another attempt, in some sense according to you advice about multiple insert statements and SQL Injections, but still not quite clear, works fast, but with exception, and I'm not sure what should be update instead insert in this case –  Mar 23 '19 at 04:01
  • 1
    What's your `sCommand.ToString()`? Try running it in MySQL Workbench, does it give you a more detailed error? This error sounds like, your generated SQL Command has some Errors @sam324 – Christian Gollhardt Mar 23 '19 at 04:04
  • It is 2 column names `user` and `nickname` of 3 columns tab `id | user | nickname`: `StringBuilder sCommand = new StringBuilder("INSERT INTO tab (user, nickname) VALUES");`. Also, I'm not sure, if I will get same speed in case of update from 0-to highest index: `"update tab set user='" + user + "'and nickname='" + nickname + "' where id='" + i + ";";` –  Mar 23 '19 at 13:09
  • Yeah, and finaly you call `ToString()` on your string builder. That's the value which is wrong, but interessting. Probably already a self made injection. – Christian Gollhardt Mar 23 '19 at 14:01
  • I've edited question with more clear explanation of what I'm trying to do, please check. I'm still trying to find solution with using of MySql. –  Mar 23 '19 at 14:08
  • You still doesn't show your SQL String @sam324 I am not interessted in the `c#` code, I am interessted in the `SQL Command String` you send to the database – Christian Gollhardt Mar 23 '19 at 15:27
  • if I get you right, in example above it is `StringBuilder sCommand = new StringBuilder("INSERT INTO tab (user, nickname) VALUES"); StringBuilder sCommand = new StringBuilder(sqlstring);`, where value is a `str ` just strings, but I'm trying to update `string sqlstring = "UPDATE tab set (user, nickname) FROM user_1 ORDER BY ID";`, seems like this statement is also incorrect and not suitable for this solution –  Mar 23 '19 at 15:50
  • Look, [this is a SQL string](https://stackoverflow.com/a/6889087/2441442), what you show is `C#` code. As I wrote erlier, the result of `sCommand.ToString()` is interessting. – Christian Gollhardt Mar 23 '19 at 15:51
  • SQL string with 2 rows for columns, each one contains user and username(s) inside parentheses and separated by apostrophes and comma between user and usernames strings and separate strings, here is outputted `sCommand.ToString()`: `INSERT INTO tab (user, nickname) VALUES('tom','xiu787?. tom45. tomtom?. ttt000.'),('sally','sally90. sal90?. sal900. sallysally90?. sallyo90.')` –  Mar 24 '19 at 02:09