Inserting all records from DataTable into a remote database

Question

I have a DataTable with a few records. I want to insert all those records into a remote database. What would be the easiest way to do it? I read that most people iterate over the rows of the DataTable and insert record by record. I would like to make just 1 connection to the remote server and do a bulk insert. Is it possible? I am using C# and MySQL.

Answer 1

Although Kemal Taskin's answer is an elegant solution it is horrible on performance with a large DataTable.
I tried it with a 37500 record insert and it took over 15 minutes.
It seems to be inserting one record at a time.
I found that if I generate a MySQL insert statement string with 1000 records in it and loop over the data until its complete I have reduced my insert time down to 6 seconds. It's not BULK LOADING, its CHUNK LOADING. If anyone can come up with a better solution, please let me know.

    public void writeToDBTable(DataTable dt)     
    {

        MySqlConnection conn = new MySqlConnection(globalClass.connString);
        conn.Open();
        String sql = null;
        String sqlStart = "insert into MyTable (run_id, model_id, start_frame,water_year, state_id, obligateCover, DTWoodyCover, perennialGrowth, clonalCover) values ";

        Console.WriteLine("Write to DB - Start. Records to insert  = {0}", dt.Rows.Count);
        int x = 0;            

        foreach (DataRow row in dt.Rows)
        {
            x += 1;
                if (x == 1)
                {
                    sql = String.Format(@"({0},{1},{2},{3},{4},{5},{6},{7},{8})",
                                          row["runId"],
                                          row["modelId"],
                                          row["startFrame"],
                                          row["waterYear"],
                                          row["currentFrame"],
                                          row["obligateCover"],
                                          row["DTWoodyCover"],
                                          row["perennialGrowth"],
                                          row["clonalCover"]
                                          );
                }
                else
                {
                    sql = String.Format(sql + @",({0},{1},{2},{3},{4},{5},{6},{7},{8})",
                                          row["runId"],
                                          row["modelId"],
                                          row["startFrame"],
                                          row["waterYear"],
                                          row["currentFrame"],
                                          row["obligateCover"],
                                          row["DTWoodyCover"],
                                          row["perennialGrowth"],
                                          row["clonalCover"]
                                          );

                }

                if (x == 1000)
                {
                   try
                   {
                       sql = sqlStart + sql;
                       MySqlCommand cmd = new MySqlCommand(sql, conn);
                       cmd.ExecuteNonQuery();
                       Console.WriteLine("Write {0}", x);
                       x = 0;
                   }
                   catch (Exception ex)
                    {
                        Console.WriteLine(sql);
                        Console.WriteLine(ex.ToString());
                    }
                }

        }
        // get any straglers
        if (x > 0)
        {
            try
            {
                sql = sqlStart + sql;
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                Console.WriteLine("Write {0}", x);
                x = 0;
            }
            catch (Exception ex)
            {
                Console.WriteLine(sql);
                Console.WriteLine(ex.ToString());
            }

        }

        conn.Close();
        Console.WriteLine("Write to DB - End.");
    }

Answer 2

I don't know whether this answer is too late or not :)

You can do something like this:

// assume you have a table with one column;
string commandText = "insert into t_test1 (myid) values (@tempid)";

using (MySqlConnection cn = new MySqlConnection(myConnectionString))
{
    cn.Open();

    using (MySqlCommand cmd = new MySqlCommand(commandText, cn))
    {
        cmd.UpdatedRowSource = UpdateRowSource.None;

        cmd.Parameters.Add("?tempid", MySqlDbType.UInt32).SourceColumn = "tempid";

        MySqlDataAdapter da = new MySqlDataAdapter();
        da.InsertCommand = cmd;
        // assume DataTable dt contains one column with name "tempid"
        int records = da.Update(dt);
    }

    cn.Close();
}

Answer 3

For Kemal Taşkın solution, RowState set must be equal to DataRowState.Added. If it is not the case do this :

foreach (DataRow row in dt.Rows)
    row.SetAdded();

For Mr.Black, it is recommended to use sql parameter and not use data value directly.

When importing data into InnoDB, turn off autocommit mode, because it performs a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET autocommit and COMMIT statements:

SET autocommit=0;
... SQL import statements ...
COMMIT;

Performance test : insertion of 5400 rows in 2 tables

• insertion from CSV file : 3 seconds

  LOAD DATA INFILE 'data.csv' INTO TABLE myTable TERMINATED BY '\t';

• insertion by using Kemal Taşkın solution: 32 seconds

  MySqlDataAdapter.Update (DataTable)

• insertion row by row (): 41 seconds

  INSERT INTO table (columns) VALUES (values);

  INSERT INTO table (columns) VALUES (values);

  ...

• insertion all rows in one query: 143 seconds

  INSERT INTO table (columns) VALUES (values), (values), ...;

=> LOAD DATA is the most performant by far !

You can check also this article : https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html