The situation:
I've got a datatable filled with data from a sharepoint list. That table has an ID column which is the PK of a table that I have in an external LOB database. In the sharepoint table I've added a second column to contain data from the LOB datatable. What I need to is take the PK from the sharepoint table, use that to get a row from the LOB datatable, get a different value from the LOB datarow and store it in the empty column of the row in the sharepoint table.
What I'm doing now:
- Get the datatable from the LOB database
- Loop through each row of the sharepoint datatable
- For each row of the sharepoint datatable get the PK value
- Filter the defaultview of the LOB datatable to that PK
- Get the value of a second column in the LOB datatable and store that as the value of the blank column in the sharepoint row
This works but it takes over 5 seconds to fill a table with over 6,000 rows and it's only going to get worse as items are added every day. I ran a trace on the code and found that it runs DataRow.set_Item over 6,200 times for the 2 empty columns that I'm filling with an average of around 30ms per set_Item. What's worse is that this code runs every time the SPGridView filters or pages or anything so 5 seconds is precious!
What I'm trying now is to serialize the sharepoint datatable into XML in memory and perform the swaps in XmlNodes, then deserializing it back into a datatable. What do you think?
So the question is, how to I set the values of these blank columns without looping through each row and calling DataRow.set_Item for every blank cell?
:confused::confused: