如何在第三个数据表中的两个数据表中存在相同的项目 [英] How to get same items exist in two datatables in third datatable

问题描述

问题



如何获取相同的项目由linq存在于第三个数据表中的两个数据表中并显示在datagridview中



在Windows窗体应用程序Visual Studio 2015中。



含义我有两个数据表



第一个数据表是dt从Excel获取数据。



其次是dtItems从sql server 2014数据库获取数据。



当我有商品代码12时我需要这个项目存在于两个数据表上



dt（显示来自excel的数据）和dtItems （显示来自sql server的数据）



在datagridview中显示它们



如果项目代码12存在于dt和dtItems显示在datagridview上。



这样如何在数据网格中通过linq到sql的两个数据表之间的项目。



我尝试过：

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();

推荐答案

您可以在上执行连接DataTable 使用 LINQ 的对象。为此，您需要先在 DataTable 对象上调用 AsEnumerable 。如果添加 System.Data.Data.DataSetExtensions 命名空间，则此方法可用。



这是一个示例代码连接第一列的第一和第二表。

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.

// 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]
}

);

获取常见数据的最简单方法是使用Linq join子句 [ ^ ]。



请参阅：

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

See:

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

这篇关于如何在第三个数据表中的两个数据表中存在相同的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！