15

I'm trying to set the same value for all rows for a single column in a datatable without using for loop. Can anyone suggest any faster methods to achieve the same.

Soham Dasgupta
  • 5,061
  • 24
  • 79
  • 125

5 Answers5

20

yes, this is possible, even without loop! This can be done with Expression property. Keep in mind that string values should be quoted, otherwise it sees this as another columnname.1

This will set the column 'col1' with the string value a:

preview.Columns["col1"].Expression = "'a'";

This will display in 'col1' the value of the column 'col2':

preview.Columns["col1"].Expression = "col2";
Julian
  • 33,915
  • 22
  • 119
  • 174
  • 8
    It's worth mentioning that even this approach uses a loop. It's just hidden from the developer and sits in [`DataColumn.EvaluateExpression`](http://referencesource.microsoft.com/#System.Data/System/Data/DataTable.cs,d3b24c1951b112d9,references). It's called from the `Expression` setter. – Tim Schmelter Mar 16 '15 at 07:48
  • if ones wants to bind the datatable/dataview to MVVM to wpf etc, please notice that .Expression makes the column ReadOnly. Even if is not specified as ReadOnly in the xaml. I ended up using foreach() loop for that reason – pbou Oct 13 '22 at 13:25
12

Not unless you count foreach. One way or another, you'll need to loop.

If you use the DataTable version, the fastest approach is to use the DataColumn accessor, i.e.

var col = table.Columns["Foo"];
foreach(var row in table.Rows)
    row[col] = value;

As an alternative : since this presumably relates to a database, write the TSQL manually to set all the values appropriately (i.e. with a suitable where clause in the TSQL).

update [theTable]
set [theColumn] = theValue
where --TODO - something sensible
Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • 1
    The datatable is already populated with data but each time it enters a specific function, some of the columns get a value. When the datatable is again passed to the same fucntion again I want to set some specific values. Foreach works well and it is already working, but its very slow. My problem is with the slowness of Foreach construct. – Soham Dasgupta Feb 21 '11 at 08:17
  • 4
    @Soham `foreach` is actually very fast. If it is slow for you, then the fundamental problem is that you have loaded **too much data**. It is generally not necessary to load an entire table, for example. You might get an improvement by moving to a direct type model (rather than `DataTable`), but either way this is `O(n)` - it is simply the difference in the linear multiplier. The problem here is the size of the `n` - nothing to do with the `foreach`. – Marc Gravell Feb 21 '11 at 08:21
  • 1
    Can you specify what exactly you mean by direct type model? – Soham Dasgupta Feb 21 '11 at 08:56
  • 1
    @soham Using specific classes (i.e. `public class Customer {...} public class Order {...}`) instead of using DataTable. DataTable is a *general purpose* tool - it has overheads as a result. – Marc Gravell Feb 21 '11 at 08:59
  • 1
    "One way or another, you'll need to loop. ". This isn't true, see my post below – Julian Oct 05 '12 at 10:41
  • @Julian: No, you're also using a loop, you just can't see it, it's hidden from the developer and sits in [`DataColumn.EvaluateExpression`](http://referencesource.microsoft.com/#System.Data/System/Data/DataTable.cs,d3b24c1951b112d9,references). It's called from the `Expression` setter. – Tim Schmelter Mar 16 '15 at 07:51
8

Have you considered removing and re-adding the column with a default value?

dt.Columns.Remove("FilterText")
dt.Columns.Add(New DataColumn() With {.ColumnName = "FilterText",
                                      .DataType = GetType(String),
                                      .DefaultValue = "Jam and chips"})

(assuming your column name is FilterText, and you want to set it to "Jam and chips")

Jonathan
  • 25,873
  • 13
  • 66
  • 85
0

I ran in to this type of problem where the Select All Checkbox was used to set a column "sel" to 1 or 0, the foreach loop is very slow, I grabed the idea from @Jonathan answer which is more faster, but with some minor modification as the With clause do not work in c# 

dtEmpList.Columns.Remove("sel");
dtEmpList.Columns.Add(new DataColumn("sel",typeof(bool)(chkSelectAll.Checked ? "1" : "0")));
Sohaib Yahya
  • 1
  • 3
0
//--Adding column datatype string in C#

string strDocument = "yourStringValue";

DataColumn col = new DataColumn();
col.ColumnName = "document";
col.AllowDBNull = false;
col.DataType = typeof(string);
col.DefaultValue = strDocument ;
dr.Columns.Add(col);
gtz
  • 1