CODEWITHSUNDEEP
c#exceldatatable
Khosrow
Khosrow

Reputation: 69

How join to excel table in c# base on common column?

Here is my to excel sheets :

Sheet one

Sheet two

And here is the code I used in c# to read them:

                _ds = new DataSet();
                _tb = new DataTable();

                var myConnection = new OleDbConnection(@"provider=Microsoft.ACE.OLEDB.12.0;data source=" + OFD.FileName + ";Extended Properties=Excel 12.0;");
                var myCommand = new OleDbDataAdapter("SELECT * FROM ["+SheetName+"$]", myConnection);
                myCommand.Fill(_tb);
                _ds.Tables.Add(_tb);
                // _bs.DataSource = _ds.Tables[0];

                DataTable Sheet0 = _ds.Tables[0];

My question is: how can I join these two based on NID and display result in DataGrid ?

Upvotes: 0

Views: 422

Answers (1)

Icosahedron
Icosahedron

Reputation: 30

joining tables that imported from Excel is no different from usual join but there is trick. these tables should have a Primary-Key column.
Then in your case first we set the "id" column as Primary-Key: 

Sheet0.PrimaryKey = new DataColumn[] { Sheet0.Columns["NID"] };

Remember: NID column values should be unique AND you should do this for other tables too.
After setting primary-key you can use join or merge your tables. I use merge method cause it's simpler one. :) :

    var _TableAll = Sheet0.Copy();
   _TableAll.Merge(_Table2);

There is question posted here about join method if you are interested. You can check it out.
I hope this help.

Upvotes: 1

Related Questions