How to get same items exist in two datatables in third datatable

Question

0.00/5 (No votes)

See more:

Problem

How to get same Items Exist in two Data tables in third data table by linq and display in datagridview

in windows form application visual studio 2015.

Meaning I Have two datatables

First Data table is dt Get data from Excel .

Second is dtItems get data from sql server 2014 database .

I need when I have itemcode 12 and this item exist on two datatables

dt(display data from excel) AND dtItems (Display data from sql server)

show them in datagridview

if itemcode 12 exist on dt and dtItems display on datagridview .

so that how to get similar it Items between two datatable by linq to sql in datagridview .

What I have tried:

public DataTable ShowExcelData()
        {
            string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", txtpath.Text);

            OleDbConnection con = new OleDbConnection(connectionString);


            con.Open();
            DataTable dt = new DataTable();

            dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            string SheetName = dt.Rows[0]["TABLE_NAME"].ToString();


            OleDbCommand com = new OleDbCommand();
            com.Connection = con;
           
            com.CommandText = @"SELECT  [ItemCode],[ItemsName],[ItemAddress] FROM  [" + SheetName + "] ";
            OleDbDataAdapter oledbda = new OleDbDataAdapter();
            oledbda.SelectCommand = com;
            DataSet ds = new DataSet();
            oledbda.Fill(ds);
            dt = ds.Tables[0];
            con.Close();
            return dt;


        }
dt = ShowExcelData();

 public DataTable GetSqlItems()
        {
            string GetItems = @"select ItemCode,ItemsName,ItemAddress from Items";


           DataTable tbGetItems = DataAccess.ExecuteDataTable(GetItems );
            return tbGetItems ;
        }
dtItems = GetSqlItems();

Posted 24-Sep-18 14:59pm

ahmed_sa

Updated 24-Sep-18 22:16pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

*dan!sh* · Answer 1 · 2018-09-24T18:05:00

You can perform joins on DataTable objects using LINQ. To do so, you will need to first call AsEnumerable on DataTable object. This method will be available if you add System.Data.Data.DataSetExtensions namespace.

Here is a sample code joining table one and two on the first column.

C#

// Prepare first table
DataTable firstTable = new DataTable();
firstTable.Columns.Add("0");
firstTable.Columns.Add("1");

DataRow firstRow1 = firstTable.NewRow();
firstRow1[0] = "1";
firstRow1[1] = "First Table First Row";
firstTable.Rows.Add(firstRow1);

DataRow secondRow1 = firstTable.NewRow();
secondRow1[0] = "02";
secondRow1[1] = "Should not come up";
firstTable.Rows.Add(secondRow1);

// Prepare second table
DataTable secondTable = new DataTable();
secondTable.Columns.Add("0");
secondTable.Columns.Add("1");

DataRow firstRow2 = secondTable.NewRow();
firstRow2[0] = "1";
firstRow2[1] = "Second table first row";
secondTable.Rows.Add(firstRow2);

DataRow secondRow2 = secondTable.NewRow();
secondRow2[0] = "2";
secondRow2[1] = "Second table second row";
secondTable.Rows.Add(secondRow2);


// Join them on first column and show all four columns from both tables
var joinedResult = firstTable.AsEnumerable().Join(secondTable.AsEnumerable(), first => first[0], second => second[0], (first, second) => new
{
First0 = first[0], Second0 = second[0], First1 = first[1], Second1 = second[1]
}

);

Maciej Los · Answer 2 · 2018-09-24T22:17:00

Solution 2

The simplest way to get common data is to use Linq join clause[^].

See:

C#

var commondata = from exd in ExcelDataTable.AsEnumerable()
    join sqd in SqlDataTable.AsEnumerable() on exd.Fields<int>("ItemCode") equals sqd.Fields<int>("ItemCode")
    select sqd

Posted 24-Sep-18 22:17pm

Maciej Los