C# Adding Rows to a Datatable Inside a Foreach Loop

C# Adding rows to a DataTable inside a foreach loop

You need to declare datatable and add data columns only once outside foreach loop.

       //Prepare Datatable and Add All Columns Here
        dataTable = new DataTable();
        column = new DataColumn();
        column.DataType = System.Type.GetType("System.Int32");
        column.ColumnName = "Machine Number";
        column.ReadOnly = false;
        column.Unique = true;
        column.AutoIncrement = false;

   //Excel Input and Dex File Data Marriage
    foreach (string Line in ExcelLines)
    {
     //Add new row and assign values to columns, no need to add columns again and again in loop which will throw exception
     row = dataTable.NewRow();

     //Map all the values in the columns
     row["ColumnName"]= value;

     //At the end just add that row in datatable
     dataTable.Rows.Add(row );

      }

Updating a row in a datatable during a foreach C#

as an alternative, using a traditional for loop over a foreach would mean not doing any data copying or anything and would likely run though the datatable faster

for(int i=0;i<table.Rows.Count;i++)
{ 
   if (table.Rows[i]["seqlinnum"].ToString() == "0")
   {
     table.Rows[i]["seqlinnum"]=maxSequenceNumber; maxSequenceNumber++;
   }
}

How to add rows and columns into datatable in single loop?

I am guessing I am missing something here. This looks like a transpose function and I cannot think of a way to accomplish this without two loops or transposing the data as you read it in. But going from what is posted it appears the column label holds the new DataTable’s column names. The first column is the first row of data to this new DataTable.

If this is the case then while you are looping through the rows to get the column names from column 1 (label), you can also get the “value’ from column 0 (value) and put this value in a List<string> named valuesList below.

Then after you have looped through all the rows and set the columns in the new DataTable dtResults you can add a single row from the valuesList by setting the list to a string array like below. This will produce the second picture you showed in one loop. Again I am guessing there is more to it than this simple transpose. Since a DataTable does not have a built in transpose function you will have to write your own. Not sure how you would do this in one loop though. Hope this helps.

private DataTable Transpose2ColDT(DataTable dtSource) {
  string prefix = "DIAP_";
  string colName = "";
  DataTable dtResult = new DataTable();
  List<string> valuesList = new List<String>();
  if (dtSource.Rows.Count > 0) {
    foreach (DataRow dr in dtSource.Rows) {
      if (!dr.IsNull("Label")) {
        if (dr.ItemArray[1].ToString() != "" ) {
          colName = prefix + "_" + dr.ItemArray[1].ToString();
          if (!dtResult.Columns.Contains(colName)) {
            dtResult.Columns.Add(colName, typeof(string));
            valuesList.Add(dr.ItemArray[0].ToString());
          }
        }
      }
    }
    dtResult.Rows.Add(valuesList.ToArray<string>());
  } // no rows in the original source
  return dtResult;
}

Looping through a class and adding each row to a DataTable only adds one row

Move the return out of the loop.

PayrollItem[] it = GetPayrollItems(xmlResult);

    foreach (var inv in it)
    {
        DataRow row = ResultsTable.NewRow();
        row["EmployeeKey"] = inv.BadgeNo;
        row["StoreKey"] = inv.CostCentre;
        row["EmployeeName"] = inv.EmpName;
        row["DateKey"] = inv.Date;
        row["Incheck"] = inv.In;
        row["Outcheck"] = inv.Out;
        System.Diagnostics.Debug.WriteLine("inv.BadgeNo {0} i StoreKey {1}", inv.EmpName, inv.CostCentre);
        ResultsTable.Rows.Add(row);

    }

return ResultsTable;

Looping through data table to get value

In a comment you mentioned that you get the error:

cannot apply indexing with [] to an expression of type object

when trying to access item["tag"] in the foreach loop.

You need to explicitly declare the DataRow in the foreach.

// declare DataRow here, not var 
foreach (DataRow item in table.Rows)
{
    // use item here
    Value = item["tag"].ToString();    // use += to concatenate string
}

The reason is that the DataRowCollection implements a non-generic IEnumerable so you index an object instead of DataRow. The solution above casts to a DataRow.

I would recommend looking at the Field<T>() and AsEnumerable() methods from System.Data.DataSetExtensions. AsEnumerable() returns an IEnumerable<DataRow>. Field() provides strongly typed access to the values (ie it casts/converts the types for you).

Then you can do:

foreach (var item in table.AsEnumerable())
{
    // item is a DataRow here
    var myString = item.Field<string>("tag");     // gets string

    // you can also do
    var myInt = item.Field<int>("Id");            // gets int
    var myDate = item.Field<DateTime?>("Date");   // gets nullable DateTime?
    var myValue = item.Field<decimal>("Price");   // gets decimal
}

---

Related Topics