MySqlConnector C# INSERT large number of rows

Question

I have data, about 200,000 rows that need to be written to the database. I wrote this code, but it takes too long to write to the database. Is it possible to insert multiple Values in one request cmd.ExecuteNonQuery(); via Parameters? Or some other way?

string sqlcommand = "INSERT INTO `Mail` ( `threadID`, `mailID`, `UserID`, `UserName`, `mailTime`, `body`) values (@threadID, @mailID, @UserID, @UserName, @mailTime, @body)";
                using (MySqlConnection mySqlConnection2 = new MySqlConnection(sqlconnect))
                {
                    mySqlConnection2.Open();
                    MySqlCommand cmd = mySqlConnection2.CreateCommand();
                    cmd.CommandText = sqlcommand;

                    MySqlParameter threadID = new MySqlParameter("@threadID", MySqlDbType.Int32);
                    MySqlParameter mailID = new MySqlParameter("@mailID", MySqlDbType.Int32);
                    MySqlParameter UserID = new MySqlParameter("@UserID", MySqlDbType.Int32);
                    MySqlParameter UserName = new MySqlParameter("@UserName", MySqlDbType.VarChar);
                    MySqlParameter mailTime = new MySqlParameter("@mailTime", MySqlDbType.DateTime);
                    MySqlParameter body = new MySqlParameter("@body", MySqlDbType.Text);
                    cmd.Parameters.Add(threadID);
                    cmd.Parameters.Add(mailID);
                    cmd.Parameters.Add(UserID);
                    cmd.Parameters.Add(UserName);
                    cmd.Parameters.Add(mailTime);
                    cmd.Parameters.Add(body);

                    foreach (Mail_Data data in List)
                    {
                        threadID.Value = data.threadID;
                        mailID.Value = data.mailID;
                        UserID.Value = data.UserID;
                        UserName.Value = data.UserName;
                        mailTime.Value = data.mailTime;
                        body.Value = data.body;
                        cmd.ExecuteNonQuery();

                    }
                }

UPD: MySqlBulkLoader is not very suitable, since the data must be taken from a file, but for me it is taken from the server. But the main problem is that this data already contains ,, \r\n, different characters and words in different languages, so it will be problematic to read them from the file and the format will be incorrect and errors will occur.

@RezaJaferi That's for MS SQL Server, not MySQL. – Dai Aug 08 '22 at 21:50 — Dai, Aug 08 '22 at 21:50

score 0 · Answer 1 · answered Aug 08 '22 at 21:50

0

Yes there is an easy way to insert multiple rows try the MySqlBulkLoader. There is a good example in the official documentation. Or you could look at this question for example.

answered Aug 08 '22 at 21:50

Johannes Krackowizer

621
4
16

MySqlBulkLoader is not very suitable, since the data must be taken from a file, but for me it is taken from the server. But the main problem is that this data already contains `,` `\r\n`, different characters and words in different languages, so it will be problematic to read them from the file and the format will be incorrect and errors will occur. – Gaben Aug 08 '22 at 22:34
@Gaben No, it won't be "problematic". – Dai Aug 08 '22 at 22:42
Please post a new question for that issue. – Dai Aug 08 '22 at 23:16
@Dai I tried to write data to cvs file. And as I said, the data was recorded, but loaded into the database incorrectly. – Gaben Aug 08 '22 at 23:16
@Gaben "for me it is taken from the server". If the data is already in the MySQL Server, say, in a different table, take a look at the [`INSERT INTO ... SELECT` statement](https://dev.mysql.com/doc/refman/8.0/en/insert-select.html). – Bradley Grainger Aug 09 '22 at 05:41
@Gaben You may also find [`MySqlBulkCopy`](https://mysqlconnector.net/api/mysqlconnector/mysqlbulkcopytype/) useful. – Bradley Grainger Aug 09 '22 at 05:42
@Gaben perhaps you should use differenct `FieldTerminator` and `LineTerminator` if they clash with your content or try to use escape strings so your data won't break your "CSV" data. For more details read through the official documentation https://dev.mysql.com/doc/connector-net/en/connector-net-programming-bulk-loader.html – Johannes Krackowizer Aug 09 '22 at 21:35

MySqlConnector C# INSERT large number of rows

1 Answers1

Linked