I am trying to encrypt a text field in an existing database using ADO.NET Command object that feeds parameters to a stored proc server-side. Code below.
NOTE: a command has already fetched the PK and the particular column that needs to be encrypted for all relevant rows in the table, and client-side that dataset is a System.Data.DataTable T. Inside a tight loop, the column's plainText is obtained, encrypted, and then the id and the encrypted values are provided as command parameters to MyUpdateCommand, the command that invokes a stored procedure server side, which updates the row with the encrypted value:
foreach (DataRow row in T.Rows)
{
int id = (int)row["id"];
string plainText = row["note"].ToString();
string encrypted = MyEncrypt(plainText, aeskey);
MyUpdateCommand.Parameters["@id"].Value = id;
MyUpdateCommand.Parameters["@encryptedanote"].Value = encrypted;
MyUpdateCommand.ExecuteNonQuery();
}
I'm doing this in batches of 100,000 records. After about 10,000 records, an exception is thrown (The CLR has been unable to transition from COM context […] for 60 seconds) which says the process is not pumping windows messages, which
... has a negative performance impact and may even lead to the application becoming unresponsive or memory usage accumulating 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.
How do I retrofit my loop with pumping wait primitives?
