从存储在datatable对象中的数据表中选择数据 [英] Selecting data from datatable stored in datatable object

问题描述

我在dtmain datatable中有数据。我想在select查询中使用这个dtmain数据，如下所示。是否有任何方法可以对存储在datatable对象中的数据表进行查询。



下面是我的代码。

I have data in dtmain datatable.I want to use this dtmain data in a select query as below.Is there any method to fire query on datatable stored in datatable object.

Below is my code.

DataSet ds = ReadDataFile("E:\\ImportData\\" + strName + ".xlsx");
 DataTable dtmain = ds.Tables[0];
Sql = "Select Name,Main.* from (SELECT EmpId, FName from  " + dtmain + ")as Main Left Join Table1 T1 ON T1.EmpId =Main.EmpId";

并在sql server中获取sql并获取记录。

and fire Sql in sql server and get records.

推荐答案

如果我没有错，你不能混合数据表使用sql查询。

if I am not wrong you cannot mix data table with sql query.

尝试使用 LINQ to DataSet [ ^ ]

或

DataTable.Select [ ^ ]

Try using LINQ to DataSet[^]
or
DataTable.Select[^]

使用此解决方案即使用linq查询数据表

use this solution i.e. use linq to query on datatable

public DataSet ds;
public DataTable dt, dt1;
ds = new DataSet();

dt = new DataTable();
dt.TableName = "Employee";
dt.Columns.Add("EmployeeID", typeof(Int32));
dt.Columns.Add("Name");

 ds.Tables.Add(dt);

dt1 = new DataTable();
dt1.TableName = "EmployeeAddress";
dt1.Columns.Add("EmployeeID", typeof(Int32));
dt1.Columns.Add("EmployeeAddressID", typeof(Int32));
dt1.Columns.Add("Address");
dt1.Columns.Add("AddressType");
 ds.Tables.Add(dt);
 ds.Tables.Add(dt1);

 //Inner join
 var result = from emp in ds.Tables[0].AsEnumerable()
             join empAdd in ds.Tables[1].AsEnumerable()
                 on (int)emp["EmployeeID"] equals (int)empAdd["EmployeeID"]
             select new
             {
                 EmployeeID = (int)emp["EmployeeID"],
                 Name = (string)emp["Name"],
                 Address = (string)empAdd["Address"],
                 AddressType = (string)empAdd["AddressType"]
             };

foreach (var emp in result)
{
    Console.WriteLine(String.Format("EmployeeID : {0} , Name : {1} , Address : {2} , AddressType : {3}", emp.EmployeeID,emp.Name,emp.Address,emp.AddressType));
}

//Left join
var result = from emp in ds.Tables[0].AsEnumerable()
             join empAdd in ds.Tables[1].AsEnumerable()
                 on (int)emp["EmployeeID"] equals (int)empAdd["EmployeeID"] into AddGrp
             from record in AddGrp.DefaultIfEmpty()
             select new
             {
                 EmployeeID = (int)emp["EmployeeID"],
                 Name = (string)emp["Name"],
                 Address = record == null ? string.Empty : (string)record["Address"],
                 AddressType = record == null ? string.Empty : (string)record["AddressType"]
             };

foreach (var emp in result)
{
    Console.WriteLine(String.Format("EmployeeID : {0} , Name : {1} , Address : {2} , AddressType : {3}", emp.EmployeeID, emp.Name, emp.Address, emp.AddressType));
}

这篇关于从存储在datatable对象中的数据表中选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！