Asked by:
Match the data tables values from data-table 1 and data-table 2 and bring the match values into another data-table 3 using LINQ OR Some other trick

Question
-
User-471420332 posted
I have two data-table like below
DataTable dt = bl.Get_LKP_Tables(bo); DataTable dt2 = bl.Get_LKP_Tables(bo);
Datatable dt have below value
F1 F2 F3 F4 Yes No Yes No
Data-table dt2 have below value
F1 F2 F3 Yes Yes Yes
I want match output like below into Data-table dt3
F1 F3 Yes Yes
How to match the column name with values and no need to match static by adding column names like f1 or f2 or f3 or f4 conditions because in feature more field will add then it will be issued a base on the column name and value match only need to check?
Thursday, March 28, 2019 2:12 PM
All replies
-
User753101303 posted
Hi,
And you have only a single row or you can have multiple rows?
For now it seems you want to basically copy to a 3rd table columns having both "Yes" in the first 2 tables (or maybe having the same value ? not sure what happens if F2 is "No" in the 2 first tables, it is also ignored or a F2 column is created with the "No" value).
Also sometimes it's best to have a big picture. As you talk about adding more fields it could be also some kind of normalization problem that would easier to handle anyway way (for example if F1 is for January, F2 for February etc...)
For example it could be something such as :
SELECT t1.* FROM (VALUES('F1','Yes'),('F2','No'),('F3','Yes'),('F4','No')) AS t1(id,value) -- Trick to create sample data without an actual table JOIN (VALUES('F1','Yes'),('F2','Yes'),('F3','Yes')) AS t2(id,value) ON t1.id=t2.id WHERE t1.Value='Yes' AND t2.Value='Yes'
and you could add rows (rather than columns) as needed
Thursday, March 28, 2019 5:03 PM -
User475983607 posted
The design makes little sense. Can you explain the problem you are trying to solve?
Thursday, March 28, 2019 5:08 PM -
User-471420332 posted
I have single row from both datatables... dt have one row and dt2 have one row.. it will not more than one row from both data tables
And I don't want select query using linq or for each condition.. and please don't check condition like f1 or f2 or f3 or f4Thursday, March 28, 2019 7:42 PM -
User-893317190 posted
Hi mazhar khan india,
If you accept to use for each and if condition, you could use datatable's columns property.
Below is my code.
protected void Page_Load(object sender, EventArgs e) { DataTable table = new DataTable(); table.Columns.Add(new DataColumn("F1")); table.Columns.Add(new DataColumn("F2")); table.Columns.Add(new DataColumn("F3")); table.Columns.Add(new DataColumn("F4")); table.Rows.Add("Yes", "No", "Yes", "No"); DataTable table2 = new DataTable(); table2.Columns.Add(new DataColumn("F1")); table2.Columns.Add(new DataColumn("F2")); table2.Columns.Add(new DataColumn("F3")); table2.Rows.Add("Yes", "Yes", "Yes");
List<string> table_columns = new List<string>(); List<string> table2_columns = new List<string>(); foreach (DataColumn item in table.Columns)// populate table_columns with all the columnnames in table { table_columns.Add(item.ColumnName); } foreach (DataColumn item in table2.Columns)// populate table_columns with all the columnnames in table2 { table2_columns.Add(item.ColumnName); } IEnumerable<string> common_cols= table_columns.Intersect(table2_columns);// get common column names in table and table2 DataTable final_table = new DataTable(); foreach (string com_col in common_cols) { if(table.Rows[0][com_col].ToString()=="Yes" && table2.Rows[0][com_col].ToString()=="Yes")// get all the yes column { final_table.Columns.Add(new DataColumn(com_col)); } } final_table.Rows.Add(new string[final_table.Columns.Count].Select(c=>"Yes").ToArray());// populate the first row with yes GridView1.DataSource = final_table; GridView1.DataBind(); }The result.
Best regards,
Ackerly Xu
Friday, March 29, 2019 2:21 AM -
User-471420332 posted
Dear Ackerly Xu,In Future may be values will change Yes/No to some other values can you pass some dynamic.
See datatable dt and datable dt1 both have only one row every time, so you can match dynamic because both have one row only with column name and value
Hope you getting my point
Friday, March 29, 2019 5:24 AM -
User-893317190 posted
Hi mazhar khan india,
Not sure about your requirement, if you want to get dynamic value, just change the value of yes to what you like.
From your table , the value may be yes or no , in the future , the value may be a or b.
Then there is one problem, what value you want to filter?
Yes or no , a or b.
This depends on your requirement.
If you want to get columns whose values are all yes, then you should specify yes, if you want to get columns whose values are all a , you should specify a.
In the code ,you should channg yes to no or whatever you like ,.
foreach (string com_col in common_cols) { if(table.Rows[0][com_col].ToString()=="write the value you want" && table2.Rows[0][com_col].ToString()=="write the value you want")// get all the yes column { final_table.Columns.Add(new DataColumn(com_col)); } } final_table.Rows.Add(new string[final_table.Columns.Count].Select(c=>"write the value you want").ToArray());// populate the first row with yes
Best regards,
Ackerly Xu
Friday, March 29, 2019 7:01 AM