Looping a DataTable twice

Question

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 ?

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.

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.

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 (you don't really need to know about SSIS) -

Answer 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];

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();
}

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.