ADO.NET - Manually filling out column

Question

(Using Visual Studio 2005 / .NET 2.0)

I have a DataSet which is being prepopulated from another module using SQL. All the values in it are fine and will stay the same as they are.

But once I retrieve the DataSet (and assign it to a DataTable since it is a one-table set) I need to add an additional column onto the end of the DataTable and populate it based on values in each row.

As a simplified example, let's say for example:

Column 1's values need to be checked. If they are equal to "A", the new column should be set to "G". "B", likewise "H", and "C" likewise "I".

What I need to do is cycle through the table, checking the value of each row and populate the new column for that row accordingly.

I am having trouble finding the way to do this (most specifically, to set the value of the DataColumn identifying it by name or some other identifier - array would be ok) and could use some code snippets or guidance.

It seems fairly straightforward but I can't find the information about how to do it anywhere I've looked. Thanks in advance...

Answer 1

DataColumn dc = dt.Columns.Add("columnName", typeof(whatever));
foreach (DataRow dr in dt.Rows) {
    dr[dc] = CalculateFromOtherColumns(dr);
}

Answer 2

If the comparisson is simple you might want to use an expression column in your datatable (check the overloads of Columns.Add for more information and MSDN)

Otherwise you should first add a normal column through Columns.Add with the right datatype (from what you write a string or perhaps boolean, judging by the true/false kinda values you want in that column)

After that do a foreach on each of the rows:

foreach (DataRow row in table.Rows)
  if (row["columnA"] == "A")
    row["columnX"] = "G";
  else
    row["columnX"] = "F";

And yeah you should do type checking and you can make this code a lot shorter (but I'm typing on an iPhone right now :))

Answer 3

You can do this by programmatically creating the column and then, using the name of your new column, populate it based on whatever conditions you require.

Here's a snippet that shows what I mean:

    ...
    yourDataTable.Columns.Add("MyNewColumn");

    foreach (DataRow row in yourDataTable.Rows) {
        string column1Value = row["Column1"].ToString();
        if (column1Value == "A") {
            row["MyNewColumn"] = "G";
        } else if (column1Value == "B") {
            row["MyNewColumn"] = "H";
        } else if (column1Value == "C") {
            row["MyNewColumn"] = "I";
        }
    }
    ...

Is this what you're looking for?

-Jay