循环一个DataTable两次 [英] Looping a DataTable twice
问题描述
我使用C＃和.NET 3.5在Visual Studio 2008，我想遍历存储在数据表中两次结果集。我用下面的脚本和循环一次成功。当我环路它第二次，我得到一个异常（如下）。我该如何解决这个问题？ 从我的Java知识，我怀疑它是用一个行指针到达最后一个位置。正确与否？
I am using c# and .NET 3.5 in visual studio 2008. I wanted to loop over a result set stored in a data table twice. I used the following script and looped once successfully. When I loop it for the second time, i get an exception (given below). How do I fix this ? From my Java knowledge, I suspect it has something to with a row pointer reaching the last position. Correct or not ?
本C＃脚本嵌入到SSIS。不要担心在SSIS。我是pretty的肯定，问题只涉及到C＃。请不要为此添加一个SSIS标签。
This C# script is embedded inside SSIS. Don't worry about the SSIS. I am pretty sure that the problem is only related to C#. Please don't add an SSIS tag for this.
有关code笔记 - 一个数据库表只有一个名为OneColumn VARCHAR列。它有 3行 - 一，二和三个。我选择此列，并将其保存到名为结果集的对象变量。我的C＃脚本应该遍历行，加入他们，并告诉他们作为一个字符串。
Notes about the code - A database table has only one varchar column named OneColumn. It has 3 rows - One, two and three. I select this column and save it to an Object variable called "ResultSet". My C# script is supposed to iterate over the rows, join them and show them as a single string.
后，即重复脚本。虽然你可能不需要它，我已经添加了SSIS图也。
After, that repeat the script. Although you might not need it, I have added the SSIS diagram also.
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Xml;
using System.Data.OleDb;
namespace ST_bde893ffaa5d49efb7aed9d752cb900c.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
public void Main()
{
OleDbDataAdapter oleDA = new OleDbDataAdapter();
DataTable dt = new DataTable();
DataColumn col1 = null;
DataRow row = null;
string strCols = "";
oleDA.Fill(dt, Dts.Variables["ResultSet"].Value);
col1 = dt.Columns["OneColumn"];
int lastRow = dt.Rows.Count - 1;
for (int i = 0; i <= lastRow - 1; i++)
{
row = dt.Rows[i];
strCols = strCols + row[col1.Ordinal].ToString() + ", ";
}
row = dt.Rows[lastRow];
strCols = strCols + row[col1.Ordinal].ToString();
MessageBox.Show(strCols);
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
错误 -
Error -
Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IndexOutOfRangeException: There is no row at position -1.
at System.Data.RBTree`1.GetNodeByIndex(Int32 userIndex)
at System.Data.RBTree`1.get_Item(Int32 index)
at System.Data.DataRowCollection.get_Item(Int32 index)
at ST_bde893ffaa5d49efb7aed9d752cb900c.csproj.ScriptMain.Main()
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
EXTRA（你并不真的需要了解SSIS） -
推荐答案
最明显的地方-1指数可以从现身的这两条线的组合：
The most obvious place a -1 index could be coming from is the combination of these two lines:
int lastRow = dt.Rows.Count - 1;
// followed by
row = dt.Rows[lastRow];
在 dt.Rows.Count == 0 ，这将有试图访问的效果 dt.Rows [-1] ，它告诉我，你的code运行时， dt.Rows 是没有得到填充的第二次。您可以通过包装两个相关线在if语句解决眼前的错误，因为在
When dt.Rows.Count == 0, this would have the effect of trying to access dt.Rows[-1], which tells me that the second time your code runs, dt.Rows is not getting populated. You can fix the immediate error by wrapping the two relevant lines in an if statement, as in
if (lastRow >= 0) {
row = dt.Rows[lastRow];
strCols = strCols + row[col1.Ordinal].ToString();
}
至于为什么你没有得到任何行摆在首位？这很可能是一个光标停留在最后，你最初的预期。下一步我想借此排除故障是要弄清楚什么实际的运行时类型是 Dts.Variables [的ResultSet。值，然后看相关文件中来看看如何后退。
As to why you're not getting any rows in the first place? That is likely to be a cursor stuck at the end as you originally expected. The next step I would take in troubleshooting is to figure out what the actual runtime type is of Dts.Variables["ResultSet"].Value, and then look in the relevant documentation to see how to rewind.
这篇关于循环一个DataTable两次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
