Most efficient way to insert Rows into MySQL Database

Question

I've read a lot of questions about that but i couldn't find one that is fast enough. I think there are better ways to insert a lot of rows into a MySQL Database

I use the following code to insert 100k into my MySQL-Database:

public static void CSVToMySQL() {     string ConnectionString = "server=192.168.1xxx";     string Command = "INSERT INTO User (FirstName, LastName ) VALUES (@FirstName, @LastName);";     using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))     {         mConnection.Open();          for(int i =0;i< 100000;i++) //inserting 100k items         using (MySqlCommand myCmd = new MySqlCommand(Command, mConnection))         {             myCmd.CommandType = CommandType.Text;             myCmd.Parameters.AddWithValue("@FirstName", "test");             myCmd.Parameters.AddWithValue("@LastName", "test");             myCmd.ExecuteNonQuery();         }     } } 

This takes for 100k rows about 40 seconds. How can i make this faster or a little more efficient?

Might be faster to insert multiple rows via a DataTable/DataAdapter or at once:

INSERT INTO User (Fn, Ln) VALUES (@Fn1, @Ln1), (@Fn2, @Ln2)... 

Due to security issues i can't load the data into a file and MySQLBulkLoad it.

Answer 1

Here is my "multiple inserts"-code.

The insertion of 100k rows took instead of 40 seconds only 3 seconds!!

public static void BulkToMySQL() {     string ConnectionString = "server=192.168.1xxx";     StringBuilder sCommand = new StringBuilder("INSERT INTO User (FirstName, LastName) VALUES ");                using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))     {         List<string> Rows = new List<string>();         for (int i = 0; i < 100000; i++)         {             Rows.Add(string.Format("('{0}','{1}')", MySqlHelper.EscapeString("test"), MySqlHelper.EscapeString("test")));         }         sCommand.Append(string.Join(",", Rows));         sCommand.Append(";");         mConnection.Open();         using (MySqlCommand myCmd = new MySqlCommand(sCommand.ToString(), mConnection))         {             myCmd.CommandType = CommandType.Text;             myCmd.ExecuteNonQuery();         }     } } 

The created SQL-statement looks like this:

INSERT INTO User (FirstName, LastName) VALUES ('test','test'),('test','test'),... ; 

Update: Thanks Salman A I added MySQLHelper.EscapeString to avoid code injection which is internally used when you use parameters.