在不调用DataRow.set_Item的情况下设置DataTable列的值 [英] Set Values Of DataTable Column Without Calling DataRow.set_Item

问题描述

情况:
我有一个数据表，其中充满了共享点列表中的数据.该表具有一个ID列，该列是我在外部LOB数据库中拥有的表的PK.在共享点表中，我添加了第二列以包含来自LOB数据表的数据.我需要做的是从共享点表中获取PK，使用它从LOB数据表中获取一行，从LOB数据行中获取一个不同的值，并将其存储在共享点表中该行的空列中. >
我现在在做什么:
-从LOB数据库获取数据表
-遍历sharepoint数据表的每一行
-对于sharepoint数据表的每一行，获取PK值
-将LOB数据表的默认视图过滤到该PK
-获取LOB数据表中第二列的值，并将其存储为共享点行中空白列的值

这种方法有效，但是要用超过5秒钟的时间来填充具有6,000行的表格，而且随着每天添加项目的增加，情况只会变得更糟.我对代码进行了跟踪，发现它为我填充的2个空列运行DataRow.set_Item超过6200次，每个set_Item平均大约30ms.更糟糕的是，每次SPGridView过滤器或页面或任何其他内容时，此代码都会运行，因此5秒钟是很宝贵的！

我现在要尝试的是将共享点数据表序列化为内存中的XML，并在XmlNodes中执行交换，然后将其反序列化为数据表.你觉得呢?

所以问题是，如何设置这些空白列的值而不循环遍历每一行并为每个空白单元格调用DataRow.set_Item?
:confused :: confused:

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:

推荐答案

好吧，您无法避免循环.必须在某个地方完成.

对于这样的事情(我不是SharePoint人士)，如果数据全部在SQL Server上，则我可能希望SQL Server在存储过程中完成工作，因为最好对其进行优化以处理较大的数据.数据集.

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.

我终于解决了这个问题.我将数据序列化到内存中的XDocument并使用XElement.Add()进行交换以设置缺少的单元格值.将DataTable转换为XML然后再返回时，会有一点额外的开销，但是总体来说，我的代码现在快了将近30％！

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!

这篇关于在不调用DataRow.set_Item的情况下设置DataTable列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！