CODEWITHSUNDEEP
c#sql-serverdatatable
jophab
jophab

Reputation: 5509

C# : Removing Columns from Datatable

Suppose I have two tables

Table 1 with following columns (Dots to represent columns in between)

id int
name varchar
.
.
.
.
LastChanged datetime

Table 2 with following columns

id int
Address varchar
.
.
.
.
LastChanged datetime

I executed a query like below in C# and stored the result in a Datatable.

select * from table1 t1
inner join  table2 t2
on t1.id=t2.id

Now I don't want the column LastChanged in the result.

LastChanged column is present in both tables. Since Datatable doesn't allow duplicate column names, one of the LastChanged column in result is renamed to LastChanged1

So when I try to remove the column using

if (table.Columns.Contains("LastChanged"))
                table.Columns.Remove("LastChanged");

Only one LastChanged is removed. The other one remains.

One solution is to eliminate LastChanged in the query itself by specifying the required columns. But it makes query long since almost all columns except LastChanged is needed in result.

Is there a solution in C#?

Upvotes: 1

Views: 490

Answers (2)

Yahfoufi
Yahfoufi

Reputation: 2544

You can use Linq to achieve this

var lst = table.Columns.Cast<DataColumn>()
     .Where(x => x.ColumnName.StartsWith("LastChanged"))
     .ToList();


foreach(DataColumn col in lst){
      table.Columns.Remove(col);}

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460058

Read carefully: Bad habits to kick : using SELECT * / omitting the column list

However, to answer the question, you could do this LINQ approach:

string colName = "LastChanged";
var lastChangedColumnList = table.Columns.Cast<DataColumn>()
  .Where(c => c.ColumnName.StartsWith(colName) && c.ColumnName.Substring(colName.Length).All(char.IsDigit))
  .ToList();

foreach(DataColumn col in lastChangedColumnList)
    table.Columns.Remove(col);

Upvotes: 1

Related Questions