Visual CSharp .NET - Comparing two datatable by matching a column and returning a third datagridview C#

 
Vista:
sin imagen de perfil
Val: 2
Ha aumentado 1 puesto en Visual CSharp .NET (en relación al último mes)
Gráfica de Visual CSharp .NET

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.

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
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder