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.

Improve this question
5
  • 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. Commented Mar 23, 2019 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 Commented Mar 23, 2019 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. Commented Mar 23, 2019 at 22:06
  • Are they really dots in between values and in between the two sets of values? Commented Mar 24, 2019 at 1: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 Commented Mar 24, 2019 at 2:07

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

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
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
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 + ";";
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.
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.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.