LINQ: Update a column with auto increment value

Article
2015-02-27

Question

_{Friday, February 27, 2015 2:07 PM}

Hi All,

Greetings.

I am very new to C# (though with lot of experience in VC++). Now a days, I am engaged in a C# project where I need to deal with ADO.NET DataTable and related classes. I am stuck up in a scenario as follows:

I have a data table (of DataTable type) pre-populated with many rows (50000+), parsed from a log file. Now I need to add a column that will be holding index number for each records; eg. 1, 2, 3......

I have tried to add the auto-incremented column dynamically, with seed = 0 and increment = 1, but it is not working.

Is there any LINQ query that I can run to update the DataTable at once? Or foreach loop per DataRow is the only option here?

I appreciate any quick suggestion on this.

Thanks in advance.

Sanjoy Jana.

All replies (4)

_{Friday, February 27, 2015 2:40 PM ✅Answered}

You should add the auto increment column to the DataTable before you pouplate it with the other data:

      //1. Create a datatable
      DataTable dt = new DataTable();

      //2. add the autoincrement column:
      DataColumn column = new DataColumn();
      column.DataType = System.Type.GetType("System.Int32");
      column.AutoIncrement = true;
      column.AutoIncrementStep = 1;
      column.AutoIncrementSeed = 1;

      //3. Fill the dataTable using an SqlDataAdapter or whatever
      dt.Columns.Add(new DataColumn("name"));
      dt.Rows.Add("1");
      dt.Rows.Add("2");
      dt.Rows.Add("3");
      dt.Rows.Add("4");

Then you don't have to add the "auto incremented" values yourself in some kind of a loop (using for example LINQ or foreach).

If you want to know how to poplulate the DataTable from a database query using an adapter, please refer to the following page: http://www.dotnetperls.com/sqldataadapter

Hope that helps.

Please remember to mark helpful posts as answer to close your threads and then start a new thread if you have a new question.

_{Friday, February 27, 2015 7:05 PM ✅Answered}

Anyway, I would appreciate if you can suggest some good LINQ solution to avoid loop traversing through the Rows.

Well that would be a good trick indeed, since that's precisely what linq updating a column in the datatable would have to do. I would try a simple foreach loop.

dt.Columns.Add("Id", typeof(System.Int32)).SetOrdinal(0);

int i=0;

foreach (DataRow row in datatable.Rows)
{
    i++;
    row[0] = i;

}

Where dt is your datatable.

Using the ordinal is more efficient than name of the column, if you're wondering why I inserted it as the first column.

Hope that helps.
Recent Technet articles: Property List Editing; Dynamic XAML

_{Saturday, February 28, 2015 8:57 AM ✅Answered}

>>Anyway, I would appreciate if you can suggest some good LINQ solution to avoid loop traversing through the Rows.

What do you think LINQ does under the hood? It produces a loop and iterates through the records. There is no need use LINQ just for the sake of it :)

You are not avoiding traversing the rows just because your are using LINQ to the traversion if that's what you think.

You cold just add the column and increment if afterwards like this: http://stackoverflow.com/questions/16179724/set-autonumber-for-datatable-after-it-already-has-data

There is no way to modify the value of a column of all rows without somehow iterating over the rows anyway.

Please remember to mark all helpful posts as answer to close your threads and please start a new thread if you have a new question. Please don't ask several questions in the same thread.

_{Friday, February 27, 2015 6:41 PM}

Many thanks Magnus for your quick response. I really appreciate it.

It is indeed a good solution; but unfortunately, this would not be working for my situation as the requirement is little more complex as follows:

1. first parse and read the log file into the DataTable

2. then apply some row filter logic on the DataTable to purge unnecessary rows.

3. then apply some column filter login to remove some columns.

4. then add the sequential index to the final DataTable.

This is why I cannot use the solutions suggested by you though it is absolutely correct for other similar situation.