Set Values Of DataTable Column Without Calling DataRow.set_Item

Question

0.00/5 (No votes)

See more:

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:

Posted 9-Sep-10 5:09am

jabit

Updated 9-Sep-10 6:54am

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2010-09-09T05:39:00

Solution 1

Well, you can't avoid the loop. It's got to be done somewhere.

For something like this (I'm not a SharePoint guy) if the data is all on an SQL Server, I'd probably have the SQL Server do the work in a stored procedure since it's better optimized to handle large data sets.

Posted 9-Sep-10 5:39am

Dave Kreskowiak

Comments

jabit 9-Sep-10 12:52pm

I will change my question, I was hoping I could do some kind of join in code or just replace an entire datacolumn but it looks like you're right, looping is needed. However I want to do it without calling DataRow.set_Item for every item since that is where the time is taken. Microsoft doesn't support and recommends against accessing the SharePoint database directly, only through the API.

Something I'm playing around with now is serializing the sharepoint data into XML in memory, performing all of the swaps against XmlNode's and then deserializing it back into a table, so far it looks promising!

Dave Kreskowiak 9-Sep-10 19:15pm

Any time ANY value in a DataSet/DataTable is changed, a call to set_Item is made. You can't avoid it. Even if you didn't loop over the set yourself and had a SELECT query that did it, it still has to loop over the set itself, even if you didn't explicitly write one.

jabit 15-Sep-10 18:19pm

NOT true, I solved it, finally! I serialized the data into XDocument's in memory and performed the swaps using XElement.Add() to set the missing cell values. There's a tiny bit of extra overhead converting the DataTable to XML and then back but overall my code is now nearly 30% faster!

jabit · Answer 2 · 2010-09-15T12:20:00

Solution 2

I finally solved this problem myself. I serialized the data into XDocument's in memory and performed the swaps using XElement.Add() to set the missing cell values. There's a tiny bit of extra overhead converting the DataTable to XML and then back but overall my code is now nearly 30% faster!

Posted 15-Sep-10 12:20pm

jabit

Comments

Dave Kreskowiak 15-Sep-10 18:50pm

Nice, but like I said, the loop has to be done somewhere. It's being executed behind the scenes inside the code that's doing the conversion.

jabit 15-Sep-10 19:59pm

Yes, the point wasn't to avoid the loop, just to avoid the set_Item call for every iteration of the loop. DataRow.set_Item takes an average of 30ms whereas setting it in xml takes only nanoseconds.