How can I add a new column and data to a datatable that already contains data?

Question

How do I add a new DataColumn to a DataTable object that already contains data?

PseudoCode

//call SQL helper class to get initial data 
DataTable dt = sql.ExecuteDataTable("sp_MyProc");

dt.Columns.Add("NewColumn", type(System.Int32));

foreach(DataRow row in dr.Rows)
{
    //need to set value to NewColumn column
}

score 153 · Accepted Answer · edited Mar 15 '16 at 14:07

153

Just keep going with your code - you're on the right track:

//call SQL helper class to get initial data 
DataTable dt = sql.ExecuteDataTable("sp_MyProc");

dt.Columns.Add("NewColumn", typeof(System.Int32));

foreach(DataRow row in dt.Rows)
{
    //need to set value to NewColumn column
    row["NewColumn"] = 0;   // or set it to some other value
}

// possibly save your Dataset here, after setting all the new values

edited Mar 15 '16 at 14:07

JohnB

18,046
16
98
110

answered Feb 22 '10 at 18:25

marc_s

732,580
175
1,330
1,459

Did you need to call `dt.AcceptChanges()` after I add the new column and value? – Michael Kniskern Feb 22 '10 at 18:32
2

@Michael: no, not really. Your data set is now prepped with those new values. If you want to save it, you need to have some kind of method to write it back (through the SqlDataAdapter or some other means). AcceptChanges() won't do anything (except mark those changes as "accepted" -> they won't be detected for saving the next time you save your data!) – marc_s Feb 22 '10 at 19:59

score 14 · Answer 2 · edited Jan 06 '16 at 11:59

14

Should it not be foreach instead of for!?

//call SQL helper class to get initial data  
DataTable dt = sql.ExecuteDataTable("sp_MyProc"); 

dt.Columns.Add("MyRow", **typeof**(System.Int32)); 

foreach(DataRow dr in dt.Rows) 
{ 
    //need to set value to MyRow column 
    dr["MyRow"] = 0;   // or set it to some other value 
}

edited Jan 06 '16 at 11:59

Musakkhir Sayyed

7,012
13
42
65

answered Jan 06 '12 at 20:54

Imir Hoxha

1,674
6
31
56

score 10 · Answer 3 · answered Feb 18 '15 at 10:51

10

Only you want to set default value parameter. This calling third overloading method.

dt.Columns.Add("MyRow", type(System.Int32),0);

answered Feb 18 '15 at 10:51

Nishantha

6,065
6
33
51

1

should "type" be "typeof" ? – Fritz W Jul 11 '22 at 14:13

score 9 · Answer 4 · answered Mar 02 '15 at 22:16

9

Here is an alternate solution to reduce For/ForEach looping, this would reduce looping time and updates quickly :)

 dt.Columns.Add("MyRow", typeof(System.Int32));
 dt.Columns["MyRow"].Expression = "'0'";

answered Mar 02 '15 at 22:16

Akxaya

834
8
6

3

@Akxaya, would you mind elaborating on how that solution works? – Mikael Dúi Bolinder Jan 14 '19 at 15:45
DataTable dt = sql.ExecuteDataTable("sp_MyProc"); // dt.Columns.Add("MyRow", typeof(System.Int32)); dt.Columns["MyRow"].Expression = "'0'"; – Akxaya Feb 25 '20 at 23:27
[Expression](https://learn.microsoft.com/en-us/dotnet/api/system.data.datacolumn.expression?view=netframework-4.7) supports _a lot_ of other things like calculations, aggregates, comparisons, IIF, LIKE, ISNULL, etc. – ourmandave Sep 24 '21 at 14:58

score 2 · Answer 5 · answered Jul 14 '15 at 18:03

2

Try this

> dt.columns.Add("ColumnName", typeof(Give the type you want));
> dt.Rows[give the row no like  or  or any no]["Column name in which you want to add data"] = Value;

answered Jul 14 '15 at 18:03

Himanshu Shukla

145
2
1

score 1 · Answer 6 · answered Jun 26 '23 at 10:41

1

In net6.0 Language Version C# 10 the solution for setting a default value is:

dt.Columns.Add("MyRow", type(System.Int32)).DefaultValue = 0;

answered Jun 26 '23 at 10:41

Legit007

181
1
8

How can I add a new column and data to a datatable that already contains data?

6 Answers6

Linked