How does one insert a column into a dataset between two existing columns?

Question

I'm trying to insert a column into an existing DataSet using C#.

As an example I have a DataSet defined as follows:

DataSet ds = new DataSet();
ds.Tables.Add(new DataTable());
ds.Tables[0].Columns.Add("column_1", typeof(string));
ds.Tables[0].Columns.Add("column_2", typeof(int));
ds.Tables[0].Columns.Add("column_4", typeof(string));

later on in my code I am wanting to insert a column between column 2 and column 4.

DataSets have methods for adding a column but I can't seem to find the best way in insert one.

I'd like to write something like the following...

...Columns.InsertAfter("column_2", "column_3", typeof(string))

The end result should be a data set that has a table with the following columns: column_1 column_2 column_3 column_4

rather than: column_1 column_2 column_4 column_3 which is what the add method gives me

surely there must be a way of doing something like this.

Edit...Just wanting to clarify what I'm doing with the DataSet based on some of the comments below:

I am getting a data set from a stored procedure. I am then having to add additional columns to the data set which is then converted into an Excel document. I do not have control over the data returned by the stored proc so I have to add columns after the fact.

Just out of curiosity, why are trying to do this? It's usually not a good idea for database code to be dependent on the order columns are stored in. — Ferruccio, Dec 09 '08 at 02:05
I am getting a data set from a stored procedure. I am then having to add additional columns to the data set which is then converted into an Excel document. I do not have control over the data returned by the stored proc so I have to add columns after the fact. — mezoid, Dec 09 '08 at 02:10
I've been there. Sometimes you just have to write a hack. Usually, because your boss demands it. — Paul Morel, Dec 09 '08 at 04:07

score 45 · Accepted Answer · edited Jan 03 '12 at 13:57

You can use the DataColumn.SetOrdinal() method for this purpose.

DataSet ds = new DataSet();
ds.Tables.Add(new DataTable());
ds.Tables[0].Columns.Add("column_1", typeof(string));
ds.Tables[0].Columns.Add("column_2", typeof(int));
ds.Tables[0].Columns.Add("column_4", typeof(string));
ds.Tables[0].Columns.Add("column_3", typeof(string));
//set column 3 to be before column 4
ds.Tables[0].Columns[3].SetOrdinal(2);

score 8 · Answer 2 · edited Mar 08 '17 at 10:33

I used your suggestion to create an extention method for the DataSet's DataColumnCollection:

public static void InsertAfter(this DataColumnCollection columns, 
                              DataColumn currentColumn, DataColumn newColumn)
{
    if (!columns.Contains(currentColumn.ColumnName))
       throw new ArgumentException(/** snip **/);

    columns.Add(newColumn);
    //add the new column after the current one
    columns[newColumn.ColumnName].SetOrdinal(currentColumn.Ordinal + 1); 
}

I can now write:

 dt = ds.Tables[0];
 dt.Columns.InsertAfter(dt.Columns["column_2"], new DataColumn("column_3"));

score 0 · Answer 3 · edited May 23 '17 at 12:02

0

Based on https://stackoverflow.com/a/17372008/492336, I use SetOrdinal with IndexOf() to insert bar before foo:

table.Columns.Add("bar").SetOrdinal(table.Columns.IndexOf("foo"));

To insert it after foo just add +1:

table.Columns.Add("bar").SetOrdinal(table.Columns.IndexOf("foo")+1);

edited May 23 '17 at 12:02

Community

1
1

answered Oct 12 '16 at 06:53

sashoalm

75,001
122
434
781

score -1 · Answer 4 · edited Jan 03 '12 at 13:57

-1

Copy the first two columns into a new dataset, then add the third column, and add the remaining columns.

You could wrap that in an InsertAfter function if necessary.

edited Jan 03 '12 at 13:57

mac

42,153
26
121
131

answered Dec 09 '08 at 01:57

Paul Morel

551
2
13

How does one insert a column into a dataset between two existing columns?

4 Answers4

Linked