How to Iterate Through a Datatable

How to iterate through a DataTable

DataTable dt = new DataTable();

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

adapter.Fill(dt);

foreach(DataRow row in dt.Rows)
{
    TextBox1.Text = row["ImagePath"].ToString();
}

...assumes the connection is open and the command is set up properly. I also didn't check the syntax, but it should give you the idea.

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
}

How to iterate through a DataTable

DataTable dt = new DataTable();

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

adapter.Fill(dt);

foreach(DataRow row in dt.Rows)
{
    TextBox1.Text = row["ImagePath"].ToString();
}

...assumes the connection is open and the command is set up properly. I also didn't check the syntax, but it should give you the idea.

C# Iterate through rows in a DataTable

Looping through the rows in your client program is exactly what you don't want to do. Let the database do that work for you. Try this:

private void BtnDelete_Click(object sender, EventArgs e)// Sends to ConfirmDeleteEMP Form 
{     
    object result;       
    string query = "SELECT PayrollNo FROM [Employee] WHERE FirstName + ' ' + LastName = ?"; 
    string connstring = @"Provider = Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\\HoliPlanData.accdb;Persist Security Info=False";

    using (OleDbConnection conn = new OleDbConnection(connstring))
    using (OleDbCommand cmd = new OleDbCommand(query, conn))
    {
        //guessing at type and length here
        cmd.Parameters.Add("?", OleDbType.VarWChar, 50).Value = DropBoxEmp.Text;

        conn.Open();
        result = cmd.ExecuteScalar();                                        
    }

    if (result != null && result != DBNull.Value)
    {
        ConfirmDeleteEMP form = new ConfirmDeleteEMP();
        form.PassValueName = DropBoxEmp.Text;
        form.PassSelectedPayroll = (int)result;
        form.Tag = this;

        form.Show(this);
        Hide();
    }                    
}

If you really want to loop through the rows against all reason (it's slower, requires writing more code, and it's more error-prone), you can do this:

private void BtnDelete_Click(object sender, EventArgs e)// Sends to ConfirmDeleteEMP Form 
{                   
    DataTable table = new DataTable();
    string query = "SELECT PayrollNo, (FirstName + ' ' + LastName) AS NAME FROM [Employee]"; 
    string connstring = @"Provider = Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\\HoliPlanData.accdb;Persist Security Info=False";
    using (OleDbConnection conn = new OleDbConnection(connstring))
    {                    
        OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn);
        adapter.Fill(table);                                        
    }

    int PayrollNumber = 0;
    foreach (DataRow ThisRow in table.Rows)
    {
        if (DropBoxEmp.Text == ThisRow["NAME"])
        {
            PayrollNumber = (int)ThisRow["PayrollNo"];
            break;
        }  
    }
    //the whole loop could also be consolidated to this:
    //PayrollNumber = (int)table.Rows.First(r => r["NAME"] == DropBoxEmp.Text)["PayrollNo"];

    ConfirmDeleteEMP form = new ConfirmDeleteEMP();
    form.PassValueName = DropBoxEmp.Text;
    form.PassSelectedPayroll = PayrollNumber ;               
    form.Tag = this;
    form.Show(this);
    Hide();
}

Looping through a DataTable

foreach (DataColumn col in rightsTable.Columns)
{
     foreach (DataRow row in rightsTable.Rows)
     {
          Console.WriteLine(row[col.ColumnName].ToString());           
     }
} 

How to iterate through a datatable and set values?

The modern way of writing it is:

row.SetField("DateRcvd", DateTime.Today);

Since SetField is a generic method, it won't box your value type like using the default indexer will (the indexer takes an object).

looping through data table to get two specifics values from each row

You don't need to surround your for loop with a foreach loop on the Rows. (You are not using the dr at all)

for (int idx = 0; idx < dt.Rows.Count; idx++)
{
    Console.WriteLine(dt.Columns[0].ColumnName + " ");
    Console.WriteLine(dt.Rows[idx].ItemArray[0] + " ");
    Console.WriteLine(dt.Columns[1].ColumnName + " ");
    Console.WriteLine(dt.Rows[idx].ItemArray[1] + " ");
    Console.WriteLine(dt.Columns[4].ColumnName + " ");
    Console.WriteLine(dt.Rows[idx].ItemArray[4] + " ");
}

A bit more generic version:

int[] columnIndexes = new[] { 0, 1, 4 };
for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
{
    for (int columnIndex = 0; columnIndex < columnIndexes.Length; columnIndex++)
    {
        Console.WriteLine(dt.Columns[columnIndex].ColumnName + " ");
        Console.WriteLine(dt.Rows[rowIndex].ItemArray[columnIndex] + " ");
    }
}

---

If you want to iterate through the Rows collection with foreach then you can do but it is a bit more trickier.

DataTable's Rows property is a DataRowCollection. It exposes a GetEnumerator method, which is essential for the foreach loop.

foreach (DataRow dr in dt.Rows)
{
    //dr does not provide you direct access to the ColumnName
}

You can't access the ColumnName from the DataRow directly. All need to do is to create a "lookup table" for the column names where the key is the index and the value is the name of the column.

int colIdx = 0;
var columnNames = dt.Columns
    .Cast<DataColumn>()
    .ToDictionary(_ => colIdx++, column => column.ColumnName);

After that your foreach loop would look like this:

int[] columnIndexes = new[] {0, 1, 4};
foreach (DataRow row in dt.Rows)
{
    for (int columnIndex = 0; columnIndex < columnIndexes.Length; columnIndex++)
    {
        Console.WriteLine(columnNames[columnIndex] + " ");
        Console.WriteLine(row.ItemArray[columnIndex] + " ");
    }
}

How to iterate over rows of a Frame in DataTable

You can use .to_tuples to achieve row iteration.

from datatable import dt
data = {"A": [1, 2, 3, 4, 5],
        "B": [4, 5, 6, 7, 8],
        "C": [7, 8, 9, 10, 11],
        "D": [5, 7, 2, 9, -1]}

DT = dt.Frame(data)

for row in DT.to_tuples():
        print(row)

---

Related Topics