CODEWITHSUNDEEP
c#datatable
rach
rach

Reputation: 679

Compare one datatable's rows to another datatable's columns

first table:dtHeader
 Header
---------
Address
ZipCode
city
state


Second table:-dtReport

RowNumber-----Address------Zipcode---Region------city

   1200       JC           00000     NYC         Bronx

   13000       RC            12345    NC          Boston

I want to remove all columns in dtreport where columnname is not in dtHeader So the result dtReport will have Address--zipcode--city columns only. I dont want to create another datatable as it has large number of rows i.e 70000 How can I achieve this?

Upvotes: 1

Views: 1263

Answers (3)

Tim Schmelter
Tim Schmelter

Reputation: 460108

You can use Enumerable.Except to find all column-names that must be removed from dtReport

var notAllowedColNames = dtReport.Columns.Cast<DataColumn>()
    .Select(c=> c.ColumnName.ToUpperInvariant())
    .Except(dtHeader.AsEnumerable().Select(r => r.Field<String>("Header").ToUpperInvariant()))
    .ToList();
foreach(var colName in notAllowedColNames) 
     dtReport.Columns.Remove(colName);

I have used ToUpperInvariant since ZipCode has a different case in both tables.

Upvotes: 1

David Scott
David Scott

Reputation: 1084

An all SQL answer would look like this

    INSERT INTO #TEMP_TABLE
  SELECT c.name
FROM sys.tables AS t
INNER JOIN sys.columns AS c ON t.OBJECT_ID = c.OBJECT_ID
WHERE t.name = 'dtReport' AND c.name NOT IN (SELECT Header FROM dtHeader)

  WHILE ((SELECT COUNT(*) FROM #TEMP_TABLE) > 0)
    BEGIN
        DECLARE @COLUMN_NAME VARCHAR(50) = (SELECT TOP 1 Header FROM #TEMP_TABLE)
        ALTER TABLE PATRON DROP COLUMN @COLUMN_NAME
        DELETE FROM #TEMP_TABLE WHERE Header = @COLUMN_NAME
    END

Upvotes: 0

Steve
Steve

Reputation: 216293

Not tested, but a simple loop on the dtReport columns collection checking if dtHeader columns collection contains a equal named column. If not remove the column from dtReport.

    SqlCommand cmdHeader = new SqlCommand("SELECT * FROM Header", conn);
    SqlCommand cmdReport = new SqlCommand("SELECT * FROM Report", conn);
    DataTable dtHeader = new DataTable();
    DataTable dtReport = new DataTable();
    SqlDataAdapter da1 = new SqlDataAdapter(cmdHeader);
    da1.Fill(dtHeader)
    SqlDataAdapter da2 = new SqlDataAdapter(cmdReport);
    da2.Fill(dtReport);

    for(int x = dtReport.Columns.Count - 1; x >= 0; x--)
    {
        DataColumn dc = dtReport.Columns[x];
        if(!dtHeader.Columns.Contains(dc.ColumnName))
             dtReport.Columns.Remove(dc.ColumnName);
    }

The trick is to use a for..loop going from the last column to the first. In this way you could remove the column while iterating over the collection (foreach cannot do this)

Upvotes: 1

Related Questions