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.
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.
Just do a replace on apostrophes in "test" with a double apostrophe, and you'll be fine. "test" ==> "test".Replace("'", "''")
You might want to use
MySQLHelper.EscapeString
on"test"
.@fubo Should be. I am guessing it uses same or similar code that escapes parameter values.
Now you create a pretty long string in memory.
Is there a way to parameterize this and get the same results? Some people on my team are telling me that this approach is invalid because it leaves you open to SQL injection. How would you protect this statement from injection? I need to be able to do a multi insert for performance reasons.