Most efficient way to insert Rows into MySQL Database

by

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.

More Related Contents:

  1. Where is likely the performance bug here? [closed]
  2. Best way to reverse a string
  3. String vs. StringBuilder
  4. Implementing a log viewer with WPF
  5. How to get object size in memory? [duplicate]
  6. Does C# optimize the concatenation of string literals?
  7. Exception: There is already an open DataReader associated with this Connection which must be closed first
  8. Debug vs. Release performance
  9. Is it possible to use a MySql User Defined Variable in a .NET MySqlCommand?
  10. LINQ: Not Any vs All Don’t
  11. .NET Core 2.1 Identity get all users with their associated roles
  12. Why does the Contains() operator degrade Entity Framework’s performance so dramatically?
  13. Detecting network connection speed and bandwidth usage in C#
  14. Using Prepared Statement in C# with Mysql
  15. Why is LINQ .Where(predicate).First() faster than .First(predicate)?
  16. Error-Attempt by method ‘X.set_DbConnection(System.Data.Common.DbConnection)’ to access method ‘Y.get_Settings()’ failed
  17. How does extern work in C#?
  18. How to force a number to be in a range in C#? [duplicate]
  19. MySql Last Insert ID, Connector .net
  20. MySQL C# Text Encoding Problems
  21. Is String.Format as efficient as StringBuilder
  22. Authentication with old password no longer supported, use 4.1 style passwords
  23. Why is this F# code so slow?
  24. Warm-up when calling methods in C#
  25. How slow is Reflection
  26. When to change the Generate Serialization Assembly value?
  27. Winforms: SuspendLayout/ResumeLayout is not enough?
  28. Heap versus Stack allocation implications (.NET)
  29. MySQL C# async methods doesn’t work?
  30. C# Dynamic Keyword — Run-time penalty?

Leave a Comment