Comparing two datatable by matching a column and returning a third datagridview C#
Publicado por Azk0t (1 intervención) el 06/05/2019 08:28:20
C# WindowsForms.
Proyect: Import 2 Excel and choose the sheet that holds the table, then compare the column "Code" at both files and show the matching ones with their info on third dataGridView.
I found this one to solve but idk how to adapt to my windows form, hope someone can help me, or at least explain a bit how to solve the last part of compare.
//----------------------------------------------------------------------------------------------------------------------------------
DGV1
Code Date Tipe Price Account
3367 02-feb-18 NEW 25 N/A
8543 04-feb-18 NEW 25 N/A
3367 05-feb-18 RENEW 50 N/A
5542 07-feb-18 NEW 75 N/A
1069 27-jan-18 NEW 25 N/A
DGV2
City Code
Texas 3367
Texas 8543
Texas 5542
Texas 8673
DGV3
Code Date Tipe Price Account
3367 02-feb-18 NEW 25 N/A
3367 05-feb-18 RENEW 50 N/A
8543 04-feb-18 NEW 25 N/A
5542 07-feb-18 NEW 75 N/A
Proyect: Import 2 Excel and choose the sheet that holds the table, then compare the column "Code" at both files and show the matching ones with their info on third dataGridView.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
//----------------------------------------------------------------------------------------------------------------------------------
DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
DataTable dtMerged = new DataTable();
private void btnSelect1_Click(object sender, EventArgs e)
{try
{OpenFileDialog openfiledialog1 = new OpenFileDialog();
openfiledialog1.Filter = "Excel Files | *.xlsx; *.xls; *.xlsm";
if (openfiledialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{this.tBox1.Text = openfiledialog1.FileName;
}string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + tBox1.Text + ";Extended Properties = \"Excel 12.0; HDR=YES;\" ; ";
OleDbConnection con = new OleDbConnection(constr);
con.Open();
dropdown_sheet1.DataSource = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
dropdown_sheet1.DisplayMember = "TABLE_NAME";
dropdown_sheet1.ValueMember = "TABLE_NAME";
}catch (Exception ex)
{MessageBox.Show(ex.Message);
}}public void btnLoad1_Click(object sender, EventArgs e)
{try
{string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + tBox1.Text + ";Extended Properties = \"Excel 12.0; HDR=YES;\" ; ";
OleDbConnection con = new OleDbConnection(constr);
OleDbDataAdapter sda = new OleDbDataAdapter("Select * From [" + dropdown_sheet1.SelectedValue + "]", con);
sda.Fill(dt1);
dataGridView1.DataSource = dt1;
}catch (Exception ex)
{MessageBox.Show(ex.Message);
}}private void btnSelect2_Click(object sender, EventArgs e)
{try
{OpenFileDialog openfiledialog2 = new OpenFileDialog();
openfiledialog2.Filter = "Excel Files | *.xlsx; *.xls; *.xlsm";
if (openfiledialog2.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{this.tBox2.Text = openfiledialog2.FileName;
}string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + tBox2.Text + ";Extended Properties = \"Excel 12.0; HDR=YES;\" ; ";
OleDbConnection con = new OleDbConnection(constr);
con.Open();
dropdown_sheet2.DataSource = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
dropdown_sheet2.DisplayMember = "TABLE_NAME";
dropdown_sheet2.ValueMember = "TABLE_NAME";
}catch (Exception ex)
{MessageBox.Show(ex.Message);
}}public void btnLoad2_Click(object sender, EventArgs e)
{try
{string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + tBox2.Text + ";Extended Properties = \"Excel 12.0; HDR=YES;\" ; ";
OleDbConnection con = new OleDbConnection(constr);
OleDbDataAdapter sda = new OleDbDataAdapter("Select * From [" + dropdown_sheet2.SelectedValue + "]", con);
sda.Fill(dt2);
dataGridView2.DataSource = dt2;
}catch (Exception ex)
{MessageBox.Show(ex.Message);
}}private void btnCompare1_Click(object sender, EventArgs e)
{}//----------------------------------------------------------------------------------------------------------------------------------I found this one to solve but idk how to adapt to my windows form, hope someone can help me, or at least explain a bit how to solve the last part of compare.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
public DataTable getLinq(DataTable dt1, DataTable dt2)
{DataTable dtMerged = (from a in dt1.AsEnumerable()
join b in dt2.AsEnumerable()
on a["Place"].ToString() equals b["Place"].ToString()
into g
where g.Count() > 0
select a).CopyToDataTable();
return dtMerged;
}here the source of this code
https://forgetcode.com/CSharp/1508-Comparing-two-datatables-and-returning-a-datatable-by-matching-one-or-more-columns-using-LINQ#
//----------------------------------------------------------------------------------------------------------------------------------
DGV1
Code Date Tipe Price Account
3367 02-feb-18 NEW 25 N/A
8543 04-feb-18 NEW 25 N/A
3367 05-feb-18 RENEW 50 N/A
5542 07-feb-18 NEW 75 N/A
1069 27-jan-18 NEW 25 N/A
DGV2
City Code
Texas 3367
Texas 8543
Texas 5542
Texas 8673
DGV3
Code Date Tipe Price Account
3367 02-feb-18 NEW 25 N/A
3367 05-feb-18 RENEW 50 N/A
8543 04-feb-18 NEW 25 N/A
5542 07-feb-18 NEW 75 N/A
Valora esta pregunta
0