0

I have an error message that occurs when I execute a loop in C#;

The CLR has been unable to transition from COM context 0x1201050 to COM context 0x1201108 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations.

I know what is causing the issue, it is a for loop that is adding 8000+ people into a table in MySQL. Even though I know the loop is the problem, I've never come across this before in C# and I am unsure how to handle it. Here is the loop that is causing the issue;

    public void WriteToMySQL(DataTable dTable)
    {
        int id;
        string firstName, middleName, lastName, formalName, title, company;

        for (int i = 0; i < 8094; i++)
        {
            id = Convert.ToInt32(dTable.Rows[i][0]);
            firstName = dTable.Rows[i][1].ToString();
            middleName = dTable.Rows[i][2].ToString();
            lastName = dTable.Rows[i][3].ToString();
            formalName = dTable.Rows[i][4].ToString();
            title = dTable.Rows[i][5].ToString();
            company = dTable.Rows[i][6].ToString();

            using (MySqlConnection mysqlCon = new MySqlConnection(mysqlConstr))
            {
                mysqlCon.Open();
                var mysqlCmd = new MySqlCommand("INSERT INTO sdcdatabase.people VALUES(@id, @firstname, @middlename, @lastname, @formalname, @title, @company)", mysqlCon);
                mysqlCmd.Parameters.AddWithValue("@id", id);
                mysqlCmd.Parameters.AddWithValue("@firstname", CharactersOnly(firstName));
                mysqlCmd.Parameters.AddWithValue("@middlename", CharactersOnly(middleName));
                mysqlCmd.Parameters.AddWithValue("@lastname", CharactersOnly(lastName));
                mysqlCmd.Parameters.AddWithValue("@formalname", CharactersOnly(formalName));
                mysqlCmd.Parameters.AddWithValue("@title", CharactersOnly(title));
                mysqlCmd.Parameters.AddWithValue("@company", CharactersOnly(company));
                mysqlCmd.ExecuteNonQuery();
            }
        }
    }

CharactersOnly simply removes anything that is not a character from the string. I understand and probably know there are better ways to do this, however I haven't got my head round a more efficient way just yet. Is there a way of correcting the loop that doesn't cause C# to complain after 60 seconds?

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
CBreeze
  • 2,925
  • 4
  • 38
  • 93
  • Wow, that's over 8,000 round trips to the server in order to insert a batch of records. You should consider re-architecting your solution to create a record set, then insert the record set all at once. It will likely fix your problem and significantly improve performance at the same time. – DeadZone Nov 03 '15 at 12:11
  • This error is shown only during debug. You can disable it if you want. http://forums.asp.net/p/1493688/3517149.aspx You can improve performance anyway http://stackoverflow.com/questions/30615443/bulk-copy-a-datatable-into-mysql-similar-to-system-data-sqlclient-sqlbulkcopy – Tim Schmelter Nov 03 '15 at 12:13
  • did you also try to use mysqlCon.Close(); inside the using? I'm not 100% if the connection is closed correctly when the use ends. Furthermore it could be that the connection survives for some time and thus causes sort of an ovelroad (in other languages that CAN happen not sure in C#). Die you try already to put the using and open outside the loop, so that the loop is nested inside the using instead of the other way round?) – Thomas Nov 03 '15 at 12:22

0 Answers0